I would like to create a summary jet report which shows the total sales by state for a date range. I can get a list of the states from the customer table with =NL("Rows","Customer","State") , but I can’t figure out how to join the customer ledger entry table in without showing each customer in a separate row. I would like just one row per state, and have the drill down to NAV available.
Thanks.
Sara
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi Sara,
You have a good start, now you just need to use the NL("Sum") with a NL("Filter") to complete. So, let's assume that you have the following
in Cell D6
=NL("Rows","Customer","State")
Then in Cell E6, you can put
=NL("Sum","Cust. Ledger Entry","Amount","Customer No.",NL("Filter","Customer","No.","State",$D6),"Posting Date",CellRefToDateFilter)
What the NL("Filter") does is create a filter (or list) of "No."'s from the Customer table where State = $D6. This gets passed to the "Customer No." field as a filter for the "Cust. Ledger Entry" table, where we then SUM the amount field based on thoses customers. -
Jet Reports Historic Posts I used your suggested formula for a similar calculation to filter on the customer posting group from the customer table. The formula works great however, when I attempt to drilldown on the cells with larger amounts of data I get the following error:
"Drilldown is not available. A calculated filter that selects multiple records might not allow drilldown. This can happen when too many records are selected, …"
The amount of data is not any larger than other reports I've run and been able to drill down on.
What can I do to avoid this error?
Theron -
Jet Reports Historic Posts I actually have a similar question…
I get the same error, I can understand why since I'm summing from the Sales Line table, I would like to just avoid users from doing a drilldown, but just for this one cell/field. Is there any way to prohibit drilldown for a specific NL function?
thanks!!
Please sign in to leave a comment.