Defining and Using Pivot Variables
This article describes how to define pivot variables and use them in Business Intelligence pivot tables.
A pivot variable is saved with the subject area. You can use it in multiple pivot tables and in controls on dashboards.
Also see Accessing the BI Samples.
Pivot variables provide another way for your end users to interact with your dashboards.
The definition of a pivot variable includes the following elements:
A name, which is not case-sensitive.
A default value, for use when the user has not specified a value for the variable.
(In most cases) A set of allowed values for the user to choose from.
These values are generally MDX identifiers, MDX expressions, or literal numbers or strings, depending on precisely how the variable is intended to be used.
Pivot variables are generally intended to be used in pivot tables. Specifically, when you create a pivot table, you can use pivot variables in selected parts of the query that defines the pivot table. When a dashboard displays the pivot table, that dashboard can include a pivot variable control, with which the user can change the value of the corresponding pivot variable. The system simply substitutes the given value into the query, executes the query, and then redisplays the pivot table.
Similarly, when you access a dashboard via a URL, that URL can include parameters that specify the value for pivot variables used in pivot tables on the dashboard. Before displaying the dashboard, the system substitutes the given value into the queries and executes the queries.
Defining a Pivot Variable
In general, to define a pivot variable:
Click the Add Pivot Variable button .
The system displays the following dialog box (partially shown here):
For Variable Name, type the logical name of the variable, to be used internally.
The logical name must contain only alphanumeric characters. The name is not case-sensitive; you cannot create multiple pivot variables whose names differ only in case.
Optionally specify the following additional options:
Caption — Specify the display name of the variable. The default caption is the logical name.
Description — Specify a description.
Default Value — Specify the default value. The best practice is to specify a default value so that the system has a valid MDX query for any pivot table that uses this variable.
How is this variable used — This option affects how the system handles the variable internally. Select one of the following:
Expression — Select this if you intend to use this variable to represent an MDX expression.
Literal — Select this if you intend to use this variable to represent a literal number or string, for use within an MDX expression.
What type of value will this variable have — This option affects the default style of the control used when you add a pivot variable control to a dashboard.
Source of values for this variable — Specify the source of possible values for this pivot variable. Select one of the following:
Manual — Select this if you want to provide a hardcoded list of values, if you want to display a calendar control, or if you want the user to type a value.
If you select Manual, do one of the following:
To specify a hardcoded list of values, specify Values and (optionally) Captions. For Values, specify one value per line. For Captions, specify the corresponding captions, if any, with one caption per line. In this case, the pivot variable control displays your list of options.
To display a calendar control, select day for What type of value will this variable have. In this case, the pivot variable control is a calendar control.
To enable the user to type a value, do not specify Values or Captions. Also, for What type of value will this variable have, do not select day. In this case, the pivot variable control is a simple field into which the user can type a value.
Termlist — Select this to use a term list to provide the values. If you select this, click the Search button and then select a term list. For each item in the term list, the system uses the item value as the value and uses the item key as the corresponding caption. The pivot variable control displays your list of options.
KPI — Select this to use a KPI to provide the values. If you select this, click the Search button and then select a KPI.
The KPI should have a property called Value. For each series in the KPI, the system uses the Value property as the value and uses the series name as the corresponding caption. The pivot variable control displays your list of options.
The new variable is then displayed within the Pivot Variables section in the Model Contents pane.
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:
[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)||
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]
[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.
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.
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.
Redefining a Pivot Variable
To redefine a pivot variable:
Select the pivot variable in the Model Contents pane.
Click the Pivot Variable button .
Make changes as needed.
Deleting a Pivot Variable
To delete a pivot variable:
Select the pivot variable in the Model Contents pane.
Click the X button.
Click OK to confirm this deletion.