Dynamic Planning Model Substitution Variables

4 Minutes to read

Print
Dark
Light
PDF

Article summary

Did you find this summary helpful?

Thank you for your feedback

Overview

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-2020

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

You 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 you can define per model. Substitution variables are model-specific. Before using substitution variable in reports, you must define them.

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

Tip

This feature can be used as a report as it provides a list of substitution variables associated with a model and dimension or all models for any dimension or all models and all dimensions. Up to 10000 rows are displayed. If your selection results into more than 10000 rows, an application message will appear indicating that you can filter on a specific model and dimension.

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

Model - Select the model for which you want to define variables, or, select All Models.

Dimension - The dimension to which you 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 you want to substitute.

You 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, you can update the values.

Adding a New Substitution Variable

In Practice

Select the Model task and the Model Administration > Substitution Variables subtask.
Select the model for which you want to define variables.
Select the dimension to which you want to apply the variable. Values are displayed based on the dimension members.
In the Substitution Variable cell, enter a name for the substitution variable.
In the Value cell, select the list box and select a dimension member or enter a value directly in the cell.
Once all substitution variables are defined, click Save.

Deleting an Existing Substitution Variable

In Practice

Select the Model task and the Model Administration > Substitution Variables subtask.
Select a model and dimension.
Select and delete the substitution variable.

Note:

All Models & All Dimensions options are only updating values.

Using Substitution Variables in a Calculation

You can use the substitution variable as value in a Calculation. For example, in the Calculation below the current scenario variable is used. The value can be a substitution variable.

In the example below, substitution variables are created for the Time and Scenario dimensions of the FinanceMaster model. The intention is to have a 12-month rolling forecast.

In Practice

Select the Model task and the Model Administration > Substitution Variables subtask.
Select the model for which you want to define variables.
Select the dimension to which you want to apply the variable. Values are displayed based on the dimension members.
In the Substitution Variable cell, enter a name for the substitution variable.
In the Value cell, select the list box and select a dimension member or enter a value directly in the cell.
Once all substitution variables are defined, click Save.
Select the Analyze task and the Data subtask.
Select the FinanceMaster model, which is the model the substitution variables were defined for.
Manipulate the data as needed using actions.
Select the Report subtask.
Notice that member values are displayed when labels are selected. In the image below, there are currently no substitution variables used. Select a block of cells or just one cell to select a substitution variable.
Click Design Manager.
The Scenario dimension is already displayed in the Dimension list box, but you can select any dimension. Click the search button ().
Substitution variables as well as dimension members are displayed in a hierarchy as shown below.
In this case, Scen1 is selected for the Actual scenario (cell B4), Scen2 is selected for the Actual scenario (cell C4), Scen3 is selected for the 2013 Forecast Q2 scenario (cell D4), and so on until all 12 periods are substituted.
Once all scenarios are substituted, select Jan-13 (cell B5) and click Design Manager.
Select Per1 and click Update. Select Jan-13 (cell C5) and click Design Manager. Select Per2 and so on until all 12 periods are substituted.
Once all periods and scenario substitution variables are applied, click Save.
Enter a report name.
Click on a dimension member label to view the substitution variable assigned.
Select the Run subtask to run the report with substitution variables.
When defining additional reports, you can substitute the variables defined as long as the report belongs to the same model. Or, use Substitution variables for existing reports. When in Design mode, select the column or row label and click Design Manager.