Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.3 / Application Development / Using the Analyzer / Filtering Pivot Tables
Previous section   Next section

Filtering Pivot Tables

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 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.
Also see “Accessing the Samples Shown in This Book,” in the first chapter.

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:
images/d2anly_filters_box.png
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:
images/d2anly_filter_alt_drop.png
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:
images/d2anly_filter_info.png

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):
images/d2anly_pivot_with_filter_dropdown.png
Then you can use the control to select members of the Favorite Color level. When you click the Search button images/d2anly_button_search.png, you see something like this:
images/d2anly_filter_year_init_state.png
Here you can do the following:
  • Select All to see all members of this level (that is, perform no filtering with this level).
    Note that the maximum number of members that can be displayed is 2500.
  • Select one or more members to filter the pivot table to use only data for those members.
    You must press Ctrl while selecting multiple members.
  • Select one or more members and select Exclude Members to filter the pivot table to use data for all members except for the selected ones.
  • Click Range and then specify an inclusive range of members. When you click Range, the control changes to look like this:
    images/d2anly_filter_year_range.png
    In each drop-down list, select a member. For a time level, the list of members includes NOW, which always refers to the current date.
For other options, see “Filtering Data,” in the chapter “Using Dashboards” in Using Dashboards and the User Portal.
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:
  • When you save the pivot table, the Analyzer saves any selections in the filter control. When you next open the pivot table, the Analyzer redisplays the filter control as you saved it.
  • The pivot table as seen in a dashboard is filtered as you saved it and does not include the filter control. (You use a separate mechanism to provide filters to users of your dashboards.)

Adding Advanced Filters

To see the filter definition for an advanced filter, click the Advanced Options button images/d2anly_button_advancedfilter_reconfigure.png next to that filter. The system then displays a dialog box with information like the following:
images/d2anly_advancedfilter_example.png
This represents the logic of the filter, but not its literal syntax. This filter selects only products that are either of the following:
  • Candy products sold in Bangalore
  • Snack products sold in Houston
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 images/d2anly_button_advanced_options.png in the Filters box. The system displays a dialog box like the following:
    images/d2anly_advancedfilter_editor.png
  2. Click Add Condition.
    The dialog box now looks something like this:
    images/d2anly_advancedfilter_editor_step2.png
    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:
      images/d2anly_advancedfilter_editor_step4.png
      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:
      images/d2anly_advancedfilter_editor_step5.png
    2. In the left area, click the Search button images/d2anly_button_search.png 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:
      images/d2anly_advancedfilter_editor_step6.png
    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:
      images/d2anly_advancedfilter_editor_step7.png
    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 images/d2anly_button_advanced_options.png in the Filters box.
  2. Click Add Condition.
    The dialog box now looks something like this:
    images/d2anly_advancedfilter_editor_step2_age.png
    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:
    images/d2anly_searchable_measure_in_list.png
    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:
    images/d2anly_searchable_measure_selected.png
  6. Click the operator, which is an equals sign (=) by default.
    Now the editor displays the following set of operators:
    images/d2anly_searchable_measure_choose_operator.png
    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:
images/d2anly_searchable_measure_example_filter.png
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 images/d2anly_button_named_filter.png 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.
images/d2anly_modelcont_namedfilters.png
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 images/d2anly_button_named_filter.png.
  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.
images/d2anly_filter_disabling.png
To remove a filter, click the X to the right of the name of the filter.

How Business Intelligence 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):
  • The system uses a logical AND to combine all the items shown in the bar above the pivot table.
    Filter Meaning
    images/d2anly_filter_meaning1.png
    All patients whose favorite color is green and whose favorite color is blue (zero patients)
    images/d2anly_filter_meaning3.png
    All patients who are allergic to both mold and to pollen
    images/d2anly_filter_meaning5.png
    All patients whose favorite color is green and who are allergic to ant bites
  • If this bar displays a level, and if you have selected one or more members of that level in the drop-down, those members are combined with a logical OR.
    Filter Meaning
    images/d2anly_filter_meaning2.png
    All patients whose favorite color is either green or blue
    images/d2anly_filter_meaning4.png
    All patients who are allergic to mold, to pollen, or to both
    images/d2anly_filter_meaning6.png
    All patients whose favorite color is green or blue and who also are allergic to ant bites
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, The system 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 Business Intelligence Query Engine Works” in Implementing InterSystems Business Intelligence.

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:
  • A name (shown in the Analyzer and pivot tables). For example, in the Patients sample, a member of the Doctor level might have the name Vivaldi, Lola
    Names are not necessarily unique.
  • A key (never displayed). This is often but not always the same as the name. For example, in the Patients sample, the doctor whose name is Vivaldi, Lola might have the key 12
    The keys should be unique in a well-defined cube. (The system does not force them to be unique, but the developers who create a cube can and should take steps to ensure their uniqueness.)
    When you create a filter by drag and drop actions, the system automatically uses the member key rather than the member name.
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 images/d2anly_button_query_text.png on the toolbar.
    The system displays the current MDX query, which looks something like this:
    images/d2anly_member_key_seeing.png
    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 other cases, it might be necessary to display a detail listing in order to correctly identify the desired member.
Previous section   Next section