Filtering Overview
- 8 minutes to read
Filtering allows you to display only specific records within the Pivot Grid. You can enable end-users to apply filtering to certain fields using filter popups or complex filter criteria using the Filter Editor.
This topic describes how to apply filtering to filter, column and row field values. For details on how to apply filtering to summary values displayed in the Data area, see Filtering by Summaries.
Filter Popup
The filter popup allows end-users to filter certain filter, column or row fields.
To apply filtering to a specific field, an end-user clicks the filter icon () within the column header.
The Pivot Grid supports two types of filter popups: Excel-style and Classic. Default type is Excel-style.
Example | Description | |
---|---|---|
Excel-style | The Excel-style filter popup’s content depends on the type of data the related field displays. In the “Values” tab, end-users can select specific field values from the Pivot Grid. The “Filters” tab supplies users with additional options related to the field type. For example, when filtering a string field, you can show only those records that begin with ‘C’: Filters applied using the Excel-style filter popup are displayed in the Filter Panel and can be changed in the Filter Editor dialog, which allows end-users to apply complex filter conditions. | |
Classic | The Classic filter popup displays unique values stored in a field’s underlying data source. It also displays a toolbar that allows end-users to control various filtering options. To learn more about the toolbar functionality, see Filter Button and Popup. |
To specify the filter popup type for all supported controls in the application, use the static option WindowsFormsSettings.ColumnFilterPopupMode.
To specify the filter popup type for all Pivot Grid fields, use the PivotGridOptionsFilterPopup.FieldFilterPopupMode property. The code below shows how to use Excel-style filter popups for all fields:
To specify the filter popup type for a certain field, use PivotGridFieldOptionsEx.FieldFilterPopupMode:
To hide filter buttons from all field headers, set the PivotGridOptionsCustomization.AllowFilter property to false. You can also do this for individual fields using their PivotGridFieldOptions.AllowFilter properties.
Note
Filter Panel and Filter Editor display ActiveFilter instead of PivotGridControl.Prefilter if the following conditions are met:
- the datasource type is not OLAP
- the field’s area is not DataArea (the PivotGridFieldBase.Area is not PivotArea.DataArea)
- no subscriptions for the PivotGridControl.CustomFilterPopupItems event
- the WindowsFormsSettings.ColumnFilterPopupMode static option is not set to the FieldFilterPopupMode.Classic value
- the PivotGridOptionsFilterPopup.FieldFilterPopupMode option is not set to the FieldFilterPopupMode.Classic value
The table below lists the main members related to filter popups.
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 remove and modify items within Excel-style filter popups, as well as add custom items that apply specific filtering conditions. |
PivotGridControl.FilterPopupExcelPrepareTemplate | Allows you to replace templates used by Excel-style filter popups and external editors generated using Filtering UI Context. |
PivotGridControl.FilterPopupExcelQueryFilterCriteria | Allows you to customize a filter criteria applied using Excel-style filter popups. |
PivotGridControl.ShowFilterPopupExcel | Allows you to customize the Excel-style filter popup (for instance, to hide specific filtering 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. |
Filter Editor
The Pivot Grid allows end-users to apply complex filter conditions at runtime using the Filter Editor dialog. End-users can invoke a Filter Editor window in the following manner:
- Show Filter context menu command if the PivotGridOptionsFilterPopup.FieldFilterPopupMode is Excel (default value). Invokes the Filter Editor dialog.
- Show Prefilter context menu command if the PivotGridOptionsFilterPopup.FieldFilterPopupMode is Classic . Invokes the PivotGrid Prefilter dialog.
- Edit Filter button in the Filter Panel invokes the Filter Editor or PivotGrid Prefilter dialog depending on the PivotGridOptionsFilterPopup.FieldFilterPopupMode value.
Tip
Use the static property WindowsFormsSettings.ColumnFilterPopupMode to change default display mode of filter dropdowns in all Pivot Grid Controls, Data Grid Controls and Tree Lists in the current application.
The FilterEditorControl editor used in the filter dialog can display a Text panel, Visual panel or both, depending on the PivotGridControl.DefaultFilterEditorView property value.
The Filter Panel at the bottom of the Pivot Grid displays the resulting filter sting and provides additional capabilities to work with the applied filter (such as toggling or resetting filtering). Note that filtering applied to specific fields can be changed later in the Excel filter popup.
You can prevent end-users from invoking a Filter Editor by disabling the PivotGridOptionsCustomization.AllowPrefilter option. In this instance, the Show Prefilter context menu item is hidden, and the Filter Editor can only be invoked in code.
The table below lists main members related to the Filter Editor.
API | Description |
---|---|
PivotGridOptionsCustomization.AllowPrefilter | Gets or sets whether end-users are allowed to invoke the Prefilter. |
PivotGridFieldOptions.ShowInPrefilter | Gets or sets whether the field is shown in Prefilter. |
PivotGridControl.ActiveFilter | Provides access to settings of the filter applied to the PivotGridControl‘s data. |
PivotGridControl.ActiveFilterCriteria | Gets or sets the filter criteria applied to the PivotGridControl‘s data. |
PivotGridControl.ActiveFilterString | Gets or sets a string that specifies the filter criteria applied to the PivotGridControl‘s data. |
PivotGridControl.ActiveFilterEnabled | Gets or sets whether to apply the filter criteria specified using the PivotGridControl.ActiveFilterCriteria or PivotGridControl.ActiveFilterString. |
PivotGridControl.PrefilterCriteriaChanged | Fires when the Prefilter’s criteria are changed. |
PivotGridOptionsFilterPopup.FieldFilterPopupMode | Gets or sets the field’s filter popup mode for all fields. |
PivotGridFieldOptionsEx.FieldFilterPopupMode | Gets or sets the field’s filter popup mode. |
PivotGridControl.DefaultFilterEditorView | Gets or sets the how an end-user can edit criteria in the Filter Editor. |
WindowsFormsSettings.UseAdvancedFilterEditorControl | Specifies whether WinForms data-aware controls should use the advanced Filter Editor Control that features a single-tab interface and enhanced text criteria builder. |
Filter Panel
The Filter Panel is displayed at the bottom of the Pivot Grid and provides additional capabilities to work with the applied filter.
The following capabilities are available for end-users:
- A cross button to remove filtering.
- A checkbox to toggle filtering.
- The Edit Filter button which invokes the Filter Editor for changing the filter criteria.
Filtering UI Context
The Pivot Grid allows you to use the Filtering UI Context that produces a set of editors end-users can utilize to filter displayed data. Filters applied using these editors are synchronized with filters applied using the Excel-style filter popup. You can see the Excel Style Filtering demo to see a sample filtering UI generated for the Pivot Grid.
Refer to the Filtering UI Context for more information.
Filtering in Code
The Pivot Grid provides an API that allows you to apply complex filter criteria or filter individual fields.
Create Complex Filter Criteria
Use the PivotGridControl.ActiveFilterCriteria or PivotGridControl.ActiveFilterString properties to apply complex filtering in code. When you create a filter expression in code, use the PivotGridFieldBase.PrefilterColumnName property to refer to a field.
The example below shows how to apply complex filter criteria in code using the PivotGridControl.ActiveFilterCriteria property.
using DevExpress.Data.Filtering; // ... pivotGridControl.ActiveFilterCriteria = new GroupOperator(GroupOperatorType.And, new InOperator(fieldTrademark.PrefilterColumnName, new string[] { "Chevrolet", "Chrysler", "Dodge", "Ford" }), new InOperator(fieldBodyStyle.PrefilterColumnName, new string[] { "Coupe" }));
See the Pivot Grid Expression Syntax to learn more about filter criteria syntax.
Note
You cannot apply complex filtering to data fields. If a data field is used in a filter criterion that has been specified in code, an error message is displayed within the Filter Panel. This message also appears if a filter criterion contains a field name that does not exist. Use the BaseFilter.IsValid property to determine whether the filter criteria is valid or not.
Apply Filtering to Individual Fields
The example below shows how to apply filtering to the ‘fieldTradeMark’ field by adding ‘Chevrolet’ or ‘Chrysler’ to the PivotGridFieldBase.FilterValues collection.