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.

Dynamics AX sales data in PowerMap

Prerequisites

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

image

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.

image

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

image

Enable PlugIns

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

image

          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.

      image

      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.

      image

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

      Advertisements

      Über erpcoder
      Software Engineer and Dynamics AX enthusiast working in Research & Development for InsideAx

      One Response to View Sales Data on Map in Excel 2013

      1. Pingback: Create a Power BI Dashboard for Dynamics AX 2012 Sales | ErpCoder

      Kommentar verfassen

      Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

      WordPress.com-Logo

      Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

      Twitter-Bild

      Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

      Facebook-Foto

      Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

      Google+ Foto

      Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

      Verbinde mit %s

      %d Bloggern gefällt das: