1. Using Oracle Essbase
  2. Calculate Cubes
  3. Use Substitution Variables

Use Substitution Variables

Use substitution variables in 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,

  1. In the Essbase web interface, on the Applications page, expand the application to show the cube you want to modify.

  2. From the Actions menu to the right of the cube, launch the inspector.

  3. Select the Variables tab, and click Add Image of the Add icon..

  4. Enter the variable name and value, click Save, and click Close.

To define a substitution variable for a specific application,

  1. On the Applications page, from the Actions menu to the right of the application, launch the inspector.

  2. Select the Variables tab, and click Add Image of the Add icon..

  3. Enter the variable name and value, click Save, and click Close.

To define a substitution variable globally,

  1. In Essbase, click Console.

  2. Click the Variables tab, and click Add.

  3. 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