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
The following rules apply to substitution variable names and values:
-
The substitution variable name must comprise alphanumeric characters or underscores ( _ ) and cannot exceed the limit specified in Limits.
-
The substitution variable name cannot include nonalphanumeric characters, such as hyphens (-), asterisks (*), and slashes (/). Do not use spaces, punctuation marks, or brackets ([ ]) in substitution variable names used in MDX.
-
If substitution variables with the same name exist at server, application, and database levels, the order of precedence for the variables: a database-level substitution variable supersedes an application-level variable, which supersedes a server-level variable.
-
The substitution variable value may contain any character except a leading ampersand (&). The substitution variable value cannot exceed the limit specified in Limits.
-
To set a substitution variable value to a duplicate member name, use the qualified member name enclosed in double quotation marks; for example, a value for &Period could be “[2006].[Qtr1]”.
-
When specifying use of a substitution variable, do not insert a substitution variable as a part of a qualified name. For example, it is invalid to specify “[2004].[&CurrentQtr]”.
-
If a substitution variable value is a member name that begins with a numeral or contains spaces or any of the special characters listed in Naming Conventions in Calculation Scripts, Report Scripts, Formulas, Filters, and Substitution and Environment Variable Values, different rules apply for how you enter the variable:
-
Enclose the member-name value in brackets ([ ]) if it is used in MDX statements.
-
Enclose the member-name value in quotation marks (“ ”) if it is not used in MDX statements.
-
-
If a substitution variable value is numeric, different rules apply for how you enter the variable:
-
If it is not used in MDX statements, enclose a substitution variable value in quotation marks; for example, if the variable name is Month, and its corresponding value is 01 (corresponding to January), place quotation marks around 01 (“01”). Substitution variables usually are used with block storage databases; they are not used in MDX statements.
-
If it is used in MDX statements only, such as in formulas in aggregate storage outlines, and the value is numeric or a member name, do not enclose the value in quotation marks.
Note:
If a substitution variable value is numeric or a member name starting with a numeral or containing the special characters referred to above is to be used both in MDX and non-MDX situations, create two substitution variables, one without the value enclosed in quotation marks and one with the value in quotation marks.
-
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