Using Substitution, Runtime Substitution, and Environment Variables in Calculation Scripts

Using Substitution Variables in Calculation Scripts

When you include a substitution variable in a calculation script, Essbase replaces the substitution variable with the value you specified for the substitution variable. Substitution variables are useful, for example, when you reference information or lists of members that change frequently.

You can create substitution variables at the server, application, and database levels. To use a substitution variable in a calculation script, the substitution variable must be available to the calculation script. For example, a database-level substitution variable is available only to calculation scripts within the database; a server-level substitution variable is available to any calculation script on the server.

In a calculation script, insert an ampersand (&) before a substitution variable. Essbase treats any string that begins with a leading ampersand as a substitution variable, replacing the variable with its assigned value before parsing the calculation script.

For example, in Sample.Basic, to calculate Qtr1 as the current quarter:

Create a substitution variable for the current quarter (&CurQtr) and assign it the value Qtr1
Create a calculation script that uses the &CurQtr substitution variable. For example:
```
FIX(&CurQtr)
   CALC DIM(Measures, Product);
ENDFIX
```

Also see Using Runtime Substitution Variables in Calculation Scripts Run in Essbase.

Using Runtime Substitution Variables in Calculation Scripts Run in Essbase

Similar to substitution variables, a runtime substitution variable can be included in a calculation script wherever substitution variables are allowed. In the calculation script, an ampersand (&) must precede the name of the runtime substitution variable.

Runtime substitution variables are different from substitution variables in that every runtime substitution variable used in a calculation script must be declared in the SET RUNTIMESUBVARS calculation command, with a name and default value.

Note:

If a default value is not included in the runtime substitution variable declaration in SET RUNTIMESUBVARS, an error occurs when the calculation script is validated. Oracle recommends that you provide a default value to avoid the validation error and, when running the calculation script, provide the expected value. However, if you do not provide a default value, you can still provide a value at runtime using the execute calculation MaxL statement with the with runtimesubvars grammar.

A description of the runtime substitution variable's data type and data input limit is a string in the <RTSV_HINT>rtsv_description</RTSV_HINT> tag. This tag is optional when the calculation script with runtime substitution variables is run in Essbase; see Specifying Data Type and Input Limit for Runtime Substitution Variables in Calculation Scripts Run in Essbase. The <RTSV_HINT> tag, with additional metadata, is required when the calculation script with runtime substitution variables is run in Smart View. See Using Runtime Substitution Variables in Calculation Scripts Run in Smart View.

In this example of SET RUNTIMESUBVARS, three runtime substitution variables are declared: myMarket, salesNum, and PointD. Default values are specified for each runtime substitution variable (for example, the value of myMarket is "New York"). This example applies to a calculation script that is run in Essbase:

SET RUNTIMESUBVARS
{
   myMarket = "New York";
   salesNum = 10;
   pointD = "Actual"->"Final";
};

At runtime, the default values that are specified in the SET RUNTIMESUBVARS command can be overwritten, using one of these methods:

execute calculation MaxL statement with the with runtimesubvars grammar, in which runtime substitution variables are specified as a string of key/value pairs.

Using the SET RUNTIMESUBVARS example above, at runtime you can overwrite the salesNum default value of 10 with 500 by using the following MaxL statement:
```
execute calculation appname.dbname.calcScriptName with runtimesubvars 'salesNum=500';
```
Using this MaxL statement also allows you to provide values for runtime substitution variables that do not have a default value in the SET RUNTIMESUBVARS declaration.
An API call in which runtime substitution variables are specified as a string of key/value pairs: IEssCube.calcFileWithRunTimeSubVars (Java API) or EssCalcWithRuntimeSubVars (C API)
An API call in which runtime substitution variables can be specified in a text file on the client computer or as a string of key/value pairs: IEssCube.calcFileWithRunTimeSubVarFile (Java API) or EssCalcFileWithRuntimeSubVars (C API)

When specifying runtime substitution variables as a string of key/value pairs, the string must be enclosed with single quotation marks, and key/value pairs must be separated by a semicolon, including a semicolon after the last runtime substitution variable in the string and before the terminal single quotation mark. In this example of a runtime substitution variable string, the name and value of four runtime substitution variables are specified (for example, the value of the runtime substitution variable named "a" is 100):

'a=100;b=@CHILDREN("100");c="Actual"->"Final";d="New York";'

When specifying runtime substitution variables in a text file, create the text file with an .rsv extension on the client computer. (Essbase does not support runtime substitution variable files located on the Essbase Server computer.) Each line in the file specifies one runtime substitution variable as a key/value pair and must end with a semicolon. In this example of an .rsv file, the name and value of four runtime substitution variables are specified:

a=100;
b=200;
c=@CHILDREN("100");
d=@TODATE("DD/MM/YY","10/11/12");

When a calculation is executed, runtime substitution variable values are determined in the following order:

Values specified through the execute calculation MaxL statement with the with runtimesubvars grammar, or the APIs (IEssCube.calcFileWithRunTimeSubVars or IEssCube.calcFileWithRunTimeSubVarFile Java APIs; EssCalcWithRuntimeSubVars or EssCalcFileWithRuntimeSubVars C APIs).
Default values specified in the SET RUNTIMESUBVARS calculation command.

Consider these guidelines when using runtime substitution variables:

If you declare a runtime substitution variable in SET RUNTIMESUBVARS but do not use the runtime substitution variable in the calculation script, Essbase ignores the unused runtime substitution variable declaration (no warning or exception is generated).
Runtime substitution variables have a higher precedence than substitution variables. Therefore, if a substitution variable and a runtime substitution variable have the same name (for example, myProduct), the value of the runtime substitution variable overwrites the value of the substitution variable.
If multiple runtime substitution variables have the same name but have different values, only the value of the first instance of the runtime substitution variable is used; all other subsequent values are ignored.

The rules for setting names and values for runtime substitution variables are the same as for substitution variables. See Rules for Setting Substitution Variable Names and Values.

Specifying Data Type and Input Limit for Runtime Substitution Variables in Calculation Scripts Run in Essbase

The information in this topic applies to running a calculation script with runtime substitution variables in Essbase. Also see Using Runtime Substitution Variables in Calculation Scripts Run in Smart View.

In the SET RUNTIMESUBVARS calculation command, the runtime substitution variable declaration can include the <RTSV_HINT>rtsv_description</RTSV_HINT> tag, in which rtsv_description is a string that describes the data type and data input limit (for example, an integer not greater than 100) for the runtime substitution variable. The rtsv_description string is not used in the calculation.

The IEssIterator.getCalcFileRunTimeSubVars or IEssIterator.getCalcRunTimeSubVars Java API methods or EssGetRuntimeSubVars C API retrieves all of the information (name, default value, and description) that is specified in the runtime substitution variable declaration in SET RUNTIMESUBVARS. The rtsv_description string can then be used to prompt a user to input a value at runtime or to validate input data before passing the value to the calculation script.

In this example of SET RUNTIMESUBVARS, each declaration specifies the name, default value, and description of the runtime substitution variable:

SET RUNTIMESUBVARS
{
   myMarket = "New York" <RTSV_HINT>myMarket: Input the value as a member name, such as "New York"</RTSV_HINT>;
   salesNum = 10 <RTSV_HINT>salesNum: Input the value as an integer, such as 100</RTSV_HINT>;
   pointD = "Actual"->"Final" <RTSV_HINT>pointD: Input the value as a member combination, such as "Actual"->"Final"</RTSVVAR_HINT>;
};

Logging Runtime Substitution Variables

To log runtime substitution variables that are used in a calculation script, set the ENABLERTSVLOGGING configuration setting to TRUE. Logging can be implemented at the Essbase Server, application, or database level.

Runtime substitution variable log entries are written to the application log file. Essbase writes one entry to the application log for each string of key/value pairs (or a list of key/value pairs specified in an .rsv file when using the IEssCube.calcFileWithRunTimeSubVarFile Java API or EssCalcFileWithRuntimeSubVars C API).

In the following example, two runtime substitution variables (Entity and Currency) and their values are logged in one entry:

Executing calc script 'calcprofit.csc' with runtime substitution variables {Entity = “MyCompany”; Currency = “USD”;}

Using Runtime Substitution Variables in Calculation Scripts Run in Smart View

The information in this topic applies to running a calculation script with runtime substitution variables in Smart View. Also see Using Runtime Substitution Variables in Calculation Scripts Run in Essbase.

Calculation scripts that are launched in Smart View can include runtime substitution variables.

To use a calculation script that includes runtime substitution variables in Smart View, the runtime substitution variable declaration in the SET RUNTIMESUBVARS calculation command must include the <RTSV_HINT> tag. Additionally, the <RTSV_HINT> tag must include the <svLaunch> tag. Typically, the <svLaunch> tag includes additional XML tags that provide metadata for executing the calculation script in Smart View.

Syntax of a runtime substitution variable definition for use in Smart View:

SET RUNTIMESUBVARS
{
   rtsv = POV
   <RTSV_HINT>
      <svLaunch>
         <description>rtsv_description</description>
         <type>member | string | number</type>
         <dimension>dimName</dimension>
         <choice>single | multiple</choice>
         <allowMissing>true | false</allowMissing>
      </svLaunch>
   </RTSV_HINT>
};

The runtime substitution variable value must be set to the POV, so that only a visible slice of data in the database is calculated.

In Smart View, when you select a calculation script that includes runtime substitution variables on the Calculation Scripts dialog box, the Runtime Prompts area is populated with fields based on how the runtime substitution variables are defined in the SET RUNTIMESUBVARS calculation command. You can run the calculation script as defined or you can use the runtime prompts to enter different variable information based on the data type.

Note:

In Smart View, you cannot run a calculation script that includes runtime substitution variables if the SET RUNTIMESUBVARS command does not include the <RTSV_HINT><svLaunch>...</svLaunch></RTSV_HINT> tags.

Example: Runtime Substitution Variable Set to POV

To set a runtime substitution variable to the POV, the value of the runtime substitution variable must be set to "POV" and the data type must be set to member.

By default, the calculation script uses the active member in the POV at runtime unless the Smart View user specifies a different member in the runtime prompt.

In this example, assume that the database has these dimensions: Account, Entity, Period and Scenario. Three runtime substitution variables are defined (named Entity, Scenario, and Period) and the value for each variable is set to POV. The runtime substitution variables are referenced in the FIX statement as &Entity, &Scenario, and &Period.

SET RUNTIMESUBVARS {
   Entity = POV
      <RTSV_HINT>
         <svLaunch>
            <description>Entities to Copy</description>
            <type>member</type>
            <dimension>Entity</dimension>
            <allowMissing>false</allowMissing>
            <choice>multiple</choice>
         </svLaunch>
      </RTSV_HINT>;
   Scenario = POV
      <RTSV_HINT>
         <svLaunch>
            <description>Scenarios to Copy</description>
            <type>member</type> 
            <allowMissing>false</allowMissing>
            <dimension>Scenario</dimension>
            <choice>multiple</choice>
         </svLaunch>
      </RTSV_HINT>;
   Period   = POV
      <RTSV_HINT>
         <svLaunch>
            <description>Period to Copy</description>
            <type>member</type>
            <allowMissing>false</allowMissing>
            <dimension>Period</dimension>
            <choice>single</choice>
         </svLaunch>
      </RTSV_HINT>;
}
FIX(&Entity, &Scenario, &Period)
“Opening Balance” (
@PREV(“Closing Balance”);
)

Note:

In Essbase, you cannot run a calculation script that includes a runtime substitution variable that is set to POV.

XML Tag Reference—Calculation Scripts with Runtime Substitution Variables for Smart View

Runtime substitution variables for Smart View XML tags defined:

<RTSV_HINT>

Required tag for defining runtime substitution variables for use in Smart View.
<svLaunch>

Required tag that indicates that the runtime substitution variable is defined for use in Smart View.

This tag is the parent tag for these tags: <description>, <type>, <dimension>, <choice>, and <allowMissing>.
<description>rtsv_description</description>

The rtsv_description is a string that describes the runtime substitution variable. The string is not used in the calculation.
<type>value</type>

Valid data type values are:
- member—The runtime substitution variable value must be defined as a member (a single member name or a comma separated list of member names), or POV.
  
  Note:
  
  Member names must be enclosed in quotes; for example, "New York" (single member) or "New York","Florida" (a comma separated list of member names).
  
  If the runtime substitution variable value is set to POV, the <type> value must be member. Also see the <choice> tag.
  
  In Essbase, you cannot run a calculation script that includes a runtime substitution variable that is set to the POV.
- string—The runtime substitution variable value can be defined as a single member name, a comma separated list of member names (for example, "New York","Florida"), or a date.
  
  When using the string data type, the Member Selection dialog box is not available; therefore, the Smart View user must be sure to use the correct syntax (enclosing a member name in quotes, separating multiple member names with a comma, or, for a date, matching the format of the date string to the format that is defined in the calculation script—mm-dd-yyyy or dd-mm-yyyy).
- number—The runtime substitution variable value must be defined as a number
<dimension>dimName</dimension>

Name of the dimension.

Note:

This XML tag is supported only if the <type> value is member.
<choice>value</choice>

Valid choice values are:
- single:
  - If there is a single member on the grid or POV, that member is used.
  - If a dimension is on the POV, the active member is used.
  - If a dimension is on the POV and there are multiple members, an error occurs.
- multiple—All dimension members on the grid or the POV are used.
Note:

This XML tag is supported only if the <type> value is member.
<allowMissing>boolean</allowMissing>

Specifies whether to allow or suppress data cells for which no data exists in the database.

Valid values: true and false.