This post deals with maturity structures in financial reports and how one can create those structures in the standard MSDyn365FO application. In total two different approaches how those structures / reports can be created are illustrated together with their advantages / disadvantages and prerequisites one has to take care of.
In this webcast I will show you different options how one can generate and distribute financial reports to managers. A special focus is made on security issues and things that you have to take into consideration when distributing financial reports in the way presented.
This webcast demonstrates how one can create a stockholders equity statement in the standard MSDyn365FO application with the help of reason codes and the Management Reporter. A major focus is made on demonstrating how to setup the Management reporter stockholders equity report that necessitates the calculation of multiple beginning and ending balances for the different periods covered. The principle demonstrated in this webcast can be applied to other reporting scenarios such as cash flow reports or reports that detail accruals and changes in accrual balances.
Linking different Management Reporter (MR) reports is a common finance scenario when for example the profit/loss from the Income Statement (IS) is linked to the equity section of the Balance Sheet (BS).
Microsoft provided a detailed description, which exemplifies how this linkage between the IS and BS reports can be established. For details, please see the following website.
Some time ago I was confronted with the requirement of linking multiple MR reports to a newly created one. As Microsoft already provided a detailed description of the necessary setup steps, I initially thought that linking multiple MR reports just follows the same principle, which is required for linking a single report.
For that reason (and based on the Microsoft guideline), I simply added two instead of a single row link in my MR row definition to get the data from the BS and IS report loaded into my newly created report. The next screen-print exemplifies this setup, where the first row link (‘BS’) links to the Balance Sheet report and the second one (‘IS’) links to the Income Statement report.
The column definition used was identical to the one used in the Microsoft guideline and included only a single FD (financial dimension) column. The next screen-print illustrates the setup of this so-called column definition.
Based on this warning message, the report setup with the two row links obviously seemed to be the wrong approach to incorporate data from multiple other MR reports into my newly created one. To get this corrected, I first changed the link type for the Income Statement data to ‘Management Reporter Worksheet’.
In line with this change, the cell reference was changed from ‘@WKS(B=C35)’ to ‘C35’ and a column restriction was incorporated into the row definition setup, which is exemplified in the next screen-print.
It is important that all financial dimensions/worksheet references, which are specified in the row definition form are included in the reporting tree. Otherwise, the report generation will interrupt with an error message.
With those report modifications in place, the report could finally be created and showed all the data retrieved from the other MR reports. The report could of course be further refined by including the values shown in the FD and WKS column into a single one. For reasons of brevity this exercise is, however, skipped here and left as an exercise for the reader.
In order to allow you a direct comparison with the Microsoft guideline referenced in the beginning, all setups and reports exemplified in this post have been created with the MR version CU12. In more recent MR versions, the link type ‘Financial dimension + Worksheet’ has been removed. As a result, linkages to multiple MR reports can only be realized through the ‘Management Reporter Worksheet’ link that has been used for incorporating the IS data above.
Within this post I would like to present a feature that allows you restricting the access to data included in Management Reporter financial reports. A common business scenario where this feature can be applied are situations where business unit/cost center managers shall only have access to the data of the business unit/cost center they are responsible for.
Let’s have a look at the following Income Statement report that shows the total amounts split up by business units to see how the Management Reporter unit security feature can be applied.
Assume that we want to restrict the report data access for Phyllis, the manager of business unit BU001, in a way that she has Access to the Income Statement data of her business unit only. This restriction can be accomplished by setting up a report with a reporting tree that includes the respective users in the unit security column. Because the report includes all business units next to each other in separate columns, the reporting tree elements need additionally to be linked to the respective columns. This is linkage and the unit security setup is illustrated in the next screen-print.
With this setup in place, Phyllis will see the following report, once she opens it.
The security restriction setup does also apply to the Management Reporter drill down functionality in Dynamics AX. That is, if Phyllis wants to see the voucher transaction details that make up a specific amount shown in the report and drills down into the Dynamics AX voucher transactions, she will only see the filtered transactions for her business unit. This system behavior is illustrated in the next screen-print:
A second thing to note here is that the Management Reporter unit restriction does not control whether or not Phyllis can open and access all transaction details through the Dynamics AX client directly. In the example used Phyllis can, for example, log into the Dynamics AX client and access the complete voucher details including the amounts that were recorded for the other business units. Example:
If you have users that can circumvent the Management Reporter unit restriction this way, you have ensure that you also implement the extensible data security framework in Dynamics AX. Otherwise, the unit security feature does not make much sense. Please see the following sites 1 / 2 for additional information on the extensible data security framework.
Report modification 1
Now let’s have a look at some Management Reporter design considerations that you should be aware of when making use of the unit security feature and reporting trees in general. Let’s imagine that the Income Statement report shown previously is modified in a way that also the cost centers that make up a business unit are included in separate report columns. If you follow the same unit restriction setup shown previously also for the sub-elements of the business units …
… users won’t see all details once they open their report. In the example used Phyllis, will see the following data when opening the report:
The data for the cost centers that make up the business unit cannot immediately be identified but rather have to be opened separately by selecting one of the business units. Example:
Report modification 2
Because of the previous data illustration issue, the report is modified once again in a way that the business unit and total columns are now setup as computed columns. (Please see the yellow highlighted section in the next screen-print).
With this setup in place, Phyllis will get the following message once she opens the report.
By selecting one of the cost center elements, she will finally be able to see the data for the selected cost center but not automatically for the other ones without selecting them separately.
Report modification 3
To avoid that the Income Statement report opens with an error message for the business unit / cost center managers, the report design is further modified as follows:
The major difference to the previous setup is that cost center sub-elements that make up the business division are now intended in the reporting tree. This setup finally ensures that Phyllis is able to see all data of the business unit she is responsible for when opening the report.
If Phyllis drills down to the different sub-elements, the report will filter the selected data respectively. Example:
I hope the different examples gave you an impression what to look out for when designing Management Reporter reports that include reporting trees and make use of the unit security feature. The major take away from this post is that setting up the unit security feature in Management Reporter does not make much sense if users can circumvent those restrictions by extracting the data directly from the AX client. To avoid such scenarios you have to ensure that the extensible data security framework is applied concurrently in the AX client.
When doing a financial consolidation, financial data from different companies need to be combined in a single “consolidation” report. As the data that need to be consolidated are often based on different Chart of Accounts (COA), the question arises how ledger accounts from different COA’s can be combined into a single consolidation report. This question will be answered in the following based on a simplified example.
The financial data of company DEMF and BRMF need to be consolidated. While the first company (DEMF) uses a “shared” COA, the second company (BRMF) uses a local Brazilian COA. What makes things even more complex is that company DEMF records all transactions in EUR-currency, while company BRMF keeps all its books in Brazilian reals. The next screenshot shows you the COA’s and currencies setup for the two companies.
In order keep the example as simple as possible, only the bank accounts of both companies are considered for the following illustrations.
In company DEMF only one single transaction with a total amount of 10000 EUR has been recorded on bank account (main account) “110200”.
In company BRMF two local currency transactions have been recorded on the bank account (main account) “220.127.116.11.02”.
The exchange rate used for consolidation purposes is 300 BRL / 100 EUR.
Against the background of those data, a financial consolidation report should show a total balance of 50000 EUR or 150000 BRL in the bank accounts line.
To realize this, the following setup has been done in Management Reporter.
Step 1: Setup of the row definition
The first step in setting up the consolidation report is mapping the different main accounts in the row definition of the report. This mapping in done by specifying separate row links that hold the main account information of both companies. Please see the following screenshot for an example.
Step 2: Setup of the reporting tree Definition
The next step is setting up a reporting tree. When doing this setup, you have to make sure that a reference is made between the different companies and the row definition / row links established in the first step.
Step 3: Setup of the column Definition
The third step is setting up a column definition as illustrated in the next screenshot.
Please note that you need to link the different columns with the reporting tree elements if you want to report on the different legal entities.
Step 4: Setup of the report Definition
The last step in the report generation is the combination of all previous elements (row definition, reporting tree and column definition) into a report. Please note that you need to select the reporting currency check box before running the report if you want to do a currency conversion once the report that gets generated.
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.
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.
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.
The following screenshots provide you some additional information on the fixed asset posting profile setup used for the examples illustrated in the blog posts. Please not that separate ledger accounts are used for each combination consisting of (a) the fixed asset transaction type, (b) the fixed asset group and (c) the value model.
Please note that the sales disposal parameters illustrated in the following screenshot could be setup in more detail. Yet, the major point that I want to make here is that the ledger accounts that are setup in this section differ from the ones used for recording the original fixed asset transaction. As an example, fixed asset acquisitions are recorded on ledger account 180500, while the offset transaction that is recorded when the fixed asset is sold is recorded on ledger account 180502.
Step 6: Setup of the fixed asset statement report in Management Reporter
The following illustrations on the setup of the fixed asset statement in Management Reporter will be done by referring to sample fixed asset transactions that directly address the problems of the standard Dynamics AX fixed asset statement report mentioned previously. For that reason, the following transactions have been recorded for six fixed assets (“FA0035” to “FA0040”) in 2017.
After recording the fixed asset transactions illustrated in the previous screenshot, the following row definition is setup in Management Reporter.
@ Issue 5: „Include prepayments, long-term financial assets and alike in the fixed asset Statement Report“
What you can identify from the previous screenshot is that the fixed asset group financial dimension values, (e.g. “PKW”, “MA”, etc.), have been mapped to the different rows of the fixed asset statement report. In addition, two main accounts are included in the report and mapped to the rows in the financial asset section.
This flexible way of setting up and mapping any combination consisting of main accounts and financial dimension values allows including also those fixed assets that are not tracked in the fixed asset module but in General Ledger. As a result, the fifth issue of the standard fixed asset statement report mentioned previously can be considered to be fixed.
After setting up the row definition, the column definition of the fixed asset statement report is setup. This setup is realized by adding the different fixed asset transaction types – such as acquisitions, depreciation, etc. – to separate report columns and by filtering on the main accounts that are setup for the different transaction types. Please see the next screenshot for an example.
Note: In order to separate fixed asset reclassifications and fixed asset splits from ordinary fixed asset acquisition transactions, an additional attribute filter has been setup in the column definition of the report.
My next step consisted of merging the row and column definitions into a new report and processing this report. The next screenshot shows the outcome of this process.
Please note that the different transaction types and values posted can easily be followed up from the fixed assed statement report generated.
@ Issue 4: „No drill-down functionality”
By clicking on the data reported in the fixed asset statement report, users can execute a drill-down into the data that make up the total amounts reported. If this drill-down does not provide sufficient details, the respective Dynamics AX voucher can be opened as illustrated in the next screenshots.
As a result also the fourth issue of the standard Dynamics AX fixed asset statement report can be considered to be fixed.
@ Issue 1: „Automatic addition of newly created fixed assets”
As the fixed asset statement report is mapped to the fixed asset group financial dimensions, newly setup fixed assets are automatically added to the report. To illustrate this, an acquisition transaction for a total of 40000 EUR was recorded for a newly setup fixed asset (“FA0041”). After re-generating the fixed asset statement report, the following result could be identified:
A drill-down into the total acquisition value shows that the newly created fixed asset is automatically added to the fixed asset statement report. For that reason, also the first issue of the standard fixed asset statement report can be considered to be fixed.
@ Issue 2: „Fix column setup”
The second issue of the standard fixed asset statement report – the fix link between fixed asset transaction types and report columns – can be overcome by setting up alternative Management Reporter column definitions, which can be selected when running the fixed asset statement report. The next screenshot shows you an example how different fixed asset transaction types can be combined into a single column by filtering on the ledger accounts and vouchers setup.
Sample report result:
@ Issue 3: „Only one single row structure available”
The last remaining issue of the fixed asset statement report – the limitation to one single row structure – can be fixed simply by setting up a new (alternative) row definition that is selected once the fixed asset statement report is generated. The next screenshot provides an example of an alternative Management Reporter row definition.
Please note that also the column definition needs to be modified in line with the newly setup row structure.
If you combine both elements in a new report, basically any fixed asset statement format required can be realized.
After investigating solutions for the problems of the standard Dynamics AX fixed asset statement report, I will now put a focus on some “special” fixed asset transactions and how they are included in the fixed asset statement report in the Management Reporter.
Special topic 1: Scrap of fixed assets
A fixed asset (“FA0037”) with a net book value of 57000 EUR is scrapped on
01. September via the following fixed asset posting journal.
Result: The scrap of the fixed asset can be identified in a separate column of the fixed asset report due to the use of separate ledger accounts for scrap transactions. For details, please have a look at the fixed asset posting profile setup in the appendix.
Note: To follow up the special transactions illustrated in this subsection, the fixed assets affected are shown at the bottom of the fixed asset statement report in separate rows.
Special topic 2: Sale of fixed assets
On 01 October a fixed asset („FA0038“) with a net book value of 57000 EUR is sold for 5000 EUR. The asset is sold via the free text invoice as illustrated in the next screenshot. Taxes are disregarded for simplicity.
Result: The sale of the fixed asset can be identified in a separate column of the fixed asset report because separate ledger accounts have been setup for fixed asset sales transactions. For details, please have a look at the fixed asset posting profile setup in the appendix.
Special topic 3: Reclassification of fixed assets:
The next special transaction is the reclassification of a fixed asset (“FA0039”) into a new fixed asset (“FA0042”) that Dynamics AX automatically creates.
Result: The next screenshot shows that the reclassification of the fixed asset is recorded in the reclassification column of the fixed asset statement report. In addition, all transactions that have been recorded for the old fixed asset “FA0039” are transferred to the new fixed asset “FA0042”.
Special topic 4: Split of fixed assets:
The last transaction illustrated here is the split of a fixed asset. In the example used, 10% of the fixed asset value of fixed asset “FA0040” are transferred to another fixed asset “FA0043”.
Result: Due to the setup of a voucher filter in the row definition, the fixed asset split transaction can be included in a separate column of the fixed asset statement report as illustrated below.
After illustrating the different issues of the standard fixed asset statement report and after introducing an approach to overcome those issues, it can be summarized that all weaknesses of the standard fixed asset statement report can be fixed by implementing some minor system adjustments and by setting up the fixed asset statement report in Management Reporter.
Please note that the automatic creation of fixed assets via purchase orders and processes related to organization wide fixed asset identifiers have not been investigated in this post. If your company uses those processes, you need to investigate whether the one or the other additional system modification might be required.
Irrespective of this qualification, I believe that the drill-down functionalities, the flexible setup options and the possibility of setting up a comprehensive fixed asset statement report that does also include prepayments, financial assets and alike, are major advantages of the approach presented here.
In addition, the possibility of setting up different row and column structures to create fixed asset statements for different reporting purposes (local GAAP reporting, tax reporting, IFRS reporting, etc.) is an advantage that can hardly be achieved with standard Dynamics AX SSRS reports.