I am working on a report to show us our purchase receipts not yet invoiced with the ability to run it “as-of” a given date in the past. This is connecting to NAV 2016. I was able to take the Item Ledger Entries easy enough with this NL Statement:
=NL("Rows","Item Ledger Entry",,"Posting Date",$F$4,"Document Type","Purchase Receipt|Purchase Return Shipment")
Then to calculate the Invoiced Qty and Cost Expected for a period back in time, I used separate NL functions on the Value Entry table for those fields.
=NL("Sum","Value Entry","Invoiced Quantity","Item Ledger Entry No.",D8,"Posting Date",$F$4)
=NL("Sum","Value Entry","Cost Amount (Expected)","Item Ledger Entry No.",D8,"Posting Date",$F$4)
This works but the issue I am having is the report pulls in lots of records that were already invoiced and thus the Cost Amount is zero. I don't need those records. I am struggling trying to find a way to exclude these results. I cannot simply filter the field in the Item Ledger because I need to calculate what the value was at another date.
I have tried a different version of report by grouping records from the Value Entry table, but again with that scenario I never found a way to exclude groups who’s subtotal was zero.
Any ideas how I can do this?