Hi,
I created a report to display Sales for a customer. Report has 4 columns: Customer No, Customer Name, Open Order, and Posted Invoices
I calculate Open Order by using the following formula:
=NL("Sum","Sales Line","Outstanding Amount","Item Category Code","@@"&$D$5,"Sell-to Customer No.",F10,"Company=",Company,"Type","Item","Link=Sales Line","Sales Header","No.","=Document No.","Shipment Date",NP("DateFilter",StartDate,EndDate),"Link=Sales Line","Item","No.","=No.","Sales Report Code","@@"&$D$4)
I calculate Posted Invoice using the following formula:
=NL("Sum","Sales Invoice Line","Line Amount","Item Category Code","@@"&$D$5,"Sell-to Customer No.",F10,"Company=",Company,"Shipment Date",NP("DateFilter",StartDate,EndDate),"Type","Item","Link=Sales Invoice Line","Item","No.","=No.","Sales Report Code","@@"&$D$4)
This works, but there is a flaw. The problem is when I run the report, if any units are on a pick they do not appear in either column until they are posted invoices. The problem with this is if you look at the report from week to week or even day to day, the totals change dramatically and one customer looks like lost sales from week to week while the invoices I post on Monday now look like a big increase for a customer but in actually the numbers just did not report anywhere while in pick status.
Ie
Customer A
Open Invoiced
Monday 2500.00 0.00 Order Entered
Tuesday 0.00 0.00 Shipment created and in picked status
Wednesday 0.00 2500.00 Invoice Posted
Everyone that looks at the report on Tuesday is very confused. I need to 2500 to show up no matter what day it is or what the status of the order is. So I wanted to create a 5th column called: In Process (which will display work in progress). In NAV this is straightforward for the Sales Line (Qty. Shipped Not invoices * Unit Price Excl. Tax). I am struggle to implement this simple logic to a nl function.