Hi all,
I am trying to create a report in jet that mimics an analysis view that we have.
We have costs related to a number of our offices in one company, they are all tagged with the office dimension.
I have created a report that lists down the GL codes (NL Row) and lists the offices (NL columns) and then a NL sum function plus the value posted filtered by the GL acc no and the office.
What i would like to do is only show those entries that have values as there are a lot of blank entries, or is there any better way of creating the report i want?
Template attached.
test doc.xlsx
Date
Votes
2 comments
-
Jet Reports Historic Posts Hello.
The easiest way to modify this report is to use conditional hides to hide the rows you don't want to show. If you are not familiar with this then please check out this KB article.
Conditional Hiding Rows Columns or Sheets - http://kb.jetreports.com/article/AA-00522/0/Conditionally-Hiding-Rows-Columns-or-Sheets.html
The report will still generate the rows, but they will be hidden.
There are other ways to accomplish this as well by comparing values and only bringing back the ones that aren't zero, but the logic there is pretty complicated.
Hope this helps. -
Jet Reports Historic Posts Hi Niallc,
Are you trying to hide GL Accounts where the values of all offices are zero?
You can use a conditional hide as Jason suggested but that will still require the report to run those calculations of your NL(Sum) against the GL Entry table. This can create a slow report.
Here are a couple suggestions for this report. First, instead of conditionally hiding, explore the "Sort by Sum" tutorial in these message boards http://community.jetreports.com/viewtopic.php?f=23&t=2109&p=10484&hilit=sort+by+sum#p10484
It would look something like this: =NL("rows","G/L Account","no.","Company=",B$6,"No.","70100..80000","=NL(""Sum"",""G/L Entry"",""Amount"",""Posting Date"",$B$2,""G/L Account No."",NF(,""No.""),""Department Code"",$B$3,""OFFICES"",E$6)",">0")
You can use that technique as a filter on your Rows replicator and instead of having the filter be "*", you would do ">0". Of course, this is a more advanced type of solution and often, using conditional hide is an easier solution where the speed of the report is acceptable.
Another suggestion would be to explore the GL Function instead of doing an NL(Sum) against the GL Entry table. This isn't always possible but if you have an analysis view available that has the dimensions of Department Code and Offices, this would perform much faster.
Hope that helps.
Please sign in to leave a comment.