Automatic matching of a worksheet column to the current period in Management Reporter (PerformancePoint Server)

You can use one of the following methods to link to Excel files that cover multiple reporting periods:

Column Period Offset (/CPO)

If the Excel file that you are linking to has multiple columns that represent different periods (for example, a worksheet that has a detailed, 12-month budget forecast), you can 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 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 of using the /CPO option to link to rows from the external worksheet file.

Row Code

Descriptio n

...

Link to Worksheet

340

Total Units Sold

...

A7/CPO

When you run a report for period 1, Microsoft Office PerformancePoint 2007 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 forth.

Notice that the referenced cell (A7) is one column to the left of the first amount that you want to import into your 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) Option

If the Excel file that you are linking to has multiple rows that represent different periods, you can 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 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 rows from the external worksheet file.

Row Code

Description

...

Link to Worksheet

340

Total Units Sold

...

B2/RPO

When you run a report for period 1, Management Reporter uses the values in row 3 of the worksheet (January). For period 2, Management Reporter uses the values in row 4 of the worksheet (February), and so forth.

Notice that the referenced cell (B2) is one row above the first amount that you want to import into your 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.

Using Multiple WKS Columns

If you specify multiple WKS columns in the column definition and use the /CPO or /RPO option in the row definition, Management Reportermatches a column in the Excel file 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 adding a value to the period cell, see Financial Data Column Detail.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×