Using Excel for Interactive Reporting with Dynamics AX 2012 Data

By Bill Thompson | October 29, 2015

Using excel for interactive reporting with dynamics ax 2012 data

There are many ways to get data into Microsoft Excel from Dynamics AX 2012 for reporting purposes.  The purpose of this article is to demonstrate quickly what you can do within Excel AFTER the data is in Excel.

The request

For our purposes, let’s assume that a request has come in to see the distribution of our customers across the country. This must be a very visual report that quickly shows this information with virtually no analysis by the end user. It has been decided that a heat map may be the best way to show this information.

SQL Server Reporting Services can be used to create a heat map report. However, by using the Power View functionality that comes with Office 2013 (and Office 2016), this can be quickly created, AND as an added bonus allow the user to interact with the report.

Step 1.  Get the data into Excel

This can be done in many ways. For purposes of this demo, a job has been created to read the customer account, zip code, and country and insert that into Excel. Here is the job:

static void HeatMapDemoJob(Args _args)
{
    // Excel object definitions
    SysExcelApplication     xlsApplication;
    SysExcelWorkBooks       xlsWorkBookCollection;
    SysExcelWorkBook        xlsWorkBook;
    SysExcelWorkSheets      xlsWorkSheetCollection;
    SysExcelWorkSheet       xlsWorkSheet;
    
    // random variable declarations
    int                     row = 2;
        
    CustTable               custTable;
    
     // define and initialize the progress bar so the user knows what is going on
    SysOperationProgress progress = new SysOperationProgress();
    #AviFiles

    Progress.setAnimation(#aviTransfer);
    progress.setCaption("Sending information to Excel");
    progress.setText("Processing...");
    progress.update(true);
    
    //Initialize Excel instance
    xlsApplication           = SysExcelApplication::construct();

    //Create Excel WorkBook and WorkSheet
    xlsWorkBookCollection    = xlsApplication.workbooks();
    xlsWorkBook              = xlsWorkBookCollection.add();
    xlsWorkSheetCollection   = xlsWorkBook.worksheets();
    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);

    //Excel columns captions

    // columns should autofit
    xlsWorkSheet.columns().autoFit();

    // headings
    xlsWorkSheet.cells().item(row,1).value("Customer account");
    xlsWorkSheet.cells().item(row,2).value("Postal code");
    xlsWorkSheet.cells().item(row,3).value("Country");
    
    row++;
    
    //Fill Excel with dataTable info
    while select custTable
    {
        progress.setText(strFmt("Transferring customer %1",custTable.AccountNum));
        xlsWorkSheet.cells().item(row,1).comObject().numberFormat('@');
        xlsWorkSheet.cells().item(row,1).value(custTable.AccountNum);
        xlsWorkSheet.cells().item(row,2).comObject().numberFormat('@');
        xlsWorkSheet.cells().item(row,2).value(custTable.postalAddress().ZipCode);
        xlsWorkSheet.cells().item(row,3).comObject().numberFormat('@');
        xlsWorkSheet.cells().item(row,3).value(custTable.postalAddress().CountryRegionId);
        row++;
        
    }
    
    //Open Excel document
    xlsApplication.visible(true);
}

When this is run, Excel will populate with the desired data (NOTE: this demo is using the Dynamics A X 2012 R3 demo data within the USMF legal entity)

Excel Table

Step 2.  Create the report

To create the report, Power View is used.  When the Power View button is clicked, the following is displayed:

Report in Power View

 

Click the Map button found in the Ribbon, and then resize the map that is displayed so it is more easily viewed:

Map in Excel

 

NOTE: You may get a message stating that the data may need to be geocoded. This is needed for the report to work as expected. This goes and used Bing maps for geographic data that will encoded and displayed on the map.

There is a message displayed stating that too many customer account values exist for this report. To remove this, simply uncheck the customer account field in the Power View Fields box. To filter by country, drag the country field into the filter area of the report. Select USA in the filter area, and a heat map displaying the locations of the USA based customers for the demo data is displayed:

Map in Excel

 

At this point, the mouse may be used on the map to zoom into different areas of the map for more detail.

Map in Excel

 

I hope that this provides a little insight into how Dynamics AX 2012 data can be reported on with not much work within Microsoft Excel.

 

Related Posts

Recommended Reading:

Manage U.S. Use Tax on Purchase Orders in Dynamics 365 Finance and Operations

  Managing sales tax requirements on your business purchase can be complicated, but Dynamics 365 Finance and Operations can help […]

Read the Article
5.19.22 Dynamics CRM

How to Write a Great Support Ticket in the Stoneridge Support Portal

Submitting a support ticket through the Stoneridge Support Portal is a quick and effective way to get assistance for any […]

Read the Article

Managing Your Business Through Uncertain Times Using Dynamics 365 Finance and Operations

  Dynamics 365 Finance and Operations (F&O) can help you make informed decisions on how to move your business forward. […]

Read the Article
5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article
5.5.22 Dynamics GP

The Real Story about the Long-Term Future of Dynamics GP Support

I’ve seen a number of people put forward comment that Dynamics GP is going away and you have to get […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release – Financial Enhancements

The Dynamics 365 Businses Central 2022 Wave 1 Release has a lot of new and exciting features to help your […]

Read the Article
4.29.22 Dynamics GP

Dynamics GP Transaction Removals: Bank Reconciliation

  This is part 2 of a 3 part series on Dynamics GP Transaction Removals. These quick tips will hopefully […]

Read the Article

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X