In calculation scripts, you can use system environment variables as placeholders for user-specific system settings. Because environment variables are defined at the operating system level, they are available to all calculation scripts on Essbase Server.
Note: | Environment variables cannot be used in MDX queries. |
To declare a system environment variable, see your operating system documentation.
To use an environment variable in a calculation script, insert the dollar sign ($) character before the environment variable name. Essbase treats any string that begins with a leading dollar sign as an environment variable, replacing the variable with its assigned value before parsing the calculation script. If a member name begins with $, enclose the name in quotation marks.
When using environment variables in calculation scripts, follow these guidelines:
Environment variable names:
Must consist of alphanumeric characters or underscores (_)
Cannot include nonalphanumeric characters, such as hyphens (-), asterisks (*), and slashes (/)
Cannot exceed 320 bytes for Unicode-mode applications and 320 characters for non-Unicode mode applications
Environment variable values:
May contain any character except a leading dollar sign ($)
Whether numeric or non-numeric, must be enclosed in quotation marks (" "). For example:
MY_PROD="100" ENV_FILE="E:\temp\export1.txt"
For non-numeric values, if you do not enclose the value in quotation marks when you define the environment variable, Essbase automatically encloses the value with quotation marks when the environment variable is passed.
For numeric values, Essbase does not automatically enclose the value with quotation marks when the variable is passed because Essbase cannot determine if you intend to pass a numeric value or a member name. For example, if you use a calculation script statement such as 'Sales = $MY_SALES' where MY_SALES=700, the intent is to pass the numeric value of 700. If, however, Essbase encloses MY_SALES in quotation marks, MY_SALES is treated as a member name. The member name would be passed, not the numeric value, causing an error. If you want the numeric value of the variable to be treated as a string, you must enclose the value with quotation marks when you define the environment variable.
Cannot exceed 256 bytes for Unicode-mode applications and 256 characters for non-Unicode mode applications
For example, you can use an environment variable to define the path and filename for an export file when exporting a block of data to a flat file. In the following calculation script, the path and filename (E:\temp\export1.txt) are explicitly defined:
SET DATAEXPORTOPTIONS { DATAEXPORTLEVEL "ALL"; DATAEXPORTOVERWRITEFILE ON; }; FIX ("New York", "100-10"); DATAEXPORT "File" "," "E:\temp\export1.txt"; ENDFIX;
You can declare an environment variable (ENV_FILE) to reference the path and filename (ENV_FILE="E:\temp\export1.txt") and use the following syntax in the calculation script:
DATAEXPORT "File" "," $ENV_FILE;
Essbase replaces the environment variable with the value taken from the user's environment.
In the following example, another environment variable (CurrMbr) is defined to export only Sales values (CurrMbr="Sales"):
SET DATAEXPORTOPTIONS { DATAEXPORTLEVEL "ALL"; DATAEXPORTOVERWRITEFILE ON; }; FIX ("New York", "100-10", $CurrMbr); DATAEXPORT "File" "," $ENV_FILE; ENDFIX;
Environment variables can also be used to parse arguments passed to RUNJAVA, an Essbase utility in which custom-defined functions can be called directly from a calculation script. For example, you can use environment variables to get user e-mail addresses.
In the following example, the RUNJAVA statement sends an e-mail notification to explicitly-defined users (to@somedomain.com and cc@mydomain.com):
RUNJAVA com.hyperion.essbase.calculator.EssbaseAlert "localhost" “to@somedomain.com” "cc@mydomain.com" "" "" "Mail Subject" "Mail Body" "";
You can declare environment variables for the users (ENV_TOMAIL=“to@somedomain.com” and ENV_CCMAIL=“to@mydomain.com”) and use the following syntax in the calculation script:
RUNJAVA com.hyperion.essbase.calculator.EssbaseAlert "localhost" $ENV_TOMAIL $ENV_CCMAIL "" "" "Mail Subject" "Mail Body" "";