Walkthrough: Creating a PowerPivot Data Mash-up

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

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.

Prerequisites

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.

Extracting Data from a List Page into Excel

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.

To add columns to the list page

1. Click Accounts receivable > Common > Customers > All customers.

2. Right-click the column header of the grid and then click Personalize.

3. Expand ListPageGrid.

4. Click Add fields.

5. Add the following fields from the Customers table:

   • Method of Payment

   • Segment

   • Subsegment

   • Customer classification group

6. Close the Add fields window and refresh the form.

To extract data from the list page

• After the All customers list page is refreshed, click Export to Excel List.

Importing Data into PowerPivot by Using an OData Feed

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.

To create an OData feed

1. Click Organization administration > Setup > Document management > Document data sources.

2. Click New.

3. 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.

4. 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.

To import content using the OData feed

1. In the same Excel workbook you used earlier in this walkthrough, click the PowerPivot tab.

2. Click PowerPivot window.

3. Click From Data Feeds.

4. In Data Feed Url, enter http://<ServerName>:8101/DynamicsAX/Services/ODataQueryservice/.

5. Click Next.

6. Select CustTransList, and then click Finish.

Creating a Pivot Table

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.

To create a linked table

1. In Excel, click the PowerPivot tab.

2. Click Create Linked Table.

3. Right-click the sheet title, and then click Rename. Enter Customers to rename the sheet from Table to Customers.

To create a pivot table

1. In PowerPivot, click PivotTable.

2. 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.

3. Expand CustTransList and then select CustTrans_AmountMST. The total sales value is displayed in the pivot table.

4. Expand Customers and then select Segment. The segment value is displayed in the pivot table.

5. Click Create on the warning that says Relationship may be needed. Click Close.

Next Steps

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.

See also

Reporting in Microsoft Dynamics AX

Walkthrough: Creating an Analyze Data Button on a List Page

OData Query Service