Using Excel for Interactive Reporting with Dynamics AX 2012 Data

by | Updated August 15, 2016 | Dynamics AX, Office 365, Reporting

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:

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

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

september

02sep10:00 am10:30 amThe Modern Manufacturer - Cycle Count Management

02sep12:00 pm12:30 pmBest Practices for Work Order Planning and Routing for Field Service Companies

09sep10:00 am11:00 amWhat is Levridge? An Overview of the Ultimate Ag Solution

09sep12:00 pm12:30 pmOverview of Microsoft Promotion for NAV and GP Clients

10sep11:00 am12:00 pmConfab with Stoneridge - Livestream - Field Service Comparisons

16sep10:00 am10:30 amThe Modern Manufacturer - Product Lifecycle Management

16sep12:00 pm12:30 pmHow to Upgrade to Dynamics 365 Finance and Operations: Examining Custom Code and ISV Implications

16sep2:00 pm2:30 pmScheduling and Dispatching for the Modern Field Service Company

23sep12:00 pm1:00 pmDynamics 365 Business Central Wave 2 Release Preview - An Overview of the Most Exciting New Features Coming in October

24sep11:00 am12:00 pmConfab with Stoneridge - Livestream - The Modern Workplace: Teams, Sharepoint, Outlook, Office, and AI Integration

30sep10:00 am10:30 amThe Modern Manufacturer - Return Management

30sep12:00 pm12:30 pmEmpowering Your Field Service Technicians with a Mobile Solution

october

07oct12:00 pm1:00 pmThe Three Paths to Business Central from Dynamics GP

08oct11:00 am12:00 pmConfab with Stoneridge - Livestream - The Vision and Strategy of Microsoft Business Systems

14oct10:00 am10:30 amThe Modern Manufacturer - Complex Cost Modeling

14oct12:00 pm12:30 pmGenerating Custom Inspection or Process Forms

19octAll Day22Stoneridge Connect Fall 2020

22oct11:00 am12:00 pmConfab with Stoneridge - Livestream - Stoneridge Connect Recap

28oct10:00 am10:30 amThe Modern Manufacturer - Engineering Change Orders

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X