Account Schedule in Microsoft Dynamics NAV is a powerful tool to organize your financial data and represent it in a readable and efficient way. However, Dynamics NAV’s chart and Pivot table features are limited. This is an especially acute problem for Microsoft Excel users who prefer to work with this application in order to analyze general ledger data. Dynamics NAV 2013 R2 provides an integration with Excel through the KPI Web Service feature.
In this demo, we are going to build a chart in Excel based on data from Dynamics NAV. To do this, we will need to perform the following steps:
- Setup KPI Web Service in Dynamics NAV
- Import data to Excel
- Build and adjust the Excel chart as needed
OData (Open Data Protocol) is used to query the tabular data. These services are currently read-only and there is no way to modify Dynamics NAV data using an OData web service. However, this allows us to read data and work with it via external applications.
In this demo, we are going to use Dynamics NAV 2013 R2 version.
Setup KPI Web Service in Dynamics NAV
- Open the Account Schedule KPI Web Service Setup page. It is located in the Administration/IT Administration/General menu.
- Enter the G/L Budget Name and Web Service Name as shown and click the Publish button to activate the web service and make it visible to other applications.
- On the Navigate FastTab, click Web Services to view the details of the created web service:
- On the lines, select the Revenue Account Schedule:
- Two URL links became available on the Web Services page. We need to copy the value in the OData URL field.
- This link will be then needed to import NAV data to Excel. Copy the link URL and close the page.
- Now we need to ensure that the OData Services are enabled for Dynamics NAV. To do this, open Dynamics NAV Administration tool, select the NAV instance which you are using and check the following field is enabled:
- Be sure to restart the NAV service if prompted.
- Open Excel application and on the Data tab, select Get External Data from OData Feed as shown.
- In the Data Connection Wizard, enter the copied OData URL link from Dynamics NAV and click Next.
- If you select to switch between the tables, select the KPI web service we have created before and click Finish:
- In the Import Data window, select the Pivot chart option. This is the way we want our data from NAV to be imported:
- Click OK. The data is retrieved from Dynamics NAV. Once the process is completed, you will see the standard Excel tools for building the Pivot chart.
- We will use Account Schedule Name fields as filter, KPI code as a Legend. In the Axis, let’s select the Date field, and in Values – Balance at Date field. We can add and remove the fields as necessary. The result of our work will look something like this:
- As usual, we can change the chart type. Right-click on the chart and select Change Series Chart Type. This is the example of the same data represented as Line:
- This is another example of forecasted net change against actual:
Import Data to Excel
Build and Adjust the Excel Chart
In this article we have reviewed the simple yet handy way of using OData services and connected Dynamics NAV data with Excel via Pivot chart. Whenever the data in NAV is changed, it is easy to update it in the Excel chart by clicking Refresh button. OData is also supported in PowerPivot excel add-in which in its turn opens the door to great Business Intelligence tools for the user.