title | TOCTitle | ms:assetid | ms:mtpsurl | ms:contentKeyID | author | ms.date | mtps_version |
---|---|---|---|---|---|---|---|
Walkthrough: Creating a PowerPivot Data Mash-up |
Walkthrough: Creating a PowerPivot Data Mash-up |
5dccd8a9-f734-410c-83d1-19850200339f |
53401822 |
tonyafehr |
05/09/2014 |
v=AX.60 |
[!INCLUDEarchive-banner]
Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2
You can use Microsoft Dynamics AX 2012 R2 and Microsoft Office Excel with PowerPivot to combine data from multiple sources for analysis. This topic demonstrates how to create a data mash-up that combines information extracted from a list page in Microsoft Dynamics AX and an OData feed that surfaces data from a Microsoft Dynamics AX query. To create the mash-up, perform the following steps:
-
Extract data from a list page into Excel.
-
Extract data from Microsoft Dynamics AX into PowerPivot by using an OData feed.
-
Create a pivot table with data from both sources.
Microsoft Dynamics AX 2012 R2
Microsoft Office SharePoint Server 2010 or above (Enterprise edition) with PowerPivot services and Power View services
Microsoft Office Excel 2010 or above with PowerPivot add-in
Note
For information about PowerPivot capabilities in Excel 2013, see What’s new in PowerPivot in Excel 2013.
The first set of data for the data mash-up is from the All customers list page in Microsoft Dynamics AX. You can add additional fields to the list page and then extract the data by using the Export to Excel button.
-
Click Accounts receivable > Common > Customers > All customers.
-
Right-click the column header of the grid and then click Personalize.
-
Expand ListPageGrid.
-
Click Add fields.
-
Add the following fields from the Customers table:
-
Method of Payment
-
Segment
-
Subsegment
-
Customer classification group
-
-
Close the Add fields window and refresh the form.
- After the All customers list page is refreshed, click Export to Excel List.
The other set of data for the mash-up is from an OData feed. You can create an OData feed that exposes information from an existing Microsoft Dynamics AX query or from a custom Microsoft Dynamics AX query. After the OData query is set up, you can import the data into PowerPivot. In the following procedures, you will create an OData feed to expose data from the CustTransList query, and then import the data into PowerPivot.
-
Click Organization administration > Setup > Document management > Document data sources.
-
Click New.
-
Enter the following values:
Field
Value
Module
Accounts receivable
Data source type
Query reference
Data source name
CustTransList
Activated
Select the Activated check box.
Description
Enter a description of the feed.
-
Close the Document data sources form. You can test the OData feed by navigating to the following URL:
http://<ServerName>:8101/DynamicsAX/Services/ODataQueryservice/.
[!NOTE]
You may need to adjust the port number in the URL. 8101 is the default port number.
-
In the same Excel workbook you used earlier in this walkthrough, click the PowerPivot tab.
-
Click PowerPivot window.
-
Click From Data Feeds.
-
In Data Feed Url, enter http://<ServerName>:8101/DynamicsAX/Services/ODataQueryservice/.
-
Click Next.
-
Select CustTransList, and then click Finish.
Now that you have data from the list page and from the OData feed, you can link the data and then view it as a pivot table.
-
In Excel, click the PowerPivot tab.
-
Click Create Linked Table.
-
Right-click the sheet title, and then click Rename. Enter Customers to rename the sheet from Table to Customers.
-
In PowerPivot, click PivotTable.
-
Click OK to create a pivot table in a new sheet.
[!NOTE]
Notice that the pivot table contains data from both sources of data: the Customers data you exported from the list page and the data from the CustTransList query that is exposed by the OData feed.
-
Expand CustTransList and then select CustTrans_AmountMST. The total sales value is displayed in the pivot table.
-
Expand Customers and then select Segment. The segment value is displayed in the pivot table.
-
Click Create on the warning that says Relationship may be needed. Click Close.
After you complete this walkthrough, you can upload a PowerPivot model (your Excel sheet) into SharePoint library, and use it to create Power View reports. For more information about creating Power View reports, see Create a report by using Power View to connect to a cube and Walkthrough: Creating an Analyze Data Button on a List Page.
Reporting in Microsoft Dynamics AX