Creating Consolidated Financial Statements using Management Reporter – Consolidating with data in a non-Dynamics ERP (Part 5 of 7)
When data resides in companies that aren’t using a Dynamics ERP, Management Reporter can still consolidate the data. The method is referred to in the Management Reporter Help file as “Linking reports to Excel”. This blog post (5th in a series) focuses on how to bring in data from a non-Dynamics General Ledger. There are also multiple examples of linking to reports using Excel in the Report Design in Management Reporter 2.0 training materials.
- Part 1 contains an overview of consolidating data across one or multiple Dynamics ERPs with the same chart of accounts and same fiscal periods.
- Part 2 describes consolidating across one or multiple Dynamics ERPs with different chart of accounts.
- Part 3 defines the process for consolidation across one or multiple Dynamics ERPs with different fiscal periods.
- Part 4 is focused on reporting with multiple Dynamics AX 2009 instances (AOS).
- Part 5 will show you how to report on consolidation data that resides in non-Dynamics ERPs.
- Part 6 will discuss how to produce a consolidated report in Management Reporter that includes a column for elimination entries.
- Part 7 will focus on scenarios where a company does not own 100% of a subsidiary.
The high level steps to report on data from non-Dynamics ERPs are:
- Create an Excel file(s) containing the data from the non-Dynamics ERP.
- Add a new Row Link in the row definition to use to Link to External Worksheet.
- Add cell references to the rows in the row definition.
- Add a column type of WKS (Amounts from external worksheet) to the column definition.
- For each node in the reporting tree that will need to pull data from an Excel worksheet, indicate the Row Definition, External Link and External File location.
- Select “Use row definition from reporting tree” in the Report Definition.
In the following example, two companies use non-Dynamics ERPs, Contoso Asia and Contoso Europe. The row definition contains a Link Type of External Worksheet. In that link, the cell references from Excel for the rows of data are defined. Notice the path to the Excel file is not yet defined, this comes later in the reporting tree definition.
Users can also utilize the CPO (Column Period Offset) and RPO (Row Period Offset) row options to make the report more dynamic. In the following example, the Sales data starts in cell B5 in the Excel file, so the cell reference is A5/CPO. This means when the report is generated for period 1, Management Reporter will report the data one column to the right of cell A5, or B5.
The column definition uses a WKS (Amounts from external worksheet) to bring in the data from the Excel file.
The reporting tree plays an important role in using this method to consolidate data. Each node in the reporting tree defines a company. Contoso Asia is row three, and Contoso Europe is row 4. Because they are not companies defined in a Dynamics ERP, the Company code of @ANY is used.
The row definition that contains the Link to External Worksheet is defined in column E, Row Definitions. Once this is selected, the Worksheet Link column (G) will contain a drop down with the links defined. The last step in the reporting tree definition is to define the path to the excel file in the Workbook or Report Path column (H).
If there are multiple tabs (worksheets) in the workbook, enter the tab name in the Worksheet Name column (I). Refer to Contoso Asia for an example. If there are not multiple tabs, then just define the path to the workbook. Refer to Contoso Europe as an example.
Once the tree is created, users can optionally make the report a side by side report by entering a Reporting Unit restriction in the column definition.
Lastly, the option in the Report Definition to use the row definition from the reporting tree is selected.
Using this method is easy and a great way to take advantage of all the Management Reporter features, while still consolidating data from multiple systems.