Follow us today>

Blog

Get industry insights, product updates, event invites, articles and latest NAV release to your inbox.

 

Web services based on Account Schedule KPI

Posted: 4/05/2017 10:54:46 AM by Dynamic Business IT Solutions

Both Pages and Query object in NAV can be published as Web Services. Microsoft used this approach in NAV to bring life to account schedules in Excel.

Today’s blog post is about using this capability to publish Pages and Queries as OData-Feeds in order to analyse NAV’s data in Excel.

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.