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 Jonathan Martin, COO at Evolution Business Systems.
His expertise extends to software development, support, implementations and project management, he can envisage potential problems and identify them to improve the client experience and outcomes, like a mad scientist, behaviour, statistics and data are where he gets his insights from and lives by a golden rule to always start at the beginning as there are no shortcuts. Jonathan’s passion is to make a difference by assisting clients with their business problems.