View Sales Data on Map in Excel 2013

Excel provides great BI features for end users and professionals. Loading, transforming and presenting data can easily be done with PowerView and PowerMap. This is an example to visualize Dynamics AX sales data.

Prerequisites

• Microsoft Dynamics AX 2012 R2
  (you could access the data via SQL ODBC to support older versions of Dynamics AX)
• Excel 2013
• PowerMap (download here http://www.microsoft.com/en-us/download/details.aspx?id=38395)

Provide Data via ODataFeed

1. Open Dynamics AX 2012 R2 development workspace
2. Create a new Query called ERPCustInvoiceJour
3. Add the CustInvoiceJour as datasource, set the Fields Dynamic property to false
4. Add InvoiceAmount, InvoiceDate and CustGroup to the field list
5. Add the LogisticsPostalAddress
6. Add the CountryRegionId and City to the field list
7. Set the Relations property to No
8. Add a new relation manually, clear the Field and Related Field property but select InvoicePostalAddress_FK

In Dynamics AX Application workspace go to Organisation Administration > Setup > Document Management > Document Data Sources. Create a new record for module “Sales and Marketing”, type Query Reference for ERPCustInvoiceJour query. Enable the data source.

Open the ODataFeed in a browser, depending on the server name and AOS Port it should look like this http://localhost:8101/DynamicsAx/Services/ODataQueryService/ERPCustInvoiceJour

Enable PlugIns

1. Open Excel and go to File > Options > Add Ins > COM Add-ins > Go…
2. Enable PowerPivot, PowerView and PowerMap

Create Map

In Excel go to DATA > From other sources > Data Feed > provide the URL from Dynamics AX data feed. Load the data in Excel. go to INSERT > Map. Set the LogisticsPostalAddress_City as Geography field and click next.

Leave the Type as Column. Set the CustInvoiceJour_InvoiceAmount as Height for the Column. Set the CustInvoiceJour_CustGroup as Category and CustInvoiceJour_InvoiceDate as Time.

Run the map time line and watch where and when sales takes place. Watch the implementation in this short video