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.
The high level steps to report on data from non-Dynamics ERPs are:
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.