Using the Analyzer
Defining and Using Pivot Variables
This chapter describes how to define pivot variables and use them in pivot tables. It discusses the following topics:
A pivot variable is saved with the subject area. You can use it in multiple pivot tables and in controls on dashboards.
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 among.
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.
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. Note that you cannot create multiple pivot variables whose names differ only in case.
Optionally specify the following additional options:
Specify the display name of the variable. The default caption is the logical name.
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.
Select this if you intend to use this variable to represent an MDX expression.
Select this if you intend to use this variable to represent a literal number or string, for use within an MDX expression.
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.
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.
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.
An expression pivot variable
represents an MDX expression.
To define an expression pivot variable:
A value for an expression pivot variable should be an expression of one of the following types:
[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.
To use an expression pivot variable in a pivot table, drag and drop the variable into Rows
, 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.
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:
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.
To define a literal pivot variable:
A literal pivot variable has a literal string or numeric value.
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.
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, next.
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:
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.
To redefine a pivot variable:
Click the Pivot Variable button
To delete a pivot variable:
Click OK to confirm this deletion.