Substitution Variables

Substitution variables are used to replace dimension member values in a report. They can be used anywhere on a report; a cell, a column, or a range of cells. A substitution variable contains a variable name and a value. For example:

• Substitution Variable Name = AR1

• Substitution Variable Value: Jan-2024

The substitution variable name is replaced by its value when the report is run, thereby making reporting flexible. For example, if user created a report column that uses the AR1 substitution variable when the report is run the name (AR1) is substituted by its value (Jan-2024).

User can use substitution variable functionality to make mass updates to a report design. For example, highlight a range of cells and change the Company for which that range pertains to. Or copy and paste one period and add another period by highlighting the range of cells and updating the period, which eliminates the need to Capture Data.

Defining Substitution Variables

Define substitution variables for a model. There is no limit to the number of substitution variables user can define per model. Substitution variables are model-specific. Before using substitution variable in reports, user must define them.

To define substitution variables select the Model task and the Model Administration > Substitution Variables subtask.

The spreadsheet fields on the Model Variables page are described below.

Model - Select the model to define variables, or, select All Models.

Dimension - The dimension to which user want to apply the variable, or select All Dimensions.

Substitution Variable - Enter a variable name. When variables are added to a report, they are enclosed with the "@" symbol (for example, @CurrPeriod@).

Value - Select the value to substitute.

User can select ‘All Models ’ and ‘ All Dimensions ’ to update the value for all substitution variables. Or, select ‘All Models’ and a particular dimension (for example, Scenario) so that the system will display all substitution variables across all models defined against the Scenario dimension. At that point, user can update the values.

How to Add a New Substitution Variable?

1. In SpotlightXL, select the Model task and the Model Administration > Substitution Variables subtask.

2. Select the Model for which user want to define variables.

3. Select the dimension to which user want to apply the variable. Value is displayed based on the dimension members.

4. In the Substitution Variable cell, enter a name for the substitution variable.

5. In the Value cell, select the list box and select a dimension member or enter a value directly in the cell.

6. Once all substitution variables are defined, click Save.
   

How to Delete an Existing Substitution Variable?

1. Select the Model task and the Model Administration > Substitution Variables subtask.

2. Select a Model and Dimension.

3. Delete the required row and and click Save.
   

Using Substitution Variables in a Calculation

User can use the substitution variable as value in a Calculation. For example, in the Calculation below the variable is used for Time dimension.

In the example below, substitution variables are created for the Time dimensions of the Insurance Revenue model. 

How to Use Substitution Variable in a Calculation?

1. Select the Model task and the Model Administration > Substitution Variables subtask.

2. Select the Model for which user want to define variables.

3. Select the Dimension where you want to apply the substitution variable, which applies only if there are already existing substitution variables defined for that dimension.

4. In the Substitution Variable cell, enter a name for the substitution variable.

5. In the Value cell, select the list box and select a dimension member or enter a value directly in the cell.

6. Once all substitution variables are defined, click Save.
   
   

   Note:

   The model and dimension fields will be automatically filled based on the selected properties when you save.

7. Select the desired report where the substitution variables are intended to be used.

8. In the image below, no substitution variables are currently applied. Select the specific cell where the substitution variable needs to be applied.

   
   

9. Click Design Manager.

   Note:

   Make sure to select both the header and all data cells to ensure that all relevant intersections are updated to use the substitution variable.

10. The Time dimension is already displayed in the Dimension list box, but user can select any dimension. Click the Select Member icon.

11. Substitution variables as well as dimension members are displayed in a hierarchy as shown below.

    
    

12. Select the required values and click Update.

    
    

13. Once all substitution variables are applied, click Save or Save as.
    
    

14. Now, click Report > Run.

    
    

15. The substitution variables are defined in the report.
    

Best Practices

• Establish a naming convention for substitution variables.

• Keep substitution variable names consistent from one model to the next.

• Do not overly encrypt substitution variable names.