Skip to content

Quick Reporting in Dynamics NAV Using Microsoft Excel Pivot Tables

ArcherPoint How-To Blog: Step-by-step instructions on how to perform specific tasks in Microsoft Dynamics NAV

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.”

Select Bin Contents from the Item Card

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.

Remove the item filter

Figure 2 – Remove the item filter

This then gave us the entire Bin Contents list for all items.

The entire Bin Contents list for all items is displayed

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.

Remove the zero quantities to list all 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.

Copy and Paste the data into Microsoft Excel

Figure 5 – Copy and Paste the data into Microsoft Excel

And from there, we created a Pivot Table to present the report.

Create a pivot table to display 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.

Blog Tags: 
Read ArcherPoint's Blog Follow us on Twitter Follow us on Facebook Follow us on LinkedIn Link to our RSS feed Join us on Google+ Watch us on YouTube
Get Help Now