Adding PCR Substitution Variables, Derived Variables, and Expressions to Reports

You can add PCR substitution variables and derived variables to reports, just as you can with substitution variables created in Dynamic Planning. You can also create expressions with the PCR variables to simplify report maintenance.

When running a report that includes Substitution Variables, the user can change the value of the substitution variables at run-time by clicking the Substitution Variables button on the menu bar. Note that users cannot changed derived substitution variables, only the standard variables.

In Practice: Substitution Variables Used in a Report

In a report built from a model with direct access to PCR, go to Report > Design.

1. Select all cells and click Design Manager.

2. Scroll down to Scenario and click the member selection icon.
   

3. Select a Scenario variable, such as @CURBDGT@ or ACTUAL.

4. Scroll to the Time dimension and click the member selection icon.

5. Select a Time variable for the year you want to view, such as @CURYR@.
   

6. Click Update.

7. In the spreadsheet, select the column containing Q1 of the year.

8. Click Design Manager and scroll to the Time dimension. Select the same variable for the year as in step 5.

9. Before clicking Update, on the Time dimension, click the member selection icon again.

10. Select Expression.
    

11. In the Expression box, click Add Function, then First Child.

12. Click Update.
    

13. In Design Manager, you see the expression as the new value.

14. Click Update.

    

15. Repeat steps 7-14 for each column in the spreadsheet, modifying the expression as needed for the month and quarter to be represented. For example, @CURYR@.FirstChild.FirstChild for the first month of the year. For more examples of expressions, see Example Substitution Variable Expressions.

16. When done, save the report design.

Going forward, only the @CURYR@ variable needs to be set for the report to be updated.

In Practice: Derived Variables Used in a Report

In this example a report is designed using the @CURYR@ and @CURQTR@ substitution variables and several derived substitution variables. Each substitution variable has a default value. For example, @CURQTR@ is Q4 2014. Derived substitution variable @CURQTR+1@ is Q1 2015.

When the user runs the report, they can click the Substitution Variables button to change the current year and current quarter.

Users are not given the option to change derived substitution variables, such as @CURQTR+1@.

When done, click Refresh to see the changes. By changing @CURQTR@ to Q1 2015, the derived substitution variables automatically change; for example, @CURQTR+2@ is now Q3 2015.