In Microsoft Dynamics NAV 2013 R2, we have introduced a feature that makes it possible to create KPI web services based on account schedules. We can use a KPI web service to integrate with applications such as Excel, so that the user can work with continuously updated KPI numbers outside of Microsoft Dynamics NAV.

Now we can create a KPI web services and use these KPI web services in Excel.

We can create a web service for an account schedule in Microsoft Dynamics NAV, so that data from the system can be integrated with Excel. First, we need to define the account schedule that we want to create a Web Service for. We can chose as many account schedules as we want, however, we must choose at least one account schedule. After that, using the Publish Web Service function we will create the web service and insert it in the web service table.

When we finish complete process about create web service, we need to integrate KPI data in Microsoft Dynamics NAV with Excel in a smart way so we can easily get the data updated in Excel when we want. In Excel, connect to Microsoft Dynamics NAV with OData using the KPI web service that we first created.

We need to enter the location of the KPI web service that we want to connect to and we will use Data Connection Wizard. By selecting PivotChart, the data coming from the KPI web service will be imported into a pivot chart in Excel.

Microsoft has shown how KPI data from Microsoft Dynamics NAV can easily be made available and updated outside of Microsoft Dynamics NAV. After that we can use Account Schedule data from Microsoft Dynamics NAV in Excel and choose Refresh every time that we want updated KPI-numbers from Microsoft Dynamics NAV.

The key benefits of KPI web service are:

  • Requires only a few steps to set up.
  • Enables smooth data integration between Microsoft Dynamics NAV and Excel and Data Warehouse.