NAV 2013 - how to create a report combining items from sales orders and posted invoices and credit memos
Sales Item HistorySales Item HistoryIn Classic NAV, combining posted invoices and credit memos typically involved the use of a temporary table to build the sales lines from the sales invoice line table and the sales cr.memo line tables.
Because the NAV uses a flat result set, it is possible to combine the lines without the use of a temporary table.
What my example has, is a customer dataitem followed by 3 indented dataitems (sales invoice line, sales cr.memo line, and the sales line).
I have one column in each (otherwise we end up with just 1 row per customer), and then added global variables to handle the common dataitem fields (item no., quantity, unit price, and line amount. In each dataitem, I populate these fields with a negative for the credit quantities and amount.
Since there is one row produced per combination of customer/sales invoice line, customer/sales cr.memo line, and customer/sales line, we now have a report by customer of all the items that customer has ordered - including invoices, credits, open orders, etc.
report 50003 text and object