Group Filtering
- 4 minutes to read
The group filtering capability provided by the Pivot Grid allows end-users to filter the pivot grid’s data against groups of fields.
Field Filter Limitations
The field filter contains a collection of filter values. Each of the filter values corresponds to a unique value stored in the underlying data source in the current field. Depending on the filter type, the pivot grid shows only those rows and columns that contain the filter values in the corresponding fields, or hides those rows and columns. To learn more about field filters, see Filtering Basics.
The field filter has little effect when used to filter data against the grouped fields. These fields are hierarchically arranged, while the field filter performs filtering based on the values of individual fields, and does not take into account the field hierarchy. To learn more about groups, see Field Groups.
The image below shows the PivotGridControl, whose Column Header Area contains two fields: ‘Year’ and ‘Month’.
Using the field filter, an end-user can display data for 1995 and 1996 only, or eliminate all winter sales from the report, but not hide the records from December 1995 to February 1996 (unless using the Prefilter, which often requires to build cumbersome criteria). The field filter can hide all ‘January’ columns, but it cannot hide the individual column corresponding to ‘January 1996’. The group filter is introduced to solve the problem.
Group Filtering Mechanism
The group filter filters data against the values of grouped fields. It owns a collection of group filter values accessed via the PivotGroupFilterValues.Values property. Each of the filter values corresponds to a field value from one of the grouped fields.
Similarly to the values of grouped fields, the group filter values are hierarchically arranged. The PivotGroupFilterValues.Values collection contains values from the first field in the group (first-level values). Each of the first-level values holds a collection of values from the second field in the group (child values), and etc.
The group filter values are used to define which data source records should be processed by the pivot grid control. A group filter value corresponds to a data source record if the following conditions are met:
- The data source record contains the group filter value in the respective field.
- The data source record contains the group filter value’s ancestor values in the respective fields.
- The group filter value does not contain child values.
If the GroupFilterValues.FilterType property is set to FieldFilterType.Included, only the data source records that correspond to one of the filter values will be processed. Otherwise, if the GroupFilterValues.FilterType property is set to FieldFilterType.Excluded, only the data source records that do not correspond to any filter value will be processed.
The image below shows the PivotGridControl with the filter drop-down invoked for the ‘Year - Quarter’ group. The filter values collection contains a ‘1995’ value, which has the ‘Quarter 3’ child value, and the filter type is set to FieldFilterType.Excluded. As a result, the column that corresponds to the 3rd quarter of 1995 is hidden, while the ‘Quarter 3’ column for 1996 is displayed.
Using the Group Filter
The group filter condition is represented by the GroupFilterValues object stored in the group’s PivotGridGroup.FilterValues property. To apply a group filter in code, specify the appropriate filter type via the GroupFilterValues.FilterType property, and add the required filter values to the PivotGroupFilterValues.Values collection.
The group filter can be disabled using the PivotGridControl.GroupFilterMode property. By default, the property is set to GroupFilterMode.Tree. To disable the group filter, set the PivotGridControl.GroupFilterMode property to GroupFilterMode.List. In this instance, pivot grid data will be filtered against grouped fields using the standard field filter.
Regardless of the currently selected group filter mode, you can customize both group and field filters in code. The customizations made for the currently unused filter will take effect after switching the group filter mode.
End-users are able to apply, remove and change group filtering using the group filter drop-down, shown on the image below.
