Using the DeepSee Analyzer
Filtering Pivot Tables
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter describes how to use the Analyzer to include filters in a pivot table, which restricts the set of records that the pivot table accesses. It discusses the following topics:

Note:
Applying a filter to a pivot table is different from adding a filter control to a pivot table widget in a dashboard (see Creating DeepSee Dashboards). A filter control enables another user later to filter the data. You can add filter controls to a widget that displays a pivot table that has its own filter.
About Filters
Filters restrict the set of records that the system uses.
You use the Filters box, shown below, to add filters to your pivot table:
Filters can be either simple or advanced, and you can add any combination of filters to your pivot table.
To create simple filters, you can also drag and drop filter items to the filter bar directly above the pivot table:
Later sections describe how to create these filters in detail.
Dragging and Dropping Filter Items
You can drag and drop an individual member to use as a filter. To do so, drag and drop a member to Drop filter here in the Filters box or to the filter bar.
The pivot table is then filtered to show only data for that member. The Filters box displays the name of the associated level, and the filter bar displays the name of the level and the member. For example:
Creating and Using a Drop-Down Menu
You can drag and drop a level to the Filters box or the filter bar and then use the filter drop-down menu that the system displays. This section provides the details. (You can use expression pivot variables in the same way. See Defining and Using Expression Pivot Variables,” later in this book.)
For example, if you drag and drop the Year level, you might see this in the Analyzer (depending on what else you have done):
Then you can use the control to select members of the Favorite Color level. When you click the Search button , you see something like this:
Here you can do the following:
For other options, see Filtering Data,” in the chapter Using Dashboards in DeepSee End User Guide.
When you are done, click the check mark button to accept the selections. Or click the X button to discard your changes.
Note:
The filter control is part of the Pivot Builder area, not part of the Pivot Preview area. This has two implications:
Adding Advanced Filters
To see the filter definition for an advanced filter, click the Advanced Options button next to that filter. DeepSee then displays a dialog box with information like the following:
This represents the logic of the filter, but not its literal syntax. This filter selects only products that are either of the following:
The Advanced Filter Editor enables you to easily add, modify, and remove parts of a filter expression, while seeing the resulting expression with every change. This book does not describe this tool in detail, but instead provides a brief demonstration:
  1. Click the Advanced Options button in the Filters box. The system displays a dialog box like the following:
  2. Click Add Condition.
    The dialog box now looks something like this:
    Initially, the advanced filter editor uses the first level (considering alphabetic order) defined in your subject area.
  3. If you want to use a different level:
    1. Click YearSold, which enables you to edit this part of the expression.
    2. Optionally type a string into the search box and then click Search. This is useful if the cube has a large number of levels.
    3. Select a level from the drop-down list on the left. As soon as you do, the expression is updated. For example:
      The end of the drop-down list may include measures; if so, see Filtering the Data by Measure Value,” later in this chapter.
  4. If you want to change the operator from IS to IS NOT:
    1. Click IS, which enables you to edit this part of the expression.
    2. In the left area, click IS NOT. As soon as you do, the expression is updated.
  5. To specify a member of the level:
    1. Click <select a value>, which enables you to edit this part of the expression. The left area might look like the following:
    2. In the left area, click the Search button under Select a value to display a dialog.
    3. The resulting dialog displays a set of members and options. Optionally type text into the search box and press Enter; this restricts the set of members listed in the dialog.
      The list of values also includes all searchable measures (see the next section) and all pivot variables (see Defining and Using Pivot Variables,” later in this book).
    4. Select members and options by selecting their check boxes.
    5. Click the check mark button in the upper right hand corner of the dialog. As soon as you do, the expression is updated.
      For example:
    6. Alternatively, you may enter an MDX key by selecting the MDX Key option.
    This advanced filter is equivalent to a simple filter. Typically you would now add another condition or a branch that contains other conditions.
  6. To add another condition:
    1. Click AND, so that you can edit this item.
    2. Optionally, in the left area, click OR. As soon as you do, the expression is updated.
    3. Click Add condition.
      You might now see the following:
    4. Edit this condition in the same way that you edited the first one.
  7. Click OK to close the dialog box.
    Or add another condition or a branch that contains other conditions.
Note that you can undo any change. To do so, click Undo.
Filtering the Base Records by Measure Value
If your subject area includes searchable measures, you can filter records used in a pivot table by the value of those measures. To do so:
  1. Click the Advanced Options button in the Filters box.
  2. Click Add Condition.
    The dialog box now looks something like this:
    The advanced filter editor initially uses the first level (considering alphabetic order) defined in your subject area.
  3. Click Age Group, which allows you to edit this part of the expression.
  4. Click the drop-down list on the left and scroll to the end of the list, which may include measures. For example:
    Note that the list of values also includes all pivot variables (see Defining and Using Pivot Variables,” later in this book).
  5. Click the measure that you want to use.
    Now the expression is updated as follows:
  6. Click the operator, which is an equals sign (=) by default.
    Now the editor displays the following set of operators:
    Or, if the searchable measure contains string values, the editor displays the following operators: = <> LIKE
  7. Click the comparison value, which is 0 by default.
    Now the editor displays a box into which you can type a new value.
  8. Type a value and then click Apply.
    If you enter a string value, enclose it within single quotes. For example: 'abc'
  9. Click OK to close the dialog box.
    Or add another condition or a branch that contains other conditions.
For example, consider the following filter:
This filter selects all patients whose test score is 65 or higher.
Defining Named Filters
A named filter is saved with the subject area. You can use it in multiple pivot tables and in filter controls on dashboards.
To define a named filter:
  1. Make sure that no named filter is currently selected.
  2. Click the Named Filter button in the Model Contents pane.
  3. For Filter Name, type a user-friendly filter name.
  4. For Description, type an optional description.
    The rest of the dialog box is the same as the Advanced Filter Editor.
  5. For details on the rest of this dialog box, see Adding Advanced Filters,” earlier in this chapter.
When you are done, the Model Contents pane displays the filter within the Named Filters section.
Because a named filter can be relatively complex, it can be useful to define appropriate named filters for use on dashboards. If you do this, users of the dashboards can filter their data more easily.
Redefining a Named Filter
To redefine a named filter:
  1. Select the named filter in the Model Contents pane.
  2. Click the Named Filter button .
  3. Make changes as needed.
    For details, see Adding Advanced Filters,” earlier in this chapter.
Deleting a Pivot Variable
To delete a named filter:
  1. Select the named filter in the Model Contents pane.
  2. Click the X button.
  3. Click OK to confirm this deletion.
Using Named Filters
To use a named filter, drag and drop it from the Model Contents pane to the Filters box.
Or double-click the named filter; then it is added to the Filters box.
Disabling or Removing Filters
To disable a filter, clear the check box to the left of the name of the filter in the Filters box.
To remove a filter, click the X to the right of the name of the filter.
How DeepSee Combines Filters
This section describes how the system combines filters.
If you apply multiple simple filters, the system combines them as follows (but see the important note after this list):
If you have also applied advanced filters, those are combined with the other filters via logical AND.
Important:
Depending on the form of the combined filter, DeepSee might perform axis folding. This phrase refers to the process of combining multiple filters (a filter is considered to be a query axis). Axis folding means that if a given source record has a non-null result for each slicer axis, that record is counted multiple times. For details on when this occurs, see Axis Folding in the appendix How the DeepSee Query Engine Works in the DeepSee Implementation Guide.
Finding the Key for a Member
This section describes how and why to find the key identifier for a member.
Each member has two identifiers:
You should use the key when you specify a filter value manually, which you do in the following scenarios:
It is also best practice to use the key when you refer to a member in a pivot variable.
To find the key for a given member, do the following:
  1. Drag and drop the member to the Filters box in the Analyzer.
  2. Click the Display Query button on the toolbar.
    The system displays the current MDX query, which looks something like this:
    The %FILTER part specifies the filter. The [DocD].[H1].[Doctor].&[32] part is the complete member identifier for this member. The key for this member is 32.
If the level contains multiple members with the same name, and if you are not sure which member to use, try the following technique:
  1. Drag and drop each of the members to Rows.
  2. If this level has a property, drag and drop that to Columns. The property values are typically different for each member, and you can use the information here to determine which member to look at.
    Or drag and drop a level to Columns. Choose a level that can give some context. In SAMPLES, you could display Doctor Group as columns. You might see something like this:
    According to this pivot table, the first Sam Presley is not in any doctor group, and the second one is in doctor group II. You can use this information to determine which member to look at.
    In other cases, it might be necessary to display a detail listing in order to correctly identify the desired member.