You can omit the square brackets for any identifier that contains only alphanumeric characters and that does not start with a number. Also, you can omit the hierarchy and level names if the member name or key is unique within this dimension.
Using an Expression Pivot Variable
To use an expression pivot variable in a pivot table, drag and drop the variable into Rows, Columns, or Filters, as applicable. In each case, the Analyzer adds a control to the filter bar so that you can test the variable. See the example.
Or define a calculated member that uses the pivot variable. See Defining Calculated Elements.
Note:
If the Analyzer encounters an error parsing a query involving a pivot variable, full details are available in the Business Intelligence Logs for debugging purposes.
Example
The following shows the definition of an expression pivot variable:
If we create a new pivot table and then drag and drop this variable to the Rows box, the Analyzer then displays the following simple pivot table:
To test the pivot variable, we can use the filter bar. If we click the search button for SampleExprVariable, the Analyzer displays the possible values for this variable:
If we select a different value, the pivot table changes, for example:
Literal Pivot Variables
A literal pivot variable represents a literal string or numeric value to be used within an MDX expression. Depending on how you define a literal pivot variable, you can use it within the Advanced Filter Editor, in calculated members, or in manually written MDX queries.
Defining a Literal Pivot Variable
To define a literal pivot variable:
-
For How is this variable used, select Literal.
-
For Default Value, specify a fragment suitable for use in an MDX query.
-
For Source of values for this variable, either specify a hardcoded set of values or select a term list or KPI that returns a set of values. See the next subsection.
Values for Literal Pivot Variables
A literal pivot variable has a literal string or numeric value.
In the most useful scenario, the variable is intended to represent the last part of a member identifier, and you use it in the Advanced Filter Editor. In this scenario, any value for the variable should be a string of the form &[key] where key is a member key. For example: &[0 to 29]. (See Finding the Key for a Member, earlier in this book.) See the example later in this section.
In another useful scenario, the variable is intended to represent a number that is used as a multiplier or other formula element. In this case, you would use the variable within the definition of a calculated measure.
Using a Literal Pivot Variable
To use a literal pivot variable in a pivot table, use the Advanced Filter Editor. When you add a condition, select a level. For the value for that level, select the name of the literal pivot variable; see the example in the next section.
Alternatively, define a calculated member that uses the pivot variable. See Defining Calculated Elements.
Note:
If the Analyzer encounters an error parsing a query involving a pivot variable, full details are available in the Business Intelligence Logs for debugging purposes.
Example
The following shows the definition of a literal pivot variable (for use with the HoleFoods sample):
Suppose that we create a new pivot table. For this pivot table, we use the Advanced Filter Editor and add a condition. For this condition, we select the YearSold level. For the Start Time value, we can select the following items:
The item $variable.SampleLitVariable is the pivot variable. The other items are the members of the YearSold level.
Note that all the pivot variables are available in all the value drop-downs in the Advanced Filter Editor. The $variable.SampleLitVariable variable is suitable for use only with one level in this case — the YearSold level, because this is the only level that has members with the keys 2008, 2009, and so on.
