Inventory Valuation in Dynamics NAV
4th July 2009
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.
July 6th, 2009 at 5:21 pm
Thank you. This is great! I will be putting it to the test today. Thank again.
July 7th, 2009 at 6:36 pm
Hello Derald
I’ve made a couple of changes/fixes to the query and made it more legible.
Please redownload it again. Thanks.
July 9th, 2009 at 5:59 pm
WOW! Our company runs an inventory valuation about twice a month to get the bottom line value and everytime they complain that it takes too long. After making one small modification to give the sum of the individual values, it took 2 minutes and 40 seconds. In Navision it took over 4 HOURS! Now if I can just get it to apply filters based on the Item Card, it will be perfect!
Thanks for sharing this!
July 10th, 2009 at 5:54 am
You are welcome. You can add Item table to the query by doing a left join on the whole statement.
July 20th, 2009 at 8:10 pm
I need to modify this to include description and unit cost, similar to the Native NAV report, can you lend a hand?
July 21st, 2009 at 2:32 pm
I have updated the sql statement and added description and unit cost.
August 3rd, 2009 at 5:29 pm
WOW!!! thank you so much. Just running our FG would take 12 hours. I knew it could be done in SQL but just had not the time to script it out. This runs great. I ran all inventory in 3.11 minutes and it use to take all day. thanks and a job well done.
February 11th, 2010 at 7:21 am
Hi rashed,
this looks very interesting! There is one thing that I didnt get very much. You mentioned:
“You can use this also in NAV report using ADO to execute the query using stored proc and get the result.”,
You mean, in the SSRS reports only? or you can bind a classic report to a query on sql too??
can you explain a little how to do it to an SSRS report?
thanks for your time,
O
February 11th, 2010 at 8:32 am
Hello Oscar
From classic client/ and you could also do this for RTC, you can use ADO to execute any query. You store the results in a temporary table and print that on the report.
Here is how to use ADO
http://mibuso.com/forum/viewtopic.php?f=5&t=23038&hilit=ADO