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.
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:
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.
The column displayed in the Column list is the one that you selected in step 1.
Click Browse next to Condition.
The Select Filter Values from the List dialog box is displayed.
In Select Filter Values from the List, select Misc, and then click OK.
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.
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:
In Name, enter the name for the filter that you are creating.
For this tutorial, enter All Children of Measures except Misc.
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.
The filter is added to the list of saved filters in the Select Data Filters dialog box.
Click Finish to apply the filter to the MEASURES.CHILD column, so that all children of Measures, except Misc, are included in the report.
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:
The Time.TRANSDATE column is sorted in descending order, displaying the transaction dates in reverse chronological order.
All children of Measures, Additions, COGS, Marketing, Payroll, Sales, and Opening Inventory, except Misc, are displayed as you specified in the filtering part of the Drill-Through Wizard.
Note: | When a filter is applied to a drill-through report, quotation marks are added to the column header. |