Management Reporter Advanced: Excel Series – Importing non financial data and perform calculation on report

One of the great features about Management Reporter is the ability to link to cells in external Excel workbooks. There are alot of possibilities that comes with this feature. In these special Excel Series, I am going to go though some concepts to bring you up to speed on how you can work with Management Reporter and Excel.

 Overview

This blog post will focus on importing non financial data for calculation in Management Reporter.

This example includes fetching salary from financial dimensions in AX, and combining them with number of employees per month to get an average head count.

Here is the report we will create:

I have a Excel workbook with monthly herad count reported from an external contributor. The workbook includes several Worsheets, so we will have to make sure we specify “Sheet4” as our Workbook template.

Since we are only using one single Workbook in this example, we can use the Financial Dimension + External Workbook Row Link in Management Reporter. This allows us to combine financial dimension data from AX together with the data from Excel, without having to use a Reporting Tree Definition.

Create a new Row Definition and click on Edit -> Row Links… from the menu:

Set the Link Type to Financial Dimensions + Worksheet, and give it a suitable Link Name and Description. Under the Worksheet definition tab, select the External workbook path option and press the button to select the path of your Excel Workbook file. Lastly, specify the Worksheet name as Sheet4.

Click OK to go back to your Row Definition.

On the Row Definition, create two lines, one for Salaries and one for Employees.

For the Salaries line, select MainAccount dimension accounts in which your companys salaries are posted in AX.

For the Employees line, enter the following code:

@WKS(B=B4, C=B4, D=B4, E=B4, F=B4, G=B4, H=B4, I=B4, J=B4, K=B4, L=B4, M=B4) /RPO

• @WKS defines that our external workbook is used
• B=B4, C=B4, D=B4, E=B4, F=B4, G=B4, H=B4, I=B4, J=B4, K=B4, L=B4, M=B4 defines which row to use in the different columns in the report. The B, C, D… defines in which column the fetched number will be included in Management Reporter. The B4 is defining which row in the Excel Workbook should be fetched. Now, here is the magic: By default, you have to define the row BEFORE the first row you are going to use in Management Reporter = B4 from the Excel file. The PERIOD defined in the Column Definition (Described below) is defining which row to fetch the number from in the Excel workbook, so we just set all columns to B4.
• /RPO (Row Period Offset) defines that Management Reporter will start with the first row and then add one row for each period

Next we will create a new Column Definition:

Create one column for each period (month), and set the first period to BASE, and then add one for the next periods (BASE+1, BASE+2…). Use FD (Financial Dimension) as Column Type, as we include both financial dimension from AX and Workbook Sheet. Finally, add a CALC column to calculate total for all months. Don’t forget to include a description column, as this is mandatory (to print the text on the report).

The last step is to setup our Report Definition to define the report. As you may see from my example below, I only use Row Definition and Column Definition (no Report Tree Definition). Generate the report.

As you can see from the report, it has fetched the row with the numbers from the Excel Worksheet:

That’s it!