Implement Variables for Changing Information
You can use Essbase substitution variables as placeholders for regularly changing information. Because changes to a variable value are reflected everywhere the variable is used, the need to make manual updates is minimized.
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 Essbase 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 (ASO) and block storage (BSO) 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
-
Report scripts
You can set substitution variables at these levels:
-
Globally: Provides access to the variable from all applications and databases on the Essbase Server.
-
Application: Provides access to the variable from all databases within the application.
-
Database: Provides access to the variable within the specified database.
Allowed Substitution Variable Names and Values
When you use substitution variables in Essbase,follow the guidelines pertaining to the characters and data types you can use in substitution variable names and values.
The following restrictions apply to substitution variable names and values:
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 (&). Limit of characters: see Limits.
Essbase member names used as variable values
Enclose member names that start with a number or contain any special characters in quotation marks (" ") for block storage databases, and in brackets ([ ]) for aggregate storage databases. Refer to Naming Conventions in Calculation Scripts, Report Scripts, Formulas, Filters, and Substitution and Environment Variable Values for more details.
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 "[2022].[Qtr1]".
Do not use a substitution variable name as a part of a qualified member name. For example, it is invalid to specify "[2022].[&CurrentQtr]".
Numbers used as variable values
Enclose numeric values in quotation marks (" ") for block storage (BSO) databases, and in brackets ([ ]) for aggregate storage (ASO) databases.
For example, if the variable name is Month, and its value is 01, then for block storage, set the value as "01". For aggregate storage, set the value as [01].
Multiple variables having the same name
Variables may have the same name as long as they are defined at different scopes. Variable resolution precedence works as follows: database variable resolves before application variable. Application variable resolves before global variable.
Create or Set Substitution Variables
Before setting a substitution variable, see Allowed Substitution Variable Names and Values.
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.
When you add or update substitution variables, they are sent to the application and dynamically resolved.
To set a substitution variable using the Essbase web interface, refer to Use Substitution Variables. You can also use an application workbook to set variables at the application level, or use the following MaxL statements:
Remove Substitution Variables
You may want to delete an Essbase substitution variable that is no longer needed.
To delete a substitution variable, you can remove it from your application workbook and build the cube, delete it using the Essbase web interface, or use these MaxL statements:
Update Substitution Variables
Because substitution variables are placeholders for changing information, you may need to update their values frequently. You can modify existing substitution variables using MaxL or the Essbase web interface.
To update a substitution variable, you can use one of these MaxL statements (which one to use depends on the context of your variable):
-
alter system to update a global (Essbase Server-level) variable
-
alter application to update an application-level variable
-
alter database to update a cube-level variable
If you would rather use the Essbase web interface to update a variable, refer to Use Substitution Variables for instructions.