Defining and Using Pivot Variables

Overview

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:

1. Click the Add Pivot Variable button .

   The system displays the following dialog box (partially shown here):

   

2. 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.

3. 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.

     For information on the kinds of values to provide, see Expression Pivot Variables and Literal Pivot Variables, later in this page.

   • 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.

     For details, see Expression Pivot Variables and Literal Pivot Variables, later in this page.

   • 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.

     In all cases, for information on the kinds of values to provide, see Expression Pivot Variables and Literal Pivot Variables, later in this page.

4. Click OK.

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.

[dimension name].[hierarchy name].[level name].[member name]

[dimension name].[hierarchy name].[level name].&[member key]

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.

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.

Redefining a Pivot Variable

To redefine a pivot variable:

1. Select the pivot variable in the Model Contents pane.

2. Click the Pivot Variable button .

3. Make changes as needed.

Deleting a Pivot Variable

To delete a pivot variable:

1. Select the pivot variable in the Model Contents pane.

2. Click the X button.

3. Click OK to confirm this deletion.