Use Substitution Variables
Use substitution variables in Essbase calculation scripts to store values that might change. Use runtime substitution variables when you need different users to specify different values for the same script.
For example, if a variety of your calculation scripts, formulas, filters, report scripts, and MDX scripts all need to refer to the current month, you would not want to search and replace the month approximately every 30 days throughout your library of cube artifacts. Instead, you can define a substitution variable named CurrMonth, and change its assigned value each month to the appropriate month. All of the cube artifacts that reference the variable will then reference the appropriate month.
Here is an example of a simple substitution variable to represent the current month:
Variable name: CurrMonth
Value: Jan
Substitution variable values apply to all users who run a calculation script containing the variable. For example, if CurrMonth has the value Jan, then all scripts containing &CurrMonth will execute for Jan. The scope of a substitution variable can be:
- global (for all applications and cubes on the server)
- application (for all cubes in the application)
- cube (for a single cube)
To define a substitution variable for a specific cube,
- On the Applications page, open the application and then open the database (cube).
- Click Customization, click Variables, and then click Create.
- Enter the variable name and value, and click
Save
.
- In the Essbase web interface, on the Applications page, expand the application to show the cube you want to modify.
- From the Actions menu to the right of the cube, launch the inspector.
- Select the Variables tab, and click
Add
.
- Enter the variable name and value, click Save, and click Close.
To define a substitution variable for a specific application,
- On the Applications page, open the application.
- Click Customization, click Variables, and then click Create.
- Enter the variable name and value, and click
Save
.
- On the Applications page, from the Actions menu to the right of the application, launch the inspector.
- Select the Variables tab, and click
Add
.
- Enter the variable name and value, click Save, and click Close.
To define a substitution variable globally,
- On the Applications page, click Console.
- Click the Variables tab, and click Create.
- Enter the variable name and value, and click
Save
.
- In Essbase, click Console.
- Click the Variables tab, and click Add.
- Enter the variable name and value, and click
Save
.
Once your substitution variable is defined, you can use it in calculation scripts, formulas, filters, MDX scripts, load rules, and reports. To reference the variable, prefix it with the &
symbol.
Here is an example of a calculation script that references a substitution variable:
FIX(&CurrMonth)
CALC DIM (Measures, Product);
ENDFIX
Here is an example of a formula that references a substitution variable:
@ISMBR(&CurrMonth)
Runtime substitution variables enable you to declare variables and their values in the context of a runtime action, such as a calculation script, MaxL script, or MDX query. Runtime substitution variables can be assigned to have numeric values or refer to member names. A default value can be assigned in case a user does not change an input value. Also, for calculation scripts, the variable value can be populated at runtime from the members of a dimension presented on a Smart View grid. For calculation scripts with variable values that populate at runtime, you must launch the calculation script from Smart View, as the variable has no definition outside the context of the grid.
Runtime substitution variables may be defined in the calculation script using key-value pairs:
SET RUNTIMESUBVARS
{
myMarket = "New York";
salesNum = 100;
pointD = "Actual"->"Final";
}
Or, to define runtime substitution variables with values that change dynamically depending on the POV, assign the definition to POV, and use XML syntax to enable Smart View contextual prompts.
For more information, see
- Using Substitution Variables
- Using Runtime Substitution Variables in Calculation Scripts Run in Essbase and Using Runtime Substitution Variables in Calculation Scripts Run in Smart View
- The SET RUNTIMESUBVARS calculation command
- The gallery template Sample_Basic_RTSV, which you can find in
Files
> Gallery > Technical > Calc
.