Working with Microsoft Dynamics AX 2012’s Excel Add-In Tools

ERP software users often look for ways to eliminate the complexity of reporting and their reliance on IT staff and consultants to produce reports.  Within Microsoft Dynamics AX 2012’s sophisticated architecture, reporting, business intelligence gathering, and data mining can present challenges for some.  To put reporting back in the hands of those who need it, Microsoft Dynamics AX provides the Microsoft Excel Add-In.

The Microsoft Excel Add-In allows users to take data directly from Microsoft Dynamics AX for analysis within the familiar and powerful interface of Microsoft Excel.

NOTE:  The Office Add-ins for Microsoft Dynamics AX must be installed by your System Administrator for the Microsoft Excel Add-in Tool to be available.  In addition, your user role must have appropriate permissions.

If Excel Add-In Tools are available you should see a Microsoft Dynamics AX tab within Microsoft Excel. This tab is where connections and data sources to Microsoft Dynamics AX can be readily controlled. (other versions of Microsoft Excel will have variations of this menu).

 

 

Connecting or Switching between Microsoft Dynamics AX Environments:

1. Click OPTIONS from Dynamics AX toolbar.
2. Enter the CONNECTION detail to switch between TEST, DEVELOPMENT or LIVE production environments.

Connections provides quick access and switching between TEST or DEVELOPMENT areas before connecting and deploying in LIVE production environments.

 

 

Accessing Microsoft Dynamics AX Tables:

1. Click ADD DATA from the Dynamics AX Toolbar
2. Select Table(s) from the list of options using the arrow keys.
3. Click OK to import into Excel

Connecting to tables provides instant access to any raw information in the database. (HINT: In the Microsoft Excel worksheet, you can only refresh your data with the ‘Field Chooser’ off. Use this toggle and you will see the ‘Refresh All’ button light up).

You can also connect different worksheets in the same Microsoft Excel file to different Microsoft Dynamics AX data sources, and use tools such as VLOOKUP to make use of interactions between them, or simply use certain worksheets as detail data for summarized information elsewhere.

 

 

Accessing other Microsoft Dynamics AX Data Sources:

Connecting to a data source from an Application Object Tree (AOT) Query provides a powerful source for multi-level data.

 

Microsoft Dynamics AX AOT queries are the source for most SQL Server Reporting Services reports. Connecting to these queries from Microsoft Excel is an easy way to create an automatically updatable file of your key information at your fingertips. It is also a quick way to add a few missing pieces of data to an existing report, since you can add fields in Microsoft Excel that might not be displayed as part of the report in the Microsoft Dynamics AX interface.

AOT queries are not available by default from Microsoft Excel. One simple step is required in Microsoft Dynamics AX:

1. Using the menu go to:  ORGANIZATION ADMINSTRATION > SETUP > DOCUMENT MANAGEMENT > DOCUMENT DATA SOURCES
2. Click NEW
3. Enter your data source details.
4. Click OK to create the link

 

In addition to accessing thousands of individual tables and hundreds of pre-built queries, you can create your own queries in the Microsoft Dynamics AX AOT. A simple approach would be to copy an existing query and modify it by adding links to another table where the information you need is stored.

CAUTION:  If you change existing queries rather than copies, you will very likely impact existing reports.

Using the AOT you can determine the links between tables and simply drag and drop additional tables to the data source branch (open the AOT twice to drag and drop).

 

 

Microsoft Excel has long been a popular tool for number crunching and analysis. With the Microsoft Dynamics AX 2012 Excel Add-In, the speed of refreshing data and direct connection providing ‘one version of the truth’ combine with Excel’s highly flexible tools and familiar features to provide a simple yet exceptionally effective reporting tool.

For more tips about Microsoft Dynamics AX Enterprise Resource Planning (ERP) Software, add BDO’s ERP blog to your browser favorites. BDO Canada has a consulting team comprised of finance, manufacturing and technical professionals with in-depth knowledge of Microsoft Dynamics AX.

BDO Solutions is a national firm with local practices throughout Canada, a Microsoft Gold Certified Partner and Reseller of the Year in Canada for 2010, 2011 and 2012.

By Ryner Rolinski, Senior ERP Solutions Consultant with BDO Solutions, specializing in Microsoft Dynamics AX implementations for manufacturing clients.