Quick Reporting in Dynamics NAV Using Microsoft Excel Pivot Tables
Yesterday a client called and asked if there was a report in Dynamics NAV that listed inventory on hand and in which warehouse bins the inventory was located.
We did not find a report for this, so we thought that if we could get to the Bin Contents Table, we could export the data to Excel and create a Pivot Table to present the information.
Using Dynamics NAV 2013, we went to an item card.
From there, select “Bin Contents.”
Figure 1 – To show inventory on hand and the associated bins, start by selecting “Bin Contents” from the Item Card
This gives us only the Bin Contents for the current item, so we removed the item filter.
Figure 2 – Remove the item filter
This then gave us the entire Bin Contents list for all items.
Figure 3 – The entire Bin Contents list for all items is displayed
We filtered out all of the zero quantities, which gave us the all of the Bin Contents containing inventory.
Figure 4 – Remove the zero quantities to list all the Bin Contents containing inventory
We then copied and pasted the data to Excel.
Figure 5 – Copy and Paste the data into Microsoft Excel
And from there, we created a Pivot Table to present the report.
Figure 6 – Create a pivot table to display the report
You can see that with a little knowledge of Dynamics NAV and some skills in Microsoft Excel, you can write reports from the Dynamics NAV data. This report took less than 5 minutes to generate. If this report became widely used, then you would want to write it using other report writing tools such as Jet Reports.
If you would like more information on this subject or another Dynamics NAV subject, please contact ArcherPoint.
Read more “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV.