Inventory Valuation in Dynamics NAV

One of the issues customers run into as their database gets bigger and bigger is that their reports take longer and longer to run. One of those reports is the Inventory Valuation report. I’ve always suggested customers to look at SQL reporting services to write these reports. Inventory valuation report is a little complex, mainly because it can be run as of date. A user can run the report today and enter a date a month ago and find out what the value of his or her inventory was at that date.

Below is a screenshot of the report.

I have written the sql statement to run this on sql server and use it as datasource for your sql reporting

Here is a screenshot.

You can use this also in NAV report using ADO to execute the query using stored proc and get the result. I’ve implemented the above solution at a client where the original report took 40 minutes to run, and with using ADO, it took less than 5 minutes to run the report. That’s a huge performance gain.

Here is a screenshot of the data running in SSRS

I’ve attached the code sql code here Inventory Value script

In 2009 NAV released a new reporting rendering using reporting services for client. The service tier runs business logic and generates an xml file that is rendered at the client. Obviously if you have a large database the xml file can get huge. This takes up resources and memory on the service tier. In 2009 Sp1 they have added a new feature to offload the xml file in parts as it being generated. The problem is mitigated but really not solved. Until NAV introduces new objects type for querying the data we will have to live with this overhead.