Using Substitution Variables
Substitution variables are global placeholders for regularly changing information. Because changes to a variable value are reflected everywhere the variable is used, manual changes are reduced.
For example, many reports depend on reporting periods; if you generate a report based on the current month, you must update the report script manually every month. With a substitution variable, such as CurMnth, set on the server, you can change the assigned value each month to the appropriate time period. When you use the variable name in a report script, the information is dynamically updated when you run the final report.
You can use substitution variables with both aggregate storage and block storage applications (unless otherwise noted) in the following areas:
-
Aggregate storage outline formulas
-
Block storage outline formulas
-
Calculation scripts (block storage databases only)
Substitution variables and runtime substitution variables are supported in calculation scripts.
-
Data load rules file header definitions and field definitions. You can enter variable names for dimension and member names.
-
Data source name (DSN) specifications in rules files for SQL data sources
-
SELECT, FROM, or WHERE clauses in rules files for SQL data sources
-
Security filters
-
MDX queries
-
Smart View
You can set substitution variables at these levels:
-
Globally: Provides access to the variable from all applications and databases on the Essbase instance.
-
Application: Provides access to the variable from all databases within the application.
-
Database: Provides access to the variable within the specified database.
Rules for Setting Substitution Variable Names and Values
When you use substitution variables in Essbase, take note of guidelines pertaining to the characters and data types you can use in substitution variable names and values.
The following rules apply to substitution variable names and values:
| Restriction type | Guideline |
|---|---|
|
Characters allowed in variable name |
Alphanumeric characters and underscores are permitted, but not special characters or spaces. Limit of characters: see Limits. |
|
Characters allowed in variable value |
Any character is allowed except a leading ampersand ( |
|
Essbase member names used as variable values |
Enclose member names that start with a number or contain any special characters in quotation marks ( To set a substitution variable value to be a duplicate member name, use the qualified member name enclosed in double quotation marks; for example, a value for &Period can be Do not use a substitution variable name as a part of a qualified member name. For example, it is invalid to specify |
|
Numbers used as variable values |
Enclose numeric values in quotation marks ( For example, if the variable name is Month, and its value is 01, then for block storage, set the value as |
|
Multiple variables having the same name |
Allowed if the variables are defined at different scopes. Precedence rules: Variable resolves at database level before at application level. Variable resolves at application level before at global level. |
Setting Substitution Variables
You can set substitution variables at the server, application, or database level. Before setting a substitution variable, see Rules for Setting Substitution Variable Names and Values.
When you add or update substitution variables, they are sent to the application and dynamically resolved.
To set a substitution variable, see Using Variables. You can also use application workbook to set at the application level, or use these MaxL statements:
Deleting Substitution Variables
You may need to delete a substitution variable that is no longer used.
To delete a substitution variable, you can remove it from your application workbook, delete it using the Essbase web interface, or use these MaxL statements:
-
alter system
-
alter application
-
alter database
Updating Substitution Variables
You can modify or update existing substitution variables. Before updating a substitution variable, see Rules for Setting Substitution Variable Names and Values.
To update a substitution variable, you can use these MaxL statements:
-
alter system
-
alter application
-
alter database