Runtime Substitution Variables in Calculation Scripts Run in Smart View

You can use runtime substitution variables (RTSVs) in Essbase calc scripts that you design for execution in Smart View. The RTSVs enable the calculation's region of focus to be dynamic, depending on the active grid context (POV).

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.

Use the following syntax to define a runtime substitution variable definition for use in Smart View:

SET RUNTIMESUBVARS
{
   rtsvName = 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>
};

In the definition above, the runtime substitution variable name is rtsvName, but you can use any name. The variable's value must be set to POV, to indicate that only the current data slice present in the spreadsheet grid should be calculated.

The <RTSV_HINT> element must also be part of the variable declaration. Its contents include:

• <description>: A descriptor to help Smart View users understand the member selection
• <type>: A specification of whether the variable is for a member, string, or number
• <allowMissing>: true if missing data should be included; false otherwise
• <dimension>: The name of the dimension from which this variable pulls information
• <choice>: single if only one selection may be passed to the runtime substitution variable; multiple otherwise.

For a full description of the XML tags listed above, refer to XML Tag Reference—Calculation Scripts with Runtime Substitution Variables for Smart View.

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 Smart View active grid context (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 rtsvEntity, rtsvScenario, and rtsvPeriod), and the value for each variable is set to POV. The runtime substitution variables are referenced in the FIX statement as &rtsvEntity, &rtsvScenario, and &rtsvPeriod.

SET RUNTIMESUBVARS {
   rtsvEntity = POV
      <RTSV_HINT>
         <svLaunch>
            <description>Entities to Copy</description>
            <type>member</type>
            <dimension>Entity</dimension>
            <allowMissing>false</allowMissing>
            <choice>multiple</choice>
         </svLaunch>
      </RTSV_HINT>;
   rtsvScenario = POV
      <RTSV_HINT>
         <svLaunch>
            <description>Scenarios to Copy</description>
            <type>member</type> 
            <allowMissing>false</allowMissing>
            <dimension>Scenario</dimension>
            <choice>multiple</choice>
         </svLaunch>
      </RTSV_HINT>;
   rtsvPeriod   = 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(&rtsvEntity, &rtsvScenario, &rtsvPeriod)
"Opening Balance" (
@PREV("Closing Balance");
)

Below the variable declarations is a FIX block, which selects the calculation's region of focus, and executes a command to set the period's opening balance. The runtime substitution variables are passed into the FIX block using this syntax: &varname. The calculation's region of focus is, therefore, dynamic, depending on the POV in Smart View.

Note:

When using the Essbase jobs interface, the MaxL execute calculation statement, or APIs (such as the REST API Execute Jobs endpoint), you cannot run a calculation script that includes a runtime substitution variable that is set to POV. As these are dependent on current Smart View grid context, they must be run from Smart View.

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

When you design Essbase calculation scripts with runtime substitution variables that are designed to execute based on Smart View POV/grid context, you need to use some XML tags which are explained here.

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

    Using Essbase jobs or MaxL, you cannot run a calculation script that includes a runtime substitution variable that is set to the POV. These calculation scripts are meant to be run from Smart View, as POV is dependent on the current grid context to determine the slice.

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