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 three 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 ( /CPOor /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.

If you are linking 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 /CPOoption, in the row definition, type the cell address that points to one column to the left of the first period (column). Then, add the /CPOoption at the end of the cell address.

The following table shows an example of using the /CPOoption to link to a column from the external Excel worksheet file.

The following table shows an example of using the /CPOoption to link to a column from the external worksheet file.

When you run a report for period 1, Management Reporter uses the values in column Bof the worksheet (January). For period 2, Management Reporter uses the values in column Cof 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 /CPOor /RPOoption only one time per link cell, regardless of the number of Excel references in that link cell.

If you are linking 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 /RPOoption, 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 /RPOoption at the end of the cell address.

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

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

When you run a report for period 1, the values in row 3of the worksheet (January) are used. For period 2, the values in row 4of 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 /CPOor /RPOoption only one time per link cell, regardless of the number of Excel references in that link cell.

If you specify multiple WKS columns in the column definition and use the /CPOor /RPOoption in the row definition, Management Reporter matches a column in the Excel worksheet with each corresponding WKS column in the column definition. In this case, specify a value in the period cell of each WKS column. You can use a specific period, such as 6, or a relative period, such as B+2.

For more information about how to add a value to the period cell, see Financial Dimensions column .

Example 1

Example 2