Support for PCR Substitution Variables and Derived Variables

If you are using substitution variables and derived variables in PCR, you can see them in models with Direct Access to PCR. 

To see the Substitution Variables in PCR, go to Maintenance > Reports > Cube Settings, and click the Substitution Variables tab.

Adding Substitution Variables and Derived Variables to Existing Direct Access Models

Creating a Direct Access Model with PCR Substitution Variables and Derived Variables

Viewing PCR Substitution Variables and Derived Variables in SpotlightXL

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

Using PCR Substitution Variables and Derived Variables in Reports at Runtime

How to Add Substitution Variables and Derived Variables to Existing Direct Access Models?

A user with Contributor or Power privilege can do the following steps. Find the model you previously created that has direct access to PCR. For example, we have a model named 2020 Products. There are two quick steps to add the PCR Substitution Variables and Derived Variables to the model: Clear Model, then Generate Model. You can do those steps manually or another option is to run the default calculation that was created automatically when you created the model.

1. In SpotlightXL , go to Model > Setup and select the model name from the Model drop-down. The model is generated and locked.
   
   

2. Click Clear Model.

3. Click Generate Model. The list of substitution variables and derived variables now appears on the Model > Model Administration > Substitution Variables screen, and they are available for your use.
   

Another way to achieve the same goal is to run the default calculation which was created with your direct access model. The calculation is named <model-name> Refresh and it contains the commands to refresh the model. By refreshing the model, the variables are automatically added to the model. To run the calculation, go to Model > Calculation, locate the calculation, and click Run.

Creating a Direct Access Model with PCR Substitution Variables and Derived Variables

A user with Contributor or Power privilege can create DAP models. When creating and generating a new model with direct access to PCR, the Substitution Variables and Derived Variables are automatically created with the model. For information on creating a direct access models, click here.

Viewing PCR Substitution Variables and Derived Variables in SpotlightXL

In SpotlightXL, to see the PCR Substitution Variables and Derived Variables, go to Model > Model Administration > Substitution Variables. Select the model from the Model drop-down. Select All Dimensions from the Dimension drop-down.

The Substitution Variables and Derived Variables and their default values cannot be changed from Dynamic Planning. To change them, make the corrections in PCR, then refresh the model.

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.

How to Use Substitution Variables in a Report?

1. In SpotlightXL, to open a report created with direct access models, go to Report > Design.

2. Select all cells and click Design Manager.

3. Select required dimension and click the member selection icon.
   
   

4. Select a required variable.
   
   
   

5. Before clicking Update, click the member selection icon again.

6. Select Expression.
   
   
   

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

8. Click Update.
   
   

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

10. Click Update.

    

11. When done, save the report design.  For more examples of expressions, see Example Substitution Variable Expressions.

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

In Practice: Derived Variables Used in a Report

Each substitution variable has a default value. 

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.

When done, click Refresh to see the changes. 

Using PCR Substitution Variables and Derived Variables in Reports at Runtime

Once you have added Substitution Variables to the report design, you are ready to use them while running the report.

In the report designed in the previous section, go to Report, Run. The current value of @CURYR@ is the basis for the Time columns. Click Substitution Variables on the ribbon to change the value.