Publish a Web Service
Account Schedule KPI Web Service Setup
Open the Account Schedule KPI Web Service Setup.
Specify the fields such as G/L Budget Name and Web Service Name.
In the Account Schedules fast tab, select Account Schedules.
Publish Web Service
Click Publish Web Service.
To see more details about the web service just created, click Web Services under Navigate tab.
Here you can also see the link to the location of the web service, i.e. Odata URL. You will need this link to establish a connection between Microsoft Dynamics NAV and Excel.
Connect from Excel to Microsoft Dynamics NAV
We would like to integrate the KPI data in Microsoft Dynamics NAV with Excel, so that we can update the data in Excel at any time.
Therefore, we connect Microsoft Dynamics Nav with Excel using OData and Web Service created earlier. This connection is established from Excel.
In Excel Connect to Odata Source
In Excel, get external data From OData Data Feed.
Connect to Account Schedule KPI Web Service
In the Data Connection Wizard, enter the location of the web service you want to connect to. Specify the link that you find in the Account Schedule KPI Web Service Setup.
Go to rest of the wizard and Finish it.
In the Import Data dialog, select PivotChart.
Once the data is retrieved you can drag and drop the required fields as needed.
As an example in this case we have selected:
- Account Schedule Name in Filters
- KPI Name in Legend
- Date in Axis and
- Balance at Date Actual in Values
The pivot chart is automatically updated and we can change as needed.
Automatic Data Refresh
Whenever you want updated numbers from Microsoft Dynamics NAV, you can go into Excel and click Refresh.