Using Substitution Variables in MDX Queries:

Substitution variables act as global placeholders for information that changes regularly; you set the substitution variables on the server through Administration Services, MaxL, or ESSCMD, and assign a value to each variable.

You can change the value at any time. You must have the role of at least Database Manager to set substitution variables.

--:To use a substitution variable in an MDX expression, consider:
● The substitution variable must be accessible from the application and database you are querying.
● A substitution variable has two components: the substitution variable name and the substitution variable value.
● The variable name can be an alphanumeric combination whose maximum size is specified in the section called “Names and Related Artifacts”on page 1010 Do not use spaces, punctuation, or brackets ([]) in substitution variable names used in MDX.
● At the point in the expression where you want to use the variable, show the variable name preceded by an ampersand (&); for example, where CurMonth is the name of the substitution variable set on the server, include &CurMonth in the MDX expression.
● When you perform the retrieval, Essbase replaces the variable name with the substitution value and that value is used by the MDX expression.
For example, the expression is written showing the variable name CurQtr preceded with the &:

SELECT
{[&CurQtr]}
ON COLUMNS
FROM Sample.Basic

When the expression is executed, the current value (Qtr1) is substituted for the variable name, and the expression that is executed is:

SELECT
{[Qtr1]}
ON COLUMNS
FROM Sample.Basic