Skip to main content
BuyLog In
WinForms Controls
Search in…
Docs > Controls and Libraries > Pivot Grid > Data Shaping > Filtering > Filtering Overview
A newer version of this page is available. .
All docs
V18.2
WinForms Controls
Prerequisites
What's Installed
Build an Application
Controls and Libraries
Forms and User Controls
Messages, Notifications, and Dialogs
Editors and Simple Controls
Ribbon, Bars and Menu
Application UI Manager
Docking Library
Data Grid
Vertical Grid
Property Grid
Pivot Grid
Getting Started
Fundamentals
Binding to Data
Data Shaping
Summarization
Grouping
Sorting
Filtering
Filtering Overview
Group Filtering
Prefilter
Filtering by Summaries
OLAP Filtering Specifics
Editing
Data Formatting
Data Analysis
Layout
Focus and Navigation
Printing and Exporting
Appearance
Design-Time Features
Miscellaneous
UI Elements
End-User Capabilities
Examples
Member Tables
Tree List
Chart Control
Diagrams
Gauges
Map Control
Scheduler
Spreadsheet
Rich Text Editor
Spell Checker
Form Layout Managers
Navigation Controls
Printing-Exporting
PDF Viewer
Reporting
Snap
TreeMap
Sunburst Control
Common Features
Get More Help
API Reference
Download CHM

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 (Pivot_FilterIcon) 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

FilterPopup_Excel

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’:

WinPivot_Filtering_Excel_TextFilters

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.

Note

The Excel-style cannot be used in the following cases:

  • to apply filtering in OLAP mode.

Classic

FilterPopup_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.

VisualElements_FilterDropdown_Toolbar

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:


pivotGridControl.OptionsFilterPopup.FieldFilterPopupMode = FieldFilterPopupMode.Excel;

To specify the filter popup type for a certain field, use PivotGridFieldOptionsEx.FieldFilterPopupMode:


fieldTrademark.Options.FieldFilterPopupMode = FieldFilterPopupMode.Excel;

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 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:

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.

WinPivot_Filtering_FilterEditor_Main

The FilterEditorControl editor used in the filter dialog can display a Text panel, Visual panel or both, depending on the PivotGridControl.DefaultFilterEditorView property value.

WinPivot_Filtering_FilterEditorDialog

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.

WinPivot_Filtering_FilterPanel

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.

    pivotGridControl.BeginUpdate();
try {
    fieldTrademark.FilterValues.Clear();
    fieldTrademark.FilterValues.FilterType = PivotFilterType.Included;
    fieldTrademark.FilterValues.Add("Chevrolet");
    fieldTrademark.FilterValues.Add("Chrysler");
}
finally {
    pivotGridControl.EndUpdate();
}