Cascading Filters

The Cascading Filters page allows the system administrator to create and configure cascading filters for use when building reports with the Report Builder. The term cascading filter refers to a series of user input drop-down menus where the value selected in the first drop-down affects the values displayed in the second drop-down and so forth. Cascading filters have no default values but instead rely on user input at runtime.

Select Cascading Filters from the Report Configuration drop-down list to display the Cascading Filters configuration page.

Click on the icon to display brief help for the Cascading Filters page.

The Add button will display an empty Cascade Filter page.

The Delete button will remove the selected cascading filters. Cascading filters are selected by clicking on the applicable checkbox.

The icon indicates that only one action can be performed on the Cascading Filter. Click on the icon to modify the cascading filter information.

Each cascading filter is comprised of one or more filter items. One filter item translates to one drop-down menu. Administrators must configure the following attributes for each filter item:

Data Object - the data object used to populate the drop-down menu
Filter Column - the column that is filtered by the value selected from the previous filter item
Value Column - the column that provides the value for the next filter item
Display Column - the column that provides the values for the drop-down menu

Note:

The first filter item always contains a blank value for the Filter Column.

Cascading filters are used as parameters in reports. From the Report Builder, end users must select a column to filter and then choose In cascading list as the operator. A list of all the cascading filters present is displayed and the end user must choose one.

Note:

The In cascading list and Not in cascading list options are only presented to the end user creating a report when the column selected to be filtered matches the last Value Column in the cascading filter definition.

Adding a new Cascading Filter

Click on the Add button to define a new cascading filter. A blank Cascading Filter page will be displayed.

Click on the icon to display brief help for the Cascading Filter page.

Enter a Name and Description (optional) for the cascading filter.

Click on the Add button to create a filter item. If this is the first filter item, the following dialog will be presented:

If this is an additional filter item, the following dialog will be presented:

The difference between the two dialogs is the Filter Column. Since there are no filters applied to the first list of a cascading filter, this option is not presented for the first cascading filter list.

Repeat the process of adding filter items until the cascading filter definition is complete.

Filter items may be removed by selecting the filter item with the checkboxes and clicking on the Delete button. The definition of the filter item may be modified by clicking on the icon.

Click on the Test Filter button to exercise the filter items and verify that the lists are properly populated. A preview page will be displayed similar to the following:

Note:

The cascading filter test will only exercise the filter. It does not accurately reflect the presentation of the cascading filter in a report.

Click on the Save button to store the cascading filter definition in the metadata database.

As an example, the following cascading filter definition was the basis for the Test Filter description above:

The first filter list is based on the Country column of the Customers data object. When the cascading filter is presented, the first drop-down list will be populated with a list of the distinct countries found in the Customers data object. The list is unfiltered. The caption for the list will be Country , the friendly name of that column. When the user selects one of the countries, the data in the Country Value Column will be passed to the next filter item.

The second filter list is based on the City column of the Customers data object. When this list is presented, the contents will be all of the distinct cities in the Customers table, filtered by the Country value selected from the previous list. The caption for the list will be City , the friendly name for that column. When the user selects a City , the data in the City Value Column will be passed to the next filter item.

The last filter item is based on the Customer ID of the Customers data object. When this list is presented, the contents will be a list of Company Names filtered by the City value selected from the previous list. When the user selects a Company Name , the corresponding Customer ID value will be used to filter the data in the report.

The In Cascade List option will be presented to the user building the report when the column that the parameter is based on is the Customer ID column.

Notes:

1) The Value Column and Display Column may be different

2) The cascading filter may be based on multiple, related data objects

3) Only the last filter item is used to filter the report. If the last filter Value Column was City and the user selected Springfield , all records with a City of Springfield will be selected. The previous filter items are not considered.