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:

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

staticvoidHeatMapDemoJob(Args _args)

{

// Excel object definitions

SysExcelApplication xlsApplication;

SysExcelWorkBooks xlsWorkBookCollection;

SysExcelWorkBook xlsWorkBook;

SysExcelWorkSheets xlsWorkSheetCollection;

SysExcelWorkSheet xlsWorkSheet;

// random variable declarations

introw=2;

CustTable custTable;

// define and initialize the progress bar so the user knows what is going on

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)

Step 2. Create the report

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

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

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:

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

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.

About Bill Thompson

Bill is a Principal Developer at Stoneridge Software focusing on Microsoft Dynamics AX services, X++ development and report customizations. He is an 11-year veteran of Microsoft, providing support to partner and ISV development teams. Bill has led many sessions on Dynamics AX development, and over the years has trained hundreds of users.

Upcoming Events

Event Details

Do you have a crystal-clear, real-time understanding of your business numbers? Can you articulate where money is coming from, where it’s going, and what’s on the horizon? If not, you

Event Details

Do you have a crystal-clear, real-time understanding of your business numbers? Can you articulate where money is coming from, where it’s going, and what’s on the horizon? If not, you need a business scorecard. We’ll show you what a weekly scorecard consists of, and how your business can gain greater insight by using one.

Location

Organizer

Event Details

More often than not, when business leaders hear the words “ERP” they cringe at the thought and re-live pain they may have experienced during their last implementation. Having led hundreds

Event Details

More often than not, when business leaders hear the words “ERP” they cringe at the thought and re-live pain they may have experienced during their last implementation. Having led hundreds of implementations, our team sees ERP as an opportunity for increased productivity and profit, not your next daunting task.

Location

Organizer

Event Details

If you're new to Microsoft Dynamics GP, this series of classes will introduce you to the software and show you the basic elements. Throughout the week you will

Event Details

If you’re new to Microsoft Dynamics GP, this series of classes will introduce you to the software and show you the basic elements. Throughout the week you will be introduced to general ledger, payables management, receivables management, and bank reconciliation. Upon completion, you will have full knowledge of the GP financial module and will be able to perform basic and advanced functions.

Day 1: Introduction to Microsoft Dynamics GP

8:30 a.m. – 5 p.m.

This one-day course explores the basic elements of Microsoft Dynamics GP. Topics covered during this session include system and company setup procedures, how to use reports and inquiries, how to use Smart List to expand inquiry and analysis capabilities and tips for the user to personalize Microsoft Dynamics GP to streamline business practices.

Day 2: General Ledger in Dynamics GP

8:30 a.m. – 5 p.m.

This course explores the accounting cycle and the processes required to enter financial data into the system. It covers how to set up general ledger accounts, enter general ledger transactions, and post the transactions. This course also shows how to perform additional functions such as account reconciliation, budgeting, allocation, period-end procedures, and year-end closing procedures.

Day 3: Payables Management in Dynamics GP

8:30 a.m. – 5 p.m.

The one-day Microsoft Dynamics® GP Payables Management course examines the accounting cycle and the processes required to enter vendor invoices and process checks. This course also shows you how to perform additional functions such as adjustments, prepayments, month-end closing, and cash flow control.

A thorough understanding of these topics allows for capitalizing on vendor discount dates to reduce accounts payable liabilities and easily select the right vouchers for payment by date, vendor, number, or custom query to control cash outlay. This course also provides guidance on how to define default values that automatically populate data fields and how to gather the information needed to make better business decisions by exploring powerful inquiry and lookup functions.

This course explores the processes required to manage cash receipts, cash disbursements, and other transactions that affect checkbook balances. It also shows you how to perform the checkbook reconciliation process in the Bank Reconciliation module, ensuring the accuracy of your financial data and its relation to your financial institution statements.

This class runs from 8:30 a.m. – 5 p.m. Monday – Thursday, and 8:30 a.m. – Noon on Friday. If you would like to register for a single day class, please email solutions@stoneridgesoftware.com.

Location

Organizer

Event Details

Included in the Dynamics GP Extended Pack, Field Service offers companies the ability to track equipment use, preventative maintenance, and contract administration. During this webinar, Bobbi Horn will showcase the

Event Details

Included in the Dynamics GP Extended Pack, Field Service offers companies the ability to track equipment use, preventative maintenance, and contract administration. During this webinar, Bobbi Horn will showcase the features included with Field Service including service types, equipment records, problem-cause resolution codes, scheduling individual or multiple technicians to a call, Linking to point-of-sale, and Tracking labor time.

If you have employees or technicians that are on customer site and still using paper forms, or if you have equipment or vehicles that are being checked out or used for different projects you should attend this webinar.

Organizer

Event Details

Are you investing valuable resources on internal projects that take time away from managing and completing organizational goals? Stoneridge Software’s AX | D365 F&O client support team offers more than

Event Details

Are you investing valuable resources on internal projects that take time away from managing and completing organizational goals? Stoneridge Software’s AX | D365 F&O client support team offers more than break-fix assistance. Support solutions should be a value-add for your organization, allowing your employees to receive expert guidance when it comes to your AX | D365 F&O system and processes. During this short 30 minute webinar, you’ll hear how to save time, money, and resources with support options available including:

Location

Organizer

Event Details

Curious about career opportunities at Stoneridge Software? Join us for happy hour at Drekker Brewing Co.! You’ll have the opportunity to meet current team members and do some networking, as

Event Details

Curious about career opportunities at Stoneridge Software? Join us for happy hour at Drekker Brewing Co.! You’ll have the opportunity to meet current team members and do some networking, as well as learn more about current openings, company perks, and our work culture. We have numerous job openings, which include senior-level positions as well as junior-level positions in our New Hire Academy and internship opportunities for college students. If you think it sounds like fun, your right!

Event Details

Now is the time to discover why the majority of companies currently using AX will move to Dynamics 365 Finance and Operations over the next few years. During this webinar,

Event Details

Now is the time to discover why the majority of companies currently using AX will move to Dynamics 365 Finance and Operations over the next few years. During this webinar, you’ll see first hand what business processes and user experience will look like in Finance and Operations. You’ll also hear the top reasons why business leaders are moving from AX to Dynamics 365.

Understand how your IT department will be affected by moving to the cloud

See the modern user experience and learn why it’s important when hiring new talent

Learn how to identify any conflicts or issues in the system prior to any updates with automated testing

See how easy it is to generate reports with PowerBI

By the end of this webinar, you’ll have a solid understanding of why current AX clients are steadily moving to Finance and Operations and if it’s worth it for your company to upgrade.

Contact Info

Follow Us Online

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.