Hello,
I have a question on replicating sheets. I am creating a report that show the total expense by cost category by month for a given office. The question here is how do I leave out offices that do not have actual expenses? I replicate for all offices in the table and a tab is created even if there are no actuals.
How can I replicate only the sheets that have activity and leave out those with no activity?
Much appreciated,
T
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hi Tom,
I actually don't see <>@@ very often, but I believe that is supposed to be filtering out blanks. Usually you would use 2 single quotes for blanks so <>'' should be equivalent.
Since it's all the same table, it seems like you should just be able to add some of the extra filters from the NL(Sum) formula to the NL(Sheets) formula like this:=NL("Sheets","G/L Entry","Global Dimension 2 Code","Global Dimension 2 Code","<>''","Business Unit Code",Options!$E$22,"Company=",Options!$C$21,"Transaction Type","Actual","Posting Date",H$11,"Closing Entry",FALSE)
Then you shouldn't get any Global Dimension 2 Codes which don't have actual entries in the specified posting date range that are not closing entries. Does something like that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi T,
What does your NL(Sheets) function actually look like and what does your formula to get the actuals look like? I'm assuming you need to do a link= in your NL(Sheets) to the table with the actuals, which should be pretty straight forward once we know what the tables are and what fields link them together.
Regards,
Hughes -
Jet Reports Historic Posts Hey Hughes,
Thanks for getting back to me.
The NL Sheets formula is below. I believe it is replicating based on a global dimension code associated with our office IDs.
=NL("Sheets","G/L Entry","Global Dimension 2 Code","Global Dimension 2 Code","<>@@","Business Unit Code",Options!$E$22,"Company=",Options!$C$21)
The NL Rows formula that pulls in actuals looks like it is using the same table with a different syntax to build the sum by g/l account number.
=NL("Sum","G/L Entry","Amount","G/L Account No.",$F12,"Global Dimension 2 Code",$D12,"Transaction Type","Actual","Posting Date",H$11,"Closing Entry",FALSE,"Business Unit Code",$L$2,"Company=",$I$2)
And BTW - What is <>@@? New to Jet so bear with me.
Thanks!
Tom -
Jet Reports Historic Posts Thanks Hughes!
This helps greatly. I just used the new formula and no zeros I appreciate the help!
If I run into any more roadblocks I'll reach out.
Tom
Please sign in to leave a comment.