Support for Structured Planning Substitution Variables and Derived Variables

Users using substitution and derived variables in Structured Planning can view them in models with Direct Access to Structured Planning. To view Substitution Variables in Structured Planning, navigate to:

Maintenance > Report Administration > Cube Settings and then click the Substitution Variables tab.

Adding Substitution Variables and Derived Variables to Workforce Models

To add Structured Planning Substitution Variables and Derived Variables to an existing workforce model, follow these steps:

In Practice: SpotlightXL

1. In SpotlightXL, go to Model > Setup and select the model name from the drop-down menu.  
    

2. Click Clear Model, then Generate Model.
    

Users can view the list of substitution and derived variables by going to Model > Model Administration > Substitution Variables.

Alternatively, run the Refresh calculation associated with the workforce model to refresh the model and automatically include the variables.

To run the calculation:

1. Go to Model > Calculation.

2. Locate the calculation and click Run.

    

Note:

Changes to substitution variables in Structured Planning are not automatically reflected in Dynamic Planning.

Adding Structured Planning Substitution Variables, Derived Variables, and Expressions to Excel Reports

Structured Planning substitution and derived variables can be added to reports similar to Dynamic Planning variables and used in expressions for easier maintenance.

During report execution, users can modify standard substitution variable values by clicking the Substitution Variables button on the menu bar. Derived variables, however, cannot be modified.

In Practice: Using Substitution Variables in a Report

1. In SpotlightXL, go to Report > Design.

2. Select all cells and click Design Manager.
    

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

4. Select a variable.
    

5. Click Update.
    

6. Click the member selection icon again and select Expression.
    

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

8. In the Design Manager, the user will see the expression as the new value. Click Update.
    

9. Click Save.
    

The user will only need to set the @CURYR@ variable to update the report moving forward.

In Practice: Using Derived Variables in a Report

In this example, a report uses substitution variables and several derived substitution variables, each with a default value.

1. When running the report, users can click the Substitution Variables button to change the current year and quarter.
    

2. After making the necessary changes, click Refresh.

Note:

Users cannot change derived substitution variables.

Using Structured Planning Substitution Variables and Derived Variables in Reports at Runtime

Once Substitution Variables are added to the report design, they can be used during report execution.

Note:

During report execution, standard substitution variable values can be changed by clicking the Substitution Variables button on the menu bar. Derived substitution variables cannot be modified.

1. Go to Report > Run.

2. Click Substitution Variables on the ribbon to change the value.

3. Select the member selection icon and choose a different year.
    

4. Click Select, then Refresh.
    
   The changes will be applied to the report.