Creation of ODATA Web Services in NAV 2013 and consuming data in excel

The Open Data Protocol (OData) is a Web protocol for querying and updating data that provides a way to unlock your data. OData does this by applying and building upon Web technologies such as HTTP, Atom Publishing Protocol (Atom Pub) and JSON to provide access to information from a variety of applications, services, and stores.

If you see the service console of NAV 2013, Microsoft Dynamics NAV Administration you will see a separate tab for ODATA Services as shown below –


As you can see the parameters in this tab are pretty self-explanatory.

First thing need to be done is enable OData services from the console if it is not enabling by default.

The URL to test the ODATA Services is – http://localhost:7048/DynamicsNAV70/OData/

Where –
Local host – is the Server name where it is hosted (in my case it is hosted on local machine).
:7048 – is the port where it is hosted on server.
DynamicsNAV70 – Service Instance Name.
OData – is open data protocol.

The response of above URL from Internet Explorer is


Open the NAV 2013 windows Client and navigate to
Departments->Administration->IT Administration->General->Web Services

Edit the page and expose the object you want. Here we are using Customer Card, Page 21.
and named it “WebCustomers” as shown.


Let’s refresh the URL in Internet Explorer. Now you can see “WebCustomers” there in the list.


Now let us try to find the data that is exposed.

Here is the URL for same – http://localhost:7048/DynamicsNAV70/OData/WebCustomers


Consuming the Web Service Data

1. Open Excel 2010.
2. You will find a new tab in Ribbon called Power Pivot as shown below.


3. Click on Power Pivot Window, icon placed at left corner of the screen.
4. A new window will open as shown.


5. The Power Pivot Add in can accept data from Multiple sources like Text, Access, Database and many others. For ODATA we will be using the From Data Feeds.
6. Click on From Data Feeds.
7. Type in the Friendly Name and Copy the URL from above to Data Feed URL.
8. Click Test Connection.


9. Select the WebCustomers Source Table
10. Import all the records of customer table in excel sheet
11. After importing the records will be updated like below


12. From this you can create Pivot Tables & charts which can be accessed via ribbon of power pivot.
13. With Excel 2010, the Pivot charts are also too good with Slicer as you can see in the Left and Right part of the Pivot Chart below.

Tags: Dynamics NAV
previous post: HTML5 localStorage with examples next post: Discount Handling in Dynamics SL