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.

https://docs.google.com/open?id=0BwJ-6_iDMdRONVZYOGJ2NnRIMzQ

https://docs.google.com/open?id=0BwJ-6_iDMdROTElBc0dJWTJFa3M

report 50003 text and object

Comments are closed.