How to Set Up Substitution Variables in Dynamic Reports?

Setting Up Substitution Variables in Dynamic Reports?

1. Navigate to Maintenance > Reports > Cube Settings and click the Substitution Variables tab.

2. Review the list of substitution variables that have been defined /delivered.

3. Click Add to set up a new substitution variable on the Add Substitution Variable page.

4. Complete the fields on the Add Substitution Variable page. Note that substitution variables are defined for a reporting area and dimension.

5. Select the newly added substitution variable on the list page and click Derived Variables to view the corresponding derived variables.

6. Add new derived variables by clicking Add on the Derived Variables list page.

7. The Add Derived Variable page is opened. Complete the fields on this page.

Using Substitution Variables in a Report

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.

Substitution variables are available for selection on the Members pane and can be applied to the page, row, or column.

Example: A dimension is added to the page axis and the @CURBDGT@ substitution variable is selected and added to the Mapped Members pane, which means that the substitution variable is applied as a page axis member

This feature is available in Dynamic Reports only and is not available when a Dynamic Report is used in the Financial Package Publisher.

When substitution variables are selected in the member selector for a row or column axis, the values are displayed as row or column headers in the report output. When you run the Dynamic Report, you are prompted for variable values.

Substitution variables can be added to the Pick List. All report functionality (drill through, formatting, and grid actions) is available for substitution variables values when applicable.

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

Improve Report Performance

Instead of Reporting Scenario Functionality

Improve Efficiency Building Report Sets

Overcome Rule Limitations

Efficient Way to Replace Formula Exceptions

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. 

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:

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.

 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.

 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.

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.

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:

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.