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

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 or update a substitution variable for a specific cube,

  1. On the Applications page, open the application and then open the database (cube).
  2. Click Customization, then click Variables.
  3. To create a new variable, click Create, enter the variable name and value, and click SaveImage of the Save (check mark) icon in the Variables dialog box..
  4. If you are editing the value of an existing variable, double click the value (or click Edit), type the updated value, and press Enter (or click Save).
  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.
  4. To create a new variable, click AddImage of the Add icon., enter the variable name and value, and click Save.
  5. If you are editing the value of an existing variable, double click the Value field, type the updated value, and press Enter.
  6. Click Close.

To define or update a substitution variable for a specific application,

  1. On the Applications page, open the application.
  2. Click Customization, then click Variables.
  3. To create a new variable, click Create, enter the variable name and value, and click SaveImage of the Save (check mark) icon in the Variables dialog box..
  4. If you are editing the value of an existing variable, double click the value (or click Edit), type the updated value, and press Enter (or click Save).
  1. On the Applications page, from the Actions menu to the right of the application, launch the inspector.
  2. Select the Variables tab.
  3. To create a new variable, click AddImage of the Add icon., enter the variable name and value, and click Save.
  4. If you are editing the value of an existing variable, double click the Value field, type the updated value, and press Enter.
  5. Click Close.

To define or update a substitution variable globally,

  1. On the Applications page, click Console.
  2. Click the Variables tile.
  3. To create a new variable, click Create, enter the variable name and value, and click SaveImage of the Save (check mark) icon in the Variables dialog box..
  4. If you are editing the value of an existing variable, double click the value (or click Edit), type the updated value, and press Enter (or click Save).
  1. In the Essbase web interface, click Console.
  2. Click the Variables tab.
  3. To create a new variable, click Add, enter the variable name and value, and click Save.
  4. If you are editing the value of an existing variable, double click the Value field, type the updated value, and press Enter.

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