Lesson: Filtering Data

You can create and apply filters to determine what Integration Services retrieves for the drill-through report. You can also save, edit, and delete the filters that you create. For a column, you can retrieve only data that meets certain conditions. For example, the MEASURES.CHILD column in the sample database contains all children of the Measures dimension.

In the sample drill-through report, if you do not apply a filter to this list of measures, Integration Services retrieves all children from the relational source, because the sample drill-through report applies to all children of Measures. In this section, you apply a filter to the MEASURES.CHILD column so that all children of Measures, except Misc, are included in the report.

Note:

When you apply a filter on a non-level 0 member using Integration Services, the filter may return more members than expected. To work around this problem, use the Drill-Through Wizard.

  To define a filter:

  1. In Column, select MEASURES.CHILD.

    The columns in the Column list box are those that you selected in Lesson: Selecting and Ordering Columns.

    If a filter is attached to the column, it is displayed in the Condition column. The full string of the filter is displayed in the lower Condition box.

  2. With MEASURES.CHILD selected, click Add condition.

    The Set Filter on Column dialog box is displayed.

  3. From Column, select CHILD.

    The column displayed in the Column list is the one that you selected in step 1.

  4. From Operator, select < >, which represents not equal to.

    Note:

    You can select multiple values simultaneously only if you selected In or Not In as the filter operator.

  5. Click Browse Browse button next to Condition.

    The Select Filter Values from the List dialog box is displayed.

    Note:

    Integration Services retrieves these values directly from the relational data source. If the relational data source contains many values, Integration Services confirms if you want to view them all before it retrieves them from the data source.

  6. In Select Filter Values from the List, select Misc, and then click OK.

    The Set Filter On Column dialog box is displayed.

  7. In Set Filter On Column, click Add.

    The filter defined above causes all children of Measures, except Misc data, to show in the drill-through report.

    The Add button becomes unavailable after you create the first filter but becomes available when you create another filter. In this tutorial, you are creating only one filter. The And and Or options are used when combining multiple filters. The default value is Or—Integration Services applies the filter if one or more of the specified conditions are met. If you select And, Integration Services applies the filter only if all conditions are met.

  8. Click OK.

    Notice that the filter defined in the Set Filter on Column dialog box is displayed in the Condition column and the Condition box of the Select Data Filters dialog box.

    You can also create a filter by typing the filter conditions directly into the Filters box of the Set Filter on Column dialog box.

    To clear a filter for a selected column, select the filter and click Clear. To clear all filters for all columns, click Clear All.

    You can save the filter that you created and apply it to the MEASURES.CHILD column, so that all children of Measures, except Misc, are included in the report.

  To save the filter that you just created:

  1. In Select Data Filters, click Add new filter.

    The Filter Name dialog box is displayed.

  2. In Name, enter the name for the filter that you are creating.

    For this tutorial, enter All Children of Measures except Misc.

  3. Select Copy definition of current filter.

    Selecting Copy definition of current filter gives the filter the same description and conditions as the filter currently selected in the Select Data Filters dialog box.

  4. Click OK.

    The filter is added to the list of saved filters in the Select Data Filters dialog box.

  5. Click Save Filters.

  6. Click Finish to apply the filter to the MEASURES.CHILD column, so that all children of Measures, except Misc, are included in the report.

    Note:

    You can delete and rename filters.

    Integration Services generates the customized drill-through report and displays the results in a worksheet that is added to the workbook before the current worksheet.

In this sample, the customized drill-through report reflects the specifications that you set using the Drill-Through Wizard: