Pop-up Filter
- 7 minutes to read
Use the pop-up filter to filter fields in column, row, and filter header areas.
Click the filter icon (
) in the column header to filter a specific field.
The Pivot Grid supports Excel-style and Classic pop-up filters.
To specify the filter type for all the controls in the application, use the static WindowsFormsSettings.ColumnFilterPopupMode option.
To specify the filter type for all Pivot Grid fields, set the PivotGridOptionsFilterPopup.FieldFilterPopupMode option to FieldFilterPopupMode.Excel.
To specify a field‘s filter type, use PivotGridFieldOptionsEx.FieldFilterPopupMode:
Note
You cannot use Excel-style filters in OLAP mode.
Example | Description | |
|---|---|---|
Excel-style |
| A filter’s content depends on the type of data in the related field. Select field values in the “Values” tab and specify additional options related to the field type in the “Filters” tab. For example, when you filter a string field, you can show only those records that begin with ‘C’:
In Asynchronous Mode, Excel-style pop-up filters apply a filter only when you click Ok and close the popup. Filters applied in the Excel-style pop-up filter are displayed in the Filter Panel and can be changed in the Filter Editor. This allows you to apply complex filter criteria. |
Classic |
| The Classic pop-up filter displays unique values from a field’s underlying data source. Use buttons on toolbar to control filtering options.
See Filter Button and Popup for more information about toolbar functionality. |
To hide filter buttons in all field headers, set the PivotGridOptionsCustomization.AllowFilter property to false, or use a field’s PivotGridFieldOptions.AllowFilter property for an individual field.
For more that one field in a column / row area, you can simulate group filter behavior and hide filter values that are hidden by the another filters. Use the following properties for corresponding pop-up filter’s mode:
| Pop-up Filter Mode | Property |
|---|---|
| Excel-style | PivotGridFieldOptionsFilter.PopupExcelFilterShowAllValues |
| Classic | PivotGridOptionsFilterBase.ShowOnlyAvailableItems |
Filter Popup API
The following tables list the main members related to pop-up filters:
| Common Popup API | Description |
|---|---|
| WindowsFormsSettings.ColumnFilterPopupMode | Gets or sets the default display mode of column filter dropdowns in all GridControls, TreeLists and PivotGridControls in the current application. |
| PivotGridOptionsFilterPopup.FieldFilterPopupMode | Gets or sets the field’s filter popup mode for all fields. |
| PivotGridFieldOptions.AllowFilter | Gets or sets whether an end-user can apply a filter to the current field. |
| PivotGridOptionsCustomization.AllowFilter | Gets or sets whether filter buttons are displayed within field headers. |
| PivotGridFieldOptions.AllowFilterBySummary | Gets or sets whether end-users are allowed to filter pivot grid data by summaries calculated against the current data field. |
| PivotGridOptionsCustomization.AllowFilterInCustomizationForm | Gets or sets whether filtering can be applied to fields via the Customization Form. |
| PivotGridAppearances.HeaderFilterButton | Gets the appearance settings used to paint filter buttons. |
| PivotGridAppearances.HeaderFilterButtonActive | Gets the appearance settings used to paint the filter buttons displayed within the field headers that are involved in filtering. |
| PivotGridOptionsView.HeaderFilterButtonShowMode | Gets or sets how filter buttons are rendered. |
| PivotGridControl.FieldFilterChanging | Allows you to customize the filter that is being applied or cancel filtering. |
| PivotGridControl.FieldFilterChanged | Occurs after a specific field’s filter criteria was changed. |
| Excel-style Popup API | Description |
|---|---|
| PivotGridField.ShowExcelFilterPopup | Invokes the Excel-style filter popup for the current field. |
| PivotGridField.OptionsFilter | Provides access to the field’s filtering options. |
| PivotGridControl.FilterPopupExcelCustomizeTemplate | Allows you to customize templates used by Excel-style filter popups and external editors generated using Filtering UI Context. |
| PivotGridControl.FilterPopupExcelData | Allows you to add, remove, and modify data values and customize predefined filters in the Excel style pop-up filter menus. Filter items added manually on this event must be unique and sorted. |
| PivotGridControl.FilterPopupExcelPrepareTemplate | Allows you to replace templates used by Excel-style filter popups and external editors generated using Filtering UI Context. |
| PivotGridControl.FilterPopupExcelQueryFilterCriteria | Fires when a filter criteria is about to be applied to data and allows you to customize the filter criteria. |
| PivotGridControl.ShowFilterPopupExcel | Allows you to customize the Excel-style filter popup (for instance, to hide specific filter conditions from the Filters tab). |
| Classic Popup API | Description |
|---|---|
| PivotGridField.ShowFilterPopup | Invokes the Classic filter popup for the current field. |
| PivotGridControl.OptionsFilterPopup | Provides access to options that define the Classic filter popup’s appearance and behavior. |
| PivotGridFieldOptions.GroupFilterMode | Gets or sets the filtering mode used for the field’s owner group. |
| PivotGridFieldOptionsEx.IsFilterRadioMode | Gets or sets whether an end-user is allowed to select only a single item in the filter drop-down. |
| PivotGridField.CanFilterRadioMode | Gets whether end-users are allowed to select only a single item in the field’s filter drop-down. |
| PivotGridField.DropDownFilterListSize | Gets or sets the width and height of the field’s filter dropdown. |
| PivotGridControl.CustomFilterPopupItems | Allows you to customize items displayed in the Classic filter popup. |
| PivotGridControl.GroupFilterChanged | Occurs after a group filter condition was changed. |
| PivotFieldDisplayTextEventArgs.IsPopulatingFilterDropdown | Gets whether the current event is called to populate the filter dropdown. |
| PivotGridOptionsFilterBase.ShowOnlyAvailableItems | Gets or sets whether filter items that cannot be displayed because of filtering applied to other fields should be hidden. |
Filter UI Context
You can use editors to filter data in the Pivot Grid. Filter values from editors are synchronized with filter values from the Excel-style pop-up filter. See an example of a UI filter for the Pivot Grid in the Excel-style Filtering demo.
Refer to the Filtering UI Context for more information.
Example
The example below shows how to apply filtering to the ‘fieldTradeMark’ field by adding ‘Chevrolet’ or ‘Chrysler’ to the PivotGridFieldBase.FilterValues collection.
pivotGridControl.BeginUpdate();
try {
fieldTrademark.FilterValues.Clear();
fieldTrademark.FilterValues.FilterType = PivotFilterType.Included;
fieldTrademark.FilterValues.Add("Chevrolet");
fieldTrademark.FilterValues.Add("Chrysler");
}
finally {
pivotGridControl.EndUpdate();
}
Group Filter
Use a group filter to filter values in grouped fields.
Each filter value corresponds to a unique value stored in a Pivot Grid Control’s underlying data source. The field filter filters values of individual fields and the group filter filters hierarchically arranged fields. The image below shows a PivotGridControl with a Column Header Area that contains ‘Year’, ‘Month’, and ‘Day’ fields. These fields use Excel-style pop-up filters (PivotGridOptionsFilterPopup.FieldFilterPopupMode is FieldFilterPopupMode.Excel).
Group Filter Mechanism
Use the PivotGroupFilterValues.Values property to access a collection of group filter values. Each group filter value corresponds to a field value from a grouped field.
Group filter values are arranged hierarchically. The PivotGroupFilterValues.Values collection contains values from the first field in the group (first-level values). Each first-level value corresponds to a collection of values from the second field in the group (child values), etc.
Use group filter values to define the data source records that the PivotGridControl should process. Group filter values correspond to data source records in the following cases:
- The data source record contains the group filter value in a corresponding field.
- The data source record contains ancestor values of the group filter value in corresponding fields.
- The group filter value does not contain child values.
Set the PivotGroupFilterValues.FilterType property to PivotFilterType.Included, to process data source records that correspond to a group filter value. The PivotFilterType.Excluded value is used to process data source records that do not correspond to any group filter value.
The image below shows a drop-down filter with the ‘Year - Quarter - Day’ group. The group’s filter values collection contains a ‘2018’ value that has ‘Quarter 3’ and ‘Quarter 4’ child values, and the filter type is set to PivotFilterType.Excluded. As a result, the ‘Quarter 3’ column for 2019 is displayed, and the column that corresponds to the 3rd and the 4th quarters of 2018 is hidden.
Use Group Filter in Code
Use the PivotGridGroup.FilterValues property to access group filter conditions in the PivotGroupFilterValues object. To apply a group filter in code, do the following:
- Use the PivotGroupFilterValues.FilterType property to specify the filter type.
- Add filter values to the PivotGroupFilterValues.Values collection. To display records with blank values, use null as the filter value.
Wrap the code in the PivotGridControl.BeginUpdate and PivotGridControl.EndUpdate method calls to avoid unnecessary control updates when you customize filter properties.
You can use the PivotGridFieldOptions.GroupFilterMode property to specify the pop-up filter’s availability for individual field in a group. The default Tree mode enables the group filter. Set the PivotGridFieldOptions.GroupFilterMode property to List to enable the field filter. In List mode, you can simulate group filter behavior and hide filter values that are hidden by the another filters. Use the following properties for corresponding pop-up filter’s mode:
| Pop-up Filter Mode | Property |
|---|---|
| Excel-style | PivotGridFieldOptionsFilter.PopupExcelFilterShowAllValues |
| Classic | PivotGridOptionsFilterBase.ShowOnlyAvailableItems |
End users can use a group drop-down filter to apply, remove, and change group filters.
| Excel-style | Classic |
|---|---|
 |
 |