Setting Up and Using Substitution Variables
- 3 Minutes to read
- Print
- DarkLight
- PDF
Setting Up and Using Substitution Variables
- 3 Minutes to read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
How to Set Up Substitution Variables
- Navigate to Maintenance, Reports, Cube Settings, and click the Substitution Variables tab.
- Review the list of substitution variables that have been defined /delivered.
- Click Add to set up a new substitution variable on the Add Substitution Variable page.
- Complete the fields on the Add Substitution Variable page. Note that substitution variables are defined for a reporting area and dimension.
- Select the newly added substitution variable on the list page and click Derived Variables to view the corresponding derived variables.
- Add new derived variables by clicking Add on the Derived Variables list page.
- The Add Derived Variable page is opened. Complete the fields on this page.
How to Use Substitution Variables in Dynamic Reports
Substitution variables are used in the creation of Dynamic Reports. A substitution variable replaces a complex multi-dimensional expression (Rule) with a specified value. Substitution variables must be added to Report Sets as lines to be reflected in reports. Planful provides over 40 system-defined substitution variables.
The purpose of using a substitution variable is to:
Improve report performance
Improve Report Set maintainability
Dynamically display report column/row headers
Where to Use Substitution Variables
Efficiently Replace Formula Exceptions
In Combination with Static Text
Instead of Reporting Scenario Functionality
Improve Efficiency Building Report Sets
Efficient Way to Replace Substitution Variable
To display the current period in the column header of a report, replace the column set’s line name with a substitution variable, such as @CURMTH@. When the report is run, @CURMTH@ will be replaced with the actual month name Mar-18. In the image below, the current year minus 1 and the current budget sub vars are used.
Combine Substitution Variables with Static Text
The year is 2019
YTD~ @CURMTH@ will display YTD~ Jan-19
@CURYR@Actual will display 2019 Actual
Improve Report Performance by Avoiding the User of Calculated or Measured Dimensions when Building Column Sets
The following Report Set is commonly used:
YTD | MTD | ||||||
|
|
Setting up this Report Set requires the usage of complex Rules that typically require a column set on a Calculated or Measured dimension and utilizes the Reporting scenario for the Budget column. The following table displays commonly used formulas to deliver report columns.
| Line Name | Formula Without Substitution Variables |
|---|---|
YTD~Actual | ([Measures].[YTD],[Scenario].&[1],[Calculated].&[1]) |
YTD~Budget | ([Measures].[YTD],StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[Calculated].&[1]) |
YTD~Variance | Custom Formula |
MTD~Actual | ([Measures].[MTD],[Scenario].&[1] ,[Calculated].&[1]) |
MTD~Budget | ([Measures].[MTD],StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[Calculated].&[1]) |
MTD~Variance | Custom Formula |
While the rules above deliver the expected result, they are complex. This complexity adversely impacts report performance. The same report can be set up more efficiently using Substitution Variables. The table below displays how Substitution Variables compare to the formulas in the table above.
| Line Name | Formula with Substitution Variables |
|---|---|
YTD~Actual | ([Scenario].&[1] ,[Measures].[YTD]) |
YTD~Budget | (@CURBGT@,[Measures].[YTD]) |
YTD~Variance | Custom Formula |
MTD~Actual | ([Scenario].&[1],[Measures].[MTD]) |
MTD~Budget | (@CURBGT@,[Measures].[MTD]) |
MTD~Variance | Custom Formula |
Performance for the Report Set depicted in the table above can be improved by moving the repeated member on the Page axis. The Report Set above uses [Measures].[MTD] and [Measures].[YTD] frequently. Move one of these members to the page level. For example, use the member [Measures].[YTD] on the Page axis rather than repeating [Measures].[YTD] on the first and second lines as shown in the table below.
| Line Name | Formula with Substitution Variables |
|---|---|
YTD~Actual | [Scenario].&[1] |
YTD~Budget | @CURBGT@ |
YTD~Variance | Custom Formula |
MTD~Actual | ([Scenario].&[1],[Measures].[MTD]) |
MTD~Budget | (@CURBGT@,[Measures].[MTD]) |
MTD~Variance | Custom Formula |
Note: The Variance Formula cannot vary based on the type of account. It will change based on Variance configuration in the Report Set Formula.
Instead of Reporting Scenario Functionality
When using reporting scenario functionality to retrieve the budget and forecast for a selected year, you might use the following rule.
StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]"))While the rule above delivers the expected result, it slows report performance. A performance improvement is to use Substitution Variables as described below.
| Line Name | Previous Approach to Formula | Formula with Substitution Variables |
|---|---|---|
Current Budget | StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")) | @CURBGT@ |
Current Forecast | StrToMember ("Scenario.&["+Time.Currentmember.properties("forecast_Id")+"]")) | @CURFCST@ |
Current Month Budget | (Time.Currentmember,StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[CalculatedDim].&[1]) | (@CURMTH,@CURBGT) |
Prior Year YTD | (Parallelperiod(Year,1,Time.currentmember),[Measures].[YTD]) | (@PREVYEAR@,[Measures].[YTD]) |
Improve Efficiency in Building Report Sets
Substitution Variables aide in the setup and maintenance of time-based Report Sets, such as Trailing 12 months, along with Budget and Forecast columns. The following example displays a Trailing 12-month Report Set:
| Line Name | Previous Approach to Rule | Rule with Substitution Variables |
|---|---|---|
Current Month | (Time.Currentmember,[CalculatedDim].&[1]) | @CURMTH@ |
Current Month - 1 | (Time.Currentmember.Lag(1),[CalculatedDim].&[1]) | @CURMTH-1@ |
Current Month - 2 | (Time.Currentmember.Lag(2),[CalculatedDim].&[1]) | @CURMTH-2@ |
Current Month - 3 | (Time.Currentmember.Lag(3),[CalculatedDim].&[1]) | @CURMTH-3@ |
Current Month - 4 | (Time.Currentmember.Lag(4),[CalculatedDim].&[1]) | @CURMTH-4@ |
Current Month - 5 | (Time.Currentmember.Lag(5),[CalculatedDim].&[1]) | @CURMTH-5@ |
Current Month - 6 | (Time.Currentmember.Lag(6),[CalculatedDim].&[1]) | @CURMTH-6@ |
Current Month - 7 | (Time.Currentmember.Lag(7),[CalculatedDim].&[1]) | @CURMTH-7@ |
Current Month - 8 | (Time.Currentmember.Lag(8),[CalculatedDim].&[1]) | @CURMTH-8@ |
Current Month - 9 | (Time.Currentmember.Lag(9),[CalculatedDim].&[1]) | @CURMTH-9@ |
Current Month - 10 | (Time.Currentmember.Lag(10),[CalculatedDim].&[1]) | @CURMTH-10@ |
Current Month - 11 | (Time.Currentmember.Lag(11),[CalculatedDim].&[1]) | @CURMTH-11@ |
Current Month - 12 | (Time.Currentmember.Lag(12),[CalculatedDim].&[1]) | @CURMTH-12@ |
Current Budget | (Ancestor(Time.Currentmember,1,Year), StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[CalculatedDim].&[1]) | (@CURYR@,@CURBGT) |
Current Forecast | (Ancestor(Time.Currentmember,1,Year), StrToMember ("Scenario.&["+Time.Currentmember.properties("forecast_Id")+"]")),[CalculatedDim].&[1]) | (@CURYR@,@CURFCST) |
Overcome Rule Limitations
You cannot use the same dimension on two axes simultaneously. For example, a report that requires a Report Set on the Time dimension, will not allow selection of Time dimension members at the Page level. To set up a trailing 12-month report, define the column set on either the Calculated or Measure dimension. Time should be on the Page axis. This setup does not use substitution variables so performance will not be optimal. With Substitution Variables, the report column set can be set up on the Time dimension directly and the required selections can be made using Substitution Variables, which will result in improved performance.
Was this article helpful?