I have a report which uses both NL(Column) and NL(Row) functions to generate a report. The NL(Column) function which returns all months within a specified data range filter, and the NL(Row) function which returns a sum for all accounts within a specified range of accounts. Question: Is there a way to have this jet reports hide a row in which all cells in all columns return a zero value for the sum?
Example:
Acc. #: July/ August/ September/ ect…
Acc# 1: 0.00/ 30.00/ 125.00/
Acc# 2: 5.00/ 15.00/ 130.00/
Acc# 3: 0.00/ 0.00/ 0.00/ <——I would like this row would be forced to hide
Acc# 4: 3.00/ 10.00/ 177.00/
Acc# 5: 2.00/ 17.00/ 130.00/
Thanks for the help and any ideas!
Date
Votes
4 comments
-
Jet Reports Historic Posts =IF(AND(D4=0,E4=0,F4=0),"Hide","Show")
-
Jet Reports Historic Posts Thanks for the response, Sebastiaan. But maybe I was unclear in my request. See, in using a Columns function I am never sure how many columns will be returned based on the variable date range that the user might choose. And also given the fact that I will be placing a totals column after all months I am unable to assume that I know which columns to target for my dependent statement. To add to the complexity I will be returning budgeted columns after the actuals which may not come to zero either.
Before I was using a statement in A4 similar to:
=If(sum(D4:E4)=0,"Hide","") where column E was an empty column. But when running the report this function is replaced by the text "Auto" and the hide command was never able to take affect. Because of this I wonder if it is possible to use any IF statement in column A to automate this hide row function I'm looking for.
Any thoughts? Thanks again for the help. -
Jet Reports Historic Posts Before I was using a statement in A4 similar to:
=If(sum(D4:E4)=0,"Hide","") where column E was an empty column. But when running the report this function is replaced by the text "Auto" and the hide command was never able to take affect. Because of this I wonder if it is possible to use any IF statement in column A to automate this hide row function I'm looking for.
Any thoughts? Thanks again for the help.
be sure that you put Hide+? in B1 and Excel IF() in column B - should work :)
take a look on "Conditionally Hiding Rows" topic in Jet Help.
B. -
Jet Reports Historic Posts You guys are awesome! Great solution. Thank you, and thank you for the reference to the Help resource. I'll be sure to look there in future.
Please sign in to leave a comment.