Substitution variables are global placeholders for regularly changing information. Since 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 have to 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 in the following areas:
• Aggregate storage outline formulas See Using MDX Formulas.
• Block storage outline formulas See Using Substitution Variables in Formulas.
• Calculation scripts
See Developing Calculation Scripts.
• Report scripts See Developing Report Scripts.
• Data load rules file header definitions and field definitions. You can type in variable names for dimension and member names.
See these Essbase Administration Services Online Help topics: “Setting Headers in the Rules File” and “Mapping Field Names.”
• Partition definitions
See Substitution Variables in Partition Definitions.
• Data source name (DSN) specifications in rules files for SQL data sources
See Essbase SQL Interface Guide.
• SELECT, FROM, or WHERE clauses in rules files for SQL data sources See Essbase SQL Interface Guide.
• Security filters
See Filtering Using Substitution Variables.
• MDX statements
See Using Substitution Variables in MDX Queries.
• Essbase Spreadsheet Add-in for Excel.
See the Essbase Spreadsheet Add-in for Excel User's Guide.
You can set substitution variables on the Essbase Server using Administration Services, MaxL, or ESSCMD. Set the variable at any of the following levels:
• Essbase Server—providing access to the variable from all applications and databases on the Essbase Server
• Application—providing access to the variable from all databases within the application
• Database—providing 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 be composed of alphanumeric characters or underscores ( _ ) and cannot exceed the lim it specified in the section called “Names and Related Artifacts”
• The substitution variable name cannot include non-alphanumeric 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 is as follows: 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 the section called “Names and Related Artifacts”
• 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 Using Dimension and Member Names in Calculation Scripts, Report Scripts, Formulas, Filters, and Substitution Variable Values, different rules apply for how you enter the variable:
o Enclose the member-name value in brackets ([ ]) if it is used in MDX statements.
o 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:
o 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”). Most of the time substitution variables are used with block storage databases; they are not used in MDX statements.
o 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 on the Essbase Server at the server, application, or database level. Before setting a substitution variable, see Rules for Setting Substitution Variable Names and Values.
To set a substitution variable, use a tool:
Tool Topic Location
Administration
Services Managing Substitution
Variables Essbase Administration Services Online Help
MaxL alter system
alter application
alter database
Essbase Technical Reference
ESSCMD CREATEVARIABLE Essbase Technical Reference
To ensure that a new substitution variable value is available in formulas, partition definitions, and security filters, stop and restart the application. All other uses of substitution variables are dynamically resolved when used.
Deleting Substitution Variables
You may need to delete a substitution variable that is no longer used.
To delete a substitution variable, use a tool:
Tool Topic For More Information
Administration Managing Substitution Essbase Administration Services
Tool Topic For More Information
Services Variables Online Help
MaxL alter system
alter application
alter database
Essbase Technical Reference
ESSCMD DELETEVARIABLE Essbase Technical Reference
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, use a tool:
Tool Topic For More Information
Administration
Services Managing Substitution
Variables Essbase Administration Services Online Help
MaxL alter system
alter application alter database
Essbase Technical Reference
ESSCMD UPDATEVARIABLE Essbase Technical Reference
Copying Substitution Variables
You can copy substitution variables to any Essbase Server, application, or database to which you have appropriate access.
To copy a substitution variable, see “Copying Substitution Variables” in the Essbase Administration Services Online Help.