Finance experts often need to generate reports that combine data that are recorded in Dynamics AX with data from external data sources. An example for those reports are consolidation reports where financial data from subsidiaries are delivered in Excel spreadsheets that need to be combined with data out of Dynamics AX. Another example are Actual-Budget comparison reports where actual data are retrieved from Dynamics AX and need to be combined with budget data that are recorded in Excel.

This blog post illustrates two different possibilities how data from Dynamics AX and data that is recorded in Excel spreadsheets can be combined into one single Management Reporter report by using the aforementioned Actual-Budget comparison example.

Importing data from Excel into Management Reporter reports can be done either by using the so-called “combined link approach” or by using the “separate link approach. Both approaches will be presented in the following.

Option 1: Combined link approach
The first option to import Excel data into Management Reporter reports is using the combined link approach. This approach is characterized by the fact that financial dimension values out of Dynamics AX and external Excel data are combined into one single column in the Management Reporter row definition window. The following screenshot illustrates this setup in detail by highlighting the references to Dynamics AX data in yellow color and the references to external Excel data in green color.

Unfortunately, Management Reporter does not allow combining references to Dynamics AX data and to external Excel data into one single row.

This is why three single lines – two non-printing lines and one total line – are used in this example for reporting on each element, such as for example Sales, COGS, etc.

In addition to the row definition, a column definition needs to be setup with a column type “WKS” that incorporates the external worksheet data.

The last step required is combining the row definition and column definition into a report and to generate the report. Result:

Please note the following when using the combined link approach

• The reference to the external worksheet data defined in the Management Reporter row definition window links the information of the column definition window to the data included in the Excel spreadsheet. As an example, the reference “@WKS(G=C3)” defines that the value included in cell C3 of the Excel spreadsheet is included in column G of the Management Reporter report. Please have a look at the following illustration.
  
• The references used by the combined link approach to the external worksheet data are static. That is, if you make a change to your report columns, all “@WKS”-rows need to be adjusted. Otherwise, values from the wrong Excel cell are picked up and reported. This fact limits the use of the combined link approach to situations where a small number of external Excel data need to be incorporated into Management Reporter reports. If large amounts of external Excel data need to be included in Management Reporter reports, the separate link approach appears to be the more suitable option.

Option 2: Separate link Approach
The second approach for importing external Excel data is using the separate link approach. The major difference to the combined link approach is that the references to Dynamics AX data and to the external Excel data are established in two (or more) separate columns in the row definition window. This is illustrated in the following screenshot.

The second major difference is that the link to the external Excel document is not made in the row link window illustrated above but in a reporting tree as illustrated in the following screenshot.

As before, the last step required is combining the row definition, the column definition and the reporting tree definition into a report and to generate the report. Result:

Please note the following when using the separate link approach

• References to external Excel data are made by using the keywords “CPO” and “RPO” to define the direction in which the external Excel data are read during the import (CPO => column by column, RPO => line by line, see the following illustration).
  
• Different from the combined linked approach, the separate link approach uses the period value in the column definition to reference the respective Excel cell. In the example used, the budget sales value reported in the first budget column is picked up by starting at cell B3 and by moving one cell to the right. The budget sales value reported in the second budget period is picked up by starting at cell B3 and by moving 2 cells to the right and so on. This relative cell reference makes the separate link approach very robust. That is, even if you add or remove columns in the column definition window, your report will still pick up the correct values from Excel.
• Due to the relative simple and robust setup framework, the separate link approach is most suitable in situations where large numbers of data need to be imported from Excel into Management Reporter reports.