Skip to main content
A newer version of this page is available. .

Group Filtering

  • 4 minutes to read

The group filtering capability provided by PivotGridControl allows end-users to filter Pivot Grid Control 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 Pivot Grid Control’s underlying data source. Depending on the filter type, the Pivot Grid Control 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 Overview.

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 three fields: ‘Year’, ‘Month’ and ‘Day’. The filter popup is Excel-style (PivotGridOptionsFilterPopup.FieldFilterPopupMode is FieldFilterPopupMode.Excel).

xtrapivotgrid_FieldFilterWithGroup

A group filter is used to filter data against all three columns with ease.

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 Pivot Grid Control 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 1994 is displayed.

xtrapivotgrid_GroupFilter

Using the Group Filter

A group filter condition is represented by a PivotGroupFilterValues object stored in the group’s PivotGridGroup.FilterValues property. To apply a group filter in code, do the following:

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 Pivot Grid Control data is recalculated after each operation, which results in slow performance.

The group filter can be disabled using the PivotGridOptionsFilterBase.GroupFilterMode property. By default, this property is set to PivotGroupFilterMode.Tree. To disable the group filter, set the PivotGridOptionsFilterBase.GroupFilterMode property to PivotGroupFilterMode.List. In this instance, Pivot Grid Control 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.

Excel-style Classic
pivotgrid_GroupFilterMode_ExcelGroupFilterDropdown pivotgrid_GroupFilterMode_GroupFilterDropdown

Example

How to: Implement Group Filter for a PivotGrid