Defining and Using an Expression Pivot Variable
An expression pivot variable represents an MDX expression.
Defining an Expression Pivot Variable
To define an expression pivot variable:
-
For How is this variable used, select Expression.
-
For Default Value, specify an MDX expression.
-
For Source of values for this variable, either specify a hardcoded set of MDX expressions or select a term list or KPI that returns a set of MDX expressions. See the next subsection.
Values for Expression Pivot Variables
A value for an expression pivot variable should be an expression of one of the following types:
| Expression Type |
Examples |
| member reference |
[aged].[h1].[age group].&[0 to 29]
See the comments after this table. |
| tuple expression (which represents an AND of multiple members) |
([aged].[h1].[age group].&[0 to 29],[gend].[h1].[gender].&[female])
Notice that the member references are separated by commas, and the expression is enclosed in parentheses. |
| set expression (which represents an OR of multiple members) |
{[homed].[h1].[city].&[magnolia],[homed].[h1].[city].&[pine]}
Notice that the member references are separated by commas, and the expression is enclosed in curly braces. |
The general syntax for a member reference is as follows:
[dimension name].[hierarchy name].[level name].[member name]
Or:
[dimension name].[hierarchy name].[level name].&[member key]
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:
