- 4 minutes to read
The group filtering capability provided by ASPxPivotGrid allows end-users to filter PivotGridControl data against groups of fields.
Field Filter Limitations
Field filters contain collections of filter values. Each of the filter values corresponds to a unique value stored in a PivotGridControl’s underlying data source. Depending on the filter type, the PivotGridControl either shows only those rows and columns that contain filter values in corresponding fields or hides those rows and columns. To learn more about field filters, see Filtering Basics.
A field filter has little effect when used to filter data against grouped fields. These fields are hierarchically arranged, while the field filter performs filtering based on 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 a PivotGridControl, whose Column Header Area contains two fields: ‘Year’ and ‘Month’.
Using a field filter, an end-user can display data for 1995 and 1996 only, or eliminate all winter sales from a report, but cannot hide records from December 1995 to February 1996 (without using a Filter Editor, which often requires building cumbersome criteria). The field filter can hide all ‘January’ columns, but it cannot hide an individual column corresponding to ‘January 1996’. A group filter is introduced to solve the problem.
Group Filtering Mechanism
A group filter filters data against values of grouped fields. It owns a collection of group filter values accessed via the PivotGroupFilterValues.Values property. Each of the group filter values corresponds to a field value from one of the grouped fields.
Similar to values of grouped fields, 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), etc.
Group filter values are used to define the data source records that should be processed by the PivotGridControl. A group filter value corresponds to a data source record if the following conditions are met.
- The data source record contains that group filter value in a respective field.
- The data source record contains ancestor values of that group filter value in respective fields.
- The group filter value does not contain child values.
If the PivotGroupFilterValues.FilterType property is set to PivotFilterType.Included, only the data source records that correspond to one of the group filter values will be processed. Otherwise, if the PivotGroupFilterValues.FilterType property is set to PivotFilterType.Excluded, only the data source records that do not correspond to any group filter value will be processed.
The image below shows a PivotGridControl with a filter drop-down invoked for the ‘Year - Quarter’ group. The group’s filter values collection contains a ‘1995’ value that has a ‘Quarter 3’ child value, and the filter type is set to PivotFilterType.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
- Specify the appropriate filter type via the PivotGroupFilterValues.FilterType property.
- Add the required filter values to the PivotGroupFilterValues.Values collection. To display records with blank values, provide a null filter value.
Before customizing a group filter, lock it via the PivotGroupFilterValues.BeginUpdate method. This prevents the filter condition from being applied during the customization process. When the required operations are completed, use the PivotGroupFilterValues.EndUpdate method to apply changes. If the group filter is unlocked when being customized, changes are applied immediately and PivotGridControl data is recalculated after each operation, which results in slow performance.
The group filter can be disabled using the PivotGridWebOptionsFilter.GroupFilterMode property. By default, this property is set to PivotGroupFilterMode.Tree. To disable the group filter, set the PivotGridWebOptionsFilter.GroupFilterMode property to PivotGroupFilterMode.List. In this instance, PivotGridControl data will be filtered against grouped fields using the standard field filter. You can also control the group filter availability for individual field groups using the PivotGridFieldOptions.GroupFilterMode property.
Regardless of the currently selected group filter mode, you can customize both group and field filters in code. 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 a group filter drop-down shown on the image below.