Walkthrough: Viewing Page Data in Excel Using PowerPivot (OData)
This walkthrough describes how you can use OData to expose a Microsoft Dynamics NAV 2013 page as a web service and then view and analyze the page data in Microsoft Excel using Microsoft PowerPivot for Excel 2010, which is a data analysis add-in. For information about PowerPivot, see Microsoft PowerPivot.
About This Walkthrough
With OData and PowerPivot, you gain access to a powerful set of tools and technologies for data exchange and analysis. This walkthrough illustrates the following tasks:
Publishing a Microsoft Dynamics NAV page as a web service.
Verifying web service availability from a browser.
Using the PowerPivot add-in for Excel 2010 to import the table data as a new worksheet.
This procedure also includes optional instructions about how to use a web service access key.
Creating a PivotTable from the worksheet, selecting relevant fields, and then organizing and formatting the data to highlight strategic data.
Prerequisites
To complete this walkthrough, you will need:
Microsoft Dynamics NAV 2013 with a developer license.
CRONUS International Ltd. demonstration database.
Excel 2010 with the PowerPivot add-in for Excel 2010 installed. PowerPivot is only supported with Excel 2010. For more information, see PowerPivot Download Page.
Optionally, if you want to use a web service access key to authenticate access to the web service, then Microsoft Dynamics NAV must meet the following requirements:
The Microsoft Dynamics NAV Server is configured to authenticate users by using the NavUserPassword credential type.
There is a Microsoft Dynamics NAV user account that has a web service access key. In this walkthrough, the user account has the user name NavTest.
For more information, see Users and Credential Types.
Publishing a Page as a Web Service
You publish a web service by using the RoleTailored client.
To register and publish a page as a web service
Open the RoleTailored client and connect to the CRONUS International Ltd. company.
In the Search box, enter Web Services, and then choose the related link.
In the Web Services page, choose New.
In the Object Type column, select Page. In the Object ID column, enter 21, and in the Service Name column, enter Customer.
Select the check box in the Published column.
Choose the OK button to close the New - Web Services page.
Verifying the Web Service’s Availability
.gif "Hh165316.security(en-us,NAV.70).gif") Security Note |
|---|
| After publishing a web service, verify that the port that web service applications will use to connect to your web service is open. The default port for OData web services is 7048. You can configure this value by using the Microsoft Dynamics NAV Server Administration Tool. |
To verify availability of a Microsoft Dynamics NAV web service
Start Windows Internet Explorer.
In the Address field, enter a URI using the following format: http://<Server>:<WebServicePort>/<ServerInstance>/OData/.
Server is the name of the computer that is running Microsoft Dynamics NAV Server.
WebServicePort is the port that OData is running on. The default port is 7048.
ServiceInstance is the name of the Microsoft Dynamics NAV Server instance for your solution. The default name is DynamicsNAV70.
For example, if the Microsoft Dynamics NAV Server is running on the computer that you are working on, then you can use: https://localhost:7048/DynamicsNAV70/OData/
The browser should now show the web service that you have published, as shown in the following illustration.
.jpg "OData services listed in a browser")
Note
If the browser cannot find the web service, then it may indicate that the specified Microsoft Dynamics NAV Server instance is not running. For more information, see Managing Microsoft Dynamics NAV Server Instances.
Importing Microsoft Dynamics NAV Data into Excel 2010
In the following procedures, you use PowerPivot to import Microsoft Dynamics NAV data into Excel 2010. If you will be using a web service access key for authentication, then only perform the second procedure; otherwise, only perform the first procedure.
To import Microsoft Dynamics NAV data into Excel 2010
Start Microsoft Excel 2010.
In Excel, on the PowerPivot tab, choose PowerPivot Window.
In PowerPivot, on the Home tab, in the Get External Data group, choose Get External Data From a Data Feed.
The Table Import Wizard opens.
In the Connect to a Data Feed dialog box, in the Data Feed Url field, enter the OData URI that you verified in the previous procedure, for example https://localhost:7048/DynamicsNAV70/OData/. Choose the Next button.
Verify that Customer appears in the Source Table column.
Select the check box next to the Customer table, and then choose Finish.
After you see the Success message, choose theee Close button.
You should now see data from the Customer page displayed as an Excel workbook.
To import Microsoft Dynamics NAV data into Excel 2010 by using a web service access key
Start Microsoft Excel 2010.
In Excel, on the PowerPivot tab, choose PowerPivot Window.
In PowerPivot, on the Home tab, in the Get External Data group, choose Get External Data From a Data Feed.
In the Advanced dialog box, in the Security section, set the Integrated Security field to Basic.
If your OData is configured to use SSL, then set the field to SSPL.
In the Password field, type the web service access key.
In the UserID field, type the user name for the Microsoft Dynamics NAV user account. For this walkthrough, use NavTest.
In the Source section, in the Service Document URL field, type the URL for the OData web service that you verified in the previous procedure, for example, https://localhost:7048/DynamicsNAV70/OData/.
Choose the OK button to return to the Table Import Wizard.
In the Connect to a Data Feed dialog box, choose the Next button.
In the Select Tables and Views dialog box, select the check box next to the Customer table, and then choose the Finish button.
After you receive the Success message, choose the Close button.
Creating a PivotTable Containing Key Microsoft Dynamics NAV Data
In this procedure, you use the Excel workbook with data from the Customer page to create a PivotTable from the worksheet. You select relevant fields and then organize and format the data to highlight strategic data. Building a pivot table is a way to select and arrange data so as to highlight and focus on key elements.
To create a PivotTable
In Excel, select the cell where you want the PivotTable located.
In the ribbon, choose the Insert tab, and then in the Tables group, choose PivotTable.
In the Create PivotTable dialog box, choose Use an external data source, and then choose Choose Connection.
In the Existing Connections dialog box, choose PowerPivot Data, and then choose Open.
Choose the OK button to add the PivotTable to the Excel Worksheet.
The PowerPivot Field List pane on the right side includes a list of fields from the Customer page that where imported from PowerPivot.
In the PowerPivot Field List pane, choose Location_Code.
Tip
To quickly find a field in the field list, type part or all of the field name in the Search text box that is above the list of fields, and then press Enter to highlight the first field that contains the text. You can then choose the right arrow to proceed to the next field, and so on.
Select the Balance_LCY field.
Select the Name field.
You can now see the data in the body of the worksheet, as shown in the following illustration.
.jpg "PivotTable after selecting fields")
The PivotTable shows customers by region (as defined by colors) and individual customer balances, and also adds the balances by region. To make the information more readable, you can update the headings on the PivotTable.
Select the cell that has the heading Sum of Balance_LCY, and then, in the formula field, type Balance.
Select the cell that has the heading Row Labels, and then in the formula field, type Companies by region.
Select the empty cell that is below the Companies by region cell, and then, in the formula field, type Region unspecified.
The following illustration shows how the worksheet looks after you make these changes.
.jpg "PivotTable during setup")
Next Steps
Now that you have created your PivotTable, you can continue to enhance the data to make it more useful and readable. You can:
Add a column to the data that shows average balance by region.
Enhance data presentation with a graph.
Post the data in a Microsoft SharePoint 2010 environment with live data from Microsoft Dynamics NAV 2013.
For more information about using Microsoft Dynamics NAV data with PowerPivot information, see Walkthrough: Combining Data from Microsoft Dynamics NAV Queries and Pages with Data from Azure DataMarket (OData).