I'm after an aged quantity report and I only want to show the items from the Item Ledger Entry where the sum of Quantity in the same table is >0. The formula so far would be..
=NL("Rows","Item Ledger Entry","Item No.","Posting Date","..5/1/14")
I tried something like =NL("Rows","Item Ledger Entry","Item No.","Posting Date","..5/1/14","=NL(""Sum"",""Item Ledger Entry"",""Quantity"",""Posting Date"",""..5/1/14"")",">0")
That obviously doesn't work because there's nothing linking that sum back to the Item No.. anyone know how to achieve this?
Date
Votes
2 comments
-
Jet Reports Historic Posts Perhaps if you try building the filter the same way you would a sort by sum filter?
See if this topic helps, but fair warning, this can slow a report down considerably:
http://community.jetreports.com/viewtopic.php?f=23&t=2109&start=0&st=0&sk=t&sd=a&hilit=teach+sort+by+sum
(if the link doesn't work, do a search for "most effective way to teach sort by sum"
good luck!
~ Heather -
Jet Reports Historic Posts Filtering by an NL(Sum) is pretty cool, but so slow. There's an altenative in this case… Off the top of my head…
Did you have a look at the "Net change" field on the Item table? It's a Flowfield - defined to be the sum of the Quantity field of Item Ledger Entry table, and it has the option to put a date filter to it. So, I'd give this one a try=NL("Rows","Item",,"Date filter","..5/1/14","Net Change",">0")
And next you can use the NF function retrieve the fields you'd like to present in your report.
Please sign in to leave a comment.