Share via

Management Reporter and Microsoft Excel

  • Article

You can use Management Reporter and Microsoft Excel to do the following:

This topic contains the following sections:

  • Link reports to Microsoft Excel

  • Prepare a link column in a row definition

  • Match worksheets with fiscal periods

  • Identify an Excel file in a reporting tree

By linking rows to cells in a Microsoft Excel spreadsheet, you can combine the data from report building blocks and Excel to summarize and display information in additional ways.

A row definition typically contains links to financial accounts and dimensions. These links allow for dynamic reporting. Management Reporter pulls the information from the links every time that you generate a report. This means that the report always uses the most current information available.

You can also create links from a row to a cell in an Excel spreadsheet file that is maintained separately from your Management Reporter reports. For example, you can create a report that compares actual expenses from Microsoft Dynamics ERP to the budget in an Excel spreadsheet.

When used with a reporting tree, linking enables each unit in the reporting tree to use a different Excel workbook or worksheet. This means that reports can be formatted to pull in the same data or specific data for each level of the report.

You can access data in an Excel worksheet by using a combined worksheet link or a separate worksheet link. For more information, see Using a combined worksheet link and Using a separate worksheet link.

A Financial Dimensions + Worksheet link, also known as a combined worksheet link, combines references to cells in an external worksheet file into the same link column that you use for links to accounts or dimensions. When you use this method to access data in an external worksheet file, some rows in a column of the report will contain data from your financial data system, and other rows in that same column will contain data from the external worksheet file. We recommend that you use this method when you import limited amounts of data, such as work hours, head count, or units sold, that you do not have to associate with a specific unit in the reporting tree.

Note

This method does not require a reporting tree, but can only pull data from a single worksheet per link.

A separate worksheet link lets you access multiple rows and columns of data in an external worksheet file, and to associate one or more external worksheet files with one or more reporting units in the reporting tree. You create a special column in the row definition that links to cells in the external worksheet file, and then add the names of the external worksheet files to the reporting tree.

Note

If you link to spreadsheets that have identical worksheet layouts, you reduce the number of link columns that are required in the row definition and the time that is required to maintain your report.

Linking to Management Reporter reports

In Management Reporter, you can use data from an Excel spreadsheet that was generated by Management Reporter and link it to another Management Reporter report. This is useful when you want to use the data from one Management Reporter report in other Management Reporter reports. You can link to individual reports or, if you use a reporting tree, you can link to data in multiple Management Reporter reports.

Within a row definition, one or more link columns contain the links to the dimensions and Microsoft Excel workbooks. Each row definition can have up to 100 link columns. The row definition column heading indicates the link types that are supported in that column.

By default, the row definition includes one link column titled Link to Financial Dimensions, for linking to the financial data. You can add new link columns, and you can modify the link type of existing link columns.

Each link column can link to one of the following types of data.

Data type

Description

Financial Dimensions

Links to only the financial data system.

External Worksheet

Links to an Excel file based on the reporting tree.

Financial Dimension + Worksheet

Links to the financial data system or to an Excel file.

Management Reporter Report

Links to another report in Management Reporter.

XBRL taxonomy

Links to an XBRL taxonomy

  1. In Report Designer, open the row definition to modify.

  2. On the Edit menu, click Row Links to open the Row Links dialog box.

  3. In the Row Links dialog box, click New.

  4. Under Link definition, in the Link type list, select one of the following options:

    • Financial Dimensions – Links to only the financial data system.

    • External Worksheet – Links to an Excel file that is specified in the reporting tree.

    • Financial Dimension + Worksheet – Links to the financial data system. Optionally, you can also link to an Excel file or to another Management Reporter report.

    • Management Reporter Worksheet – Links to another worksheet in Management Reporter that is specified in the reporting tree.

    • XBRL Taxonomy – Links to an XBRL taxonomy. For more information, see Link to XBRL taxonomies.

  5. In the Link name field, type a name for the link column.

  6. If you selected the Financial Dimensions + Worksheet link type in step 4, you can link to an Excel worksheet or to another report in Management Reporter.

    • To link to an Excel worksheet, enter the file path in the External workbook path field, or click Browse to browse to the file.

    • To link to another report in Management Reporter, enter the file path in the Management Reporter report field, or click Browse to browse to the file. To link to a specific tab in the worksheet, enter the tab name in the Worksheet name field.

      Note

      You can link to any report in the Management Reporter report library, even if you do not have access to that report. However, the linked data will not be included in the report that you generate.

  1. In Report Designer, open the row definition to modify.

  2. On the Edit menu, click Row Links.

  3. In the Row Links dialog box, select a link from the table.

  4. Under Link definition, modify any of the available properties, and then click OK.

  1. In Report Designer, open the row definition to modify.

  2. On the Edit menu, click Row Links.

  3. In the Row Links dialog box, select a link from the table.

  4. Click Delete.

Referring to Excel worksheet names

To reference a specific Excel worksheet, type the name in the Worksheet name field of the Row Links dialog box. When you reference a particular Excel worksheet, the name of the worksheet must be at least two characters, for example, [workbook.xlsx]AA.

When you reference a specific cell in an Excel worksheet, use the column letter of the cell and row number references. For example, C10 would be column C, row 10. Management Reporter does not support referencing ranges of cells in an Excel worksheet.

The following examples are valid references for Financial Dimensions + worksheet links:

  • @WKS(B5)

  • @WKS(B=B5)

  • @WKS(B=B5, C=C5, D=D5)

The following examples are valid references for worksheet links:

  • B5

  • B5=B5

  • (B=B5, C=C5, D=D5)

Because the column definition is defined separately from the row definition in Management Reporter, you must determine which report columns are to receive the data from the Excel worksheet. If you specify a single cell, such as B5, the value is only added in a WKS column. If you use specific cell references like B=B5, you can position Excel values in FD, WKS, or CALC columns.

Note

The report columns that accept data from an Excel worksheet are the columns that are identified in the column definition as FD or WKS. If you reference an Excel worksheet cell in a CALC, DESC, or FILL column, the value is ignored.

Match worksheets with fiscal periods

If you have data in an external worksheet or a Microsoft Excel spreadsheet, you can create a link in a report in Management Reporter to add the external data to the report. You can use either a combined link or a separate link to retrieve the external data.

The combined link method is primarily used to add spreadsheet data to a row in the report. A combined link must meet the following requirements:

  • Connect to a single Microsoft Excel spreadsheet.

  • The row definition supplies the spreadsheet file name and location.

  • Spreadsheet cell references are preceded by @WKS.

In the separate link method of adding external data to a report, the following building block definitions contribute to the setup of the link. In addition, the report definition is set up to pull worksheet-related data from the reporting tree.

  • The row definition contains worksheet cell references in one or more external worksheet links.

    • If the worksheet is designed with periodic data, then the related period offset code (/CPO or /RPO) is also required.

    • If multiple WKS type columns are defined in the column definition, then the related column is also entered in the link syntax, for example, C=B4, E=C4.

  • The column definition contains one or more WKS type columns.

  • The reporting tree definition is associated to the external link in the row definition, in addition to the location of the worksheet file.

You can use either a column period offset (/CPO) or a row period offset (/RPO) to link to Microsoft Excel files that cover multiple reporting periods.

Column Period Offset (/CPO)

If you link to an Excel file that has multiple columns that represent different periods, for example, a worksheet that has a detailed, 12-month budget forecast, use the /CPO (column period offset) option to match the accounting periods in the column definition with the appropriate columns in the Excel worksheet. You can use this technique with either the combined worksheet link or the separate worksheet link.

To use the /CPO option, in the row definition, type the cell address that points to one column to the left of the first period (column). Then, add the /CPO option at the end of the cell address.

The following table shows an example in which you use the /CPO option to link to a column from the external Excel worksheet file.

Row code

Description

...

Link to worksheet

340

Total Units Sold

...

A7 /CPO

The following table shows an example in which you use the /CPO option to link to a column from the external worksheet file.

Row code

Description

...

Link to external worksheet

340

Total Units Sold

...

@WKS (B=B2) /CPO

When you run a report for period 1, Management Reporter uses the values in column B of the worksheet (January). For period 2, Management Reporter uses the values in column C of the worksheet (February), and so on.

Note that the referenced cell (A7) is one column to the left of the first amount that you want to import into the report for the first period.

Note

Include the /CPO or /RPO option only one time per link cell, regardless of the number of Excel references in that link cell.

Row Period Offset (/RPO)

If you link to an Excel worksheet that has multiple rows that represent different periods, use the /RPO (row period offset) option to match the accounting periods in the column definition with the appropriate rows in the Excel worksheet. You can use this technique with either the combined worksheet link or the separate worksheet link.

To use the /RPO option, type the cell address that points to one row above the first period (row) in the building block for the given column. Then, add the /RPO option at the end of the cell address.

The following table shows an example of using the /RPO option to link to a row from the external Excel file.

Row Code

Description

...

Link to worksheet

340

Total Units Sold

...

B2 /RPO

The following table shows an example of using the /RPO option to link to a row from the external worksheet file.

Row Code

Description

...

Link to external worksheet

340

Total Units Sold

...

@WKS (B2) /RPO

When you run a report for period 1, the values in row 3 of the worksheet (January) are used. For period 2, the values in row 4 of the worksheet (February) are used, and so on.

Note that the referenced cell (B2) is one row above the first amount that you want to import into the report for the first period.

Note

Include the /CPO or /RPO option only one time per link cell, regardless of the number of Excel references in that link cell.

Example 1: Using the /CPO option

To reference multiple values in a worksheet, you must enter multiple columns in the same WKS statement.

The following table shows an example of using the /CPO option to link to multiple columns from the external worksheet file.

Row Code

Description

...

Link to worksheet

340

Total Units Sold

...

@WKS (B=A2, C=A3) /CPO

Example 2: Retrieving values for calculation

To use the same worksheet value as a base reference, you can use the values in the PERIOD column of a column definition to determine which values to retrieve for the calculation. The PERIOD column can use a hardcoded period, or the BASE+# and BASE-# codes for this calculation.

For example, you can use the WKS statement of @WKS (B=A2, C=A2, D=A2) /CPO with the following column definition.

A

B

C

D

E

DESC

FD

WKS

WKS

WKS

ACTUAL

BASE

BASE

BASE+1

BASE+2

BASE+3

PERIODIC

This calculation uses the same cell in the external worksheet (A2) as a reference point, but the BASE+ period codes in the column definition determine which values to retrieve for the report.

Identify an Excel file in a reporting tree

After you have created an External Worksheet column in the row definition and have specified the Excel worksheet cells to include in the report, you must update the reporting tree with the linked column and the Excel file information. An Excel file can be imported into any unit in the reporting tree.

  1. In Report Designer, open the reporting tree definition to modify.

  2. Double-click the Row Definitions cell, and then select the row definition that contains information about the Excel file.

    Note

    The cells in the Row Definitions column display information based on the selected row information, because the same row definition must be used in all units of the reporting tree.

  3. In the Worksheet Link cell for a reporting unit, select the link name that corresponds to the external worksheet that is selected in the row definition.

  4. In the Workbook or Report Path cell for a reporting unit, type the name of the Excel file or browse to select the Excel file.

  5. To specify a worksheet in an Excel file, type the name of the worksheet in the Worksheet name cell.

  6. Repeat steps 3 through 5 for each reporting unit that should receive data from an Excel file. To prevent incorrect data from appearing in your report, make sure that the correct Excel file names appear in the corresponding unit of the reporting tree.

See Also

Prepare a link column in a row definition

Match worksheets with fiscal periods

Identify an Excel file in a reporting tree

Manage reporting units

(DEU) Management Reporter and XBRL