Skip to main content

Filtering Pivot Tables

This page describes how to use the Business Intelligence Analyzer to include filters in a pivot table, which restricts the set of records that the pivot table accesses.

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 BI Samples.

About Filters

Filters restrict the set of records that the system uses.

Use the Filters box, shown below, to add filters to your pivot table:

Filters box, listing Product category and advanced filter

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:

generated description: filter alt drop

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:

Filters box shows product category, label shows Snack as filter

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

For example, if you drag and drop the Year level, you might see this in the Analyzer (depending on what else you have done):

Year filter shown with no selection

Use the control to select members of the Year level. When you click the Search button , you see something like this:

Year filter expanded to show options

Here you can:

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

    Two dropdowns labeled From and To, each with option NOW currently displayed

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

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 next to that filter. The system then displays a dialog box with information like the following:

Advanced filter shown as logic tree with ANDs and ORs

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 in the Filters box. The system displays a dialog box like the following:

    empty with one option shown: add condition

  2. Click Add Condition.

    The dialog box now looks something like this:

    year sold selector, operator, and value field, with AND option to join to other conditions

    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:

      generated description: advancedfilter editor step4

      The end of the drop-down list may include measures; if so, see Filtering the Data by Measure Value, later in this page.

  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:

      select a value from a list or enter and MDX key

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

    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:

      discount type in range 20 to 49%

    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:

      discount type in range 20 to 49% or year sold is all

    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:

    age group is <select a value>

    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:

    list of levels and searchable measures

    Note that the list of values also includes all pivot variables (see Defining and Using Pivot Variables).

  5. Click the measure that you want to use.

    Now the expression is updated as follows:

    measures.test score=0

  6. Click the operator, which is an equals sign (=) by default.

    Now the editor displays the following set of operators:

    list of operators: =, not equals, less than, less than or equal, greater than, greater than or equal

    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:

measures.test score>=65

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

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

Deleting a Named Filter

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 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
    2 labels: Favorite color is blue, favorite color is green
    All patients whose favorite color is green and whose favorite color is blue (zero patients)
    2 labels: Allergies is mold, allergies is pollen
    All patients who are allergic to both mold and to pollen
    2 labels: Favorite color is green, allergies is ant bites
    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
    favorite color filter with label blue+green
    All patients whose favorite color is either green or blue
    allergies filter with label mold+pollen
    All patients who are allergic to mold, to pollen, or to both
    favorite color filter with label blue+green and allergies filter with label ant bites
    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 How the Business Intelligence Query Engine Works.

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 on the toolbar.

    The system displays the current MDX query, which looks something like this:

    SELECT from patients %Filter DocD.h1.doctor.&[32]

    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.

FeedbackOpens in a new tab