Microsoft Dynamics Nav and Excel are both excellent Microsoft products, in this blog I will show you how Microsoft Dynamics NAV and Excel work together to create a report without any enhancements or modifications needed.
Let’s set up a scenario and start by creating a Customer Report summarized by Country/Region on excel and retrieving the data on the Dynamics NAV.
I will also show you how to use the “Pivot Table” on excel, which is a great tool to reorganise and summarize data on excel.
Step 1: We need to export the Customer List on excel. Open Microsoft Dynamics NAV and go the Customer List and click the Microsoft Excel.
Step 2: Click the “Open” button on the Export file window this will open excel with the Customer List on the sheet. The Customer List is now exported on excel.
Step 3: Now we need to use Excel and the Pivot Table. Select the cell in the Customer List that you want to use in the Pivot Table function in this case it’s the (No, Name, Country/Region Code and Country/Region Name).
On the Excel Ribbon “INSERT” click the “Pivot Table” this will open the “Create Pivot Table” window then Click “OK”.
Step 4: A new “Sheet” will be added on Excel. On the “Pivot Table Fields” you can see the Column that you selected select the “Country/Region Name” and drag thin one at a time on the “Rows” and “Values” as a result this will be summarised by Country/Region with a grand total and you can see on the summarised by Country/Region Australia has 8 Customers and a total of 68 in all Country.
Step 5: To further enhance the Customer Report summarized by Country/Region on the excel you can add a chart that is available on the Excel. You can do this by simple, select the “Grand Total” this will select the “PivotTable” and on the Excel Ribbon “INSERT” click the “Chart Icon” and this will automatically create a Pivot Chart and now you have a chart with the data of the PivotTable.
This post was written by Jayser Santos, Technical Specialist at Evolution Business Systems.
If you have out-dated business software that’s limited in its reporting and analytical capabilities – then you should to talk to EBS about a powerful business management software solution – Microsoft Dynamics NAV. To find out more about our suite of Microsoft business management software solutions, please call Paul Woods at EBS for an obligation free discussion on 1300 303 973.
Subscribe here to get our free EBS monthly enewsletter to get valuable blog articles from EBS and the latest updates on the Microsoft Dynamics product suite.