The reports spits out rows of Unique G/L Accounts found within the G/L Budget Entry Table and a NL Amount field Sum which references the account. There are three distinct sections representing a range of accounts - these are departments.
Issue: If a range of accounts does not exist in the table for the department then Jet returns a blank - which is correct. The problem is that the sums are still looking for an account number and therefore display #VALUE.
Objective: Hide those rows where an account is blank.
Assumption: There must be a way to feed a HIDE value into the Auto+Hide (ColumnA) so that these offending rows do not display.
Thanks…looking forward to your response. Love the JET!
Date
Votes
1 comment
-
Jet Reports Historic Posts Hi,
There's a couple of things you can do.
First, to get rid of that nasty #value, you need to use "@@" when you are referring to the account number in the NL Sum functions. You're getting this because you are filtering on a blank value - the G/L account # isn't there. For example, supposing the blank GL account number is being returned in cell D11. The NL SUM formula should look something like this: =NL("sum","g/l entry","amount","g/l account no.","@@"&D11)
You asked how to hide empty rows. That's easy. In cell B1, enter HIDE+? Make sure you are in B1. You need to write and IF statement on the same row in column B where the Jet functions reside. If you have more than one column to be tested, you can either add a hidden column to add those cells together so you can test just that cell or you can build the conditions into the IF statement.
For example: Cell E11, F11 and G11 hold all the NL SUM functions.
Say you total all the cells into cell H11 with =SUM(E11:G11). The IF statement in cell B11 would look like this: =IF(H11=0,"HIDE","SHOW")
Or, you could also write the IF statement in cell B11 like this: =IF(AND(E11=0,F11=0,G11=0),"HIDE","SHOW")
This is in the HELP screens in Jet Reports and on the Support Site. The topic is called "Conditionally Hiding Rows, Columns or Sheets". When you type your question in the Support Site, it will automatically start to find you articles/tips to answer your question!
If you are still having a problem, please take advantage of our online support and submit at ticket with the report. We're only too happy to help you troubleshoot your reports.
By the way, we do have hands-on training coming up in Toronto at the end of January. Contact me if you need more info as it's not posted on the website. The 2008 schedule is posted at http://www.jetreports.com/ca/training.html.
Cheers!
Please sign in to leave a comment.