Using the DeepSee Analyzer
Defining and Using Pivot Variables
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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.
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:
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. DeepSee 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, DeepSee 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. Note that you cannot create multiple pivot variables whose names differ only in case.
  3. Optionally specify the following additional options:
  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.
Defining an Expression Pivot Variable
To define an expression pivot variable:
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
See the comments after this table.
tuple expression (which represents an AND of multiple members)
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]
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 the chapter Defining Calculated Elements.”
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:
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, next.
Or define a calculated member that uses the pivot variable. See the chapter Defining Calculated Elements.”
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.