Filter Editor and Filter Panel

• Feb 25, 2022
• 4 minutes to read

You can filter data in the Filter Editor and Filter Panel at runtime or in code.

Run Demo: Excel Style Filtering

The Pivot Grid synchronizes filters applied in the pop-up filter and Filter Editor. You can customize the resulting filter sting or clear it to reset the applied filters.

Filter Editor

Use the Filter Editor dialog to apply complex filter conditions at runtime. Users can use the Show Filter Editor context menu command or Edit Filter button to invoke the Filter Editor window.

Show Filter Editor

The Show Filter Editor context menu command can be used if PivotGridOptionsFilterPopup.FieldFilterPopupMode is set to Excel.

Edit Filter

The Edit Filter button in the Filter Panel invokes the Filter Editor dialog.

The PivotGridControl.DefaultFilterEditorView property specifies the Filter Editor’s display style. The image displays the VisualAndText style:

To prevent users from invoking the Filter Editor, disable the PivotGridOptionsCustomization.AllowPrefilter option. This also disables the Show Filter context menu item.

Use the PivotGridFieldOptions.ShowInFilter property to hide a field and its criteria in the Filter Editor.

The list below shows the main members related to the Filter Editor.

ActiveFilter

Provides access to settings of the filter applied to the PivotGridControl‘s data.

ActiveFilterCriteria

Gets or sets the filter criteria applied to the PivotGridControl‘s data.

ActiveFilterString

Gets or sets a string that specifies the filter criteria applied to the PivotGridControl‘s data.

ActiveFilterEnabled

Gets or sets whether to apply the filter criteria specified using the PivotGridControl.ActiveFilterCriteria or PivotGridControl.ActiveFilterString.

ActiveFilterCriteriaChanged

Occurs when the filter criteria, currently applied to the PivotGridControl, is changed.

FieldFilterPopupMode

Gets or sets the field’s filter popup mode for all fields.

FieldFilterPopupMode

Gets or sets the field’s pop-up filter mode.

DefaultFilterEditorView

Gets or sets the how an end-user can edit criteria in the Filter Editor.

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.

To enable the Classic mode, set the PivotGridOptionsFilterPopup.FieldFilterPopupMode option to FieldFilterPopupMode.Classic.

Note that this mode does not synchronizes values applied in the pop-up filter and the Filter Editor.

Run Demo: Filtering

C#

pivotGridControl.OptionsFilterPopup.FieldFilterPopupMode = FieldFilterPopupMode.Classic;

VB.NET

pivotGridControl.OptionsFilterPopup.FieldFilterPopupMode = FieldFilterPopupMode.Classic

For more information how to use the Filter Editor, see the Filter Editor topic in the End-user Documentation.

Filter Panel

You can customize the resulting filter sting in the Filter Panel or clear it to reset the applied filters.

Enable the PivotGridOptionsCustomization.AllowFilter option and specify a filter condition to display the Filter Panel.

The following options are available:

Option	Description
Close Filter button	Closes the panel and clears the filter
Enable Filter checkbox	Enables/disables the current filter.
MRU Filter button	Displays MRU filter list.
Edit Filter button	Invokes the Filter Editor to build complex filter criteria.
MRU filter list	Displays the last filters applied to the Pivot Grid.

Note

The MRU list is not available if the filter criteria display style is Visual (the WindowsFormsSettings.FilterCriteriaDisplayStyle property value equals Visual).

Filtering in Code

The PivotGridControl contains the following properties that allows you to manage active filters:

ActiveFilterCriteria

Gets or sets the filter criteria applied to the PivotGridControl‘s data.

ActiveFilterString

Gets or sets a string that specifies the filter criteria applied to the PivotGridControl‘s data.

ActiveFilterEnabled

Gets or sets whether to apply the filter criteria specified using the PivotGridControl.ActiveFilterCriteria or PivotGridControl.ActiveFilterString.

The example below shows how to filter a Pivot Grid Control’s underlying data source. The filter selects records that contain ‘USA’ strings in the Country field and ‘Beverages’ in the Category field:

C#

using DevExpress.Data.Filtering;

//...

pivotgridControl1.ActiveCriteriaString = "[" + fieldCountry + 
                "] = 'UK' And [" + fieldCategory + "] = 'Beverages'";

VB.NET

Imports DevExpress.Data.Filtering

'...

PivotGridControl1.ActiveCriteriaString = "[" + FieldCountry + 
                "]== 'UK' And [" + fieldCategory + "] = 'Beverages'"

The example below shows how to apply complex filter criteria in code using the PivotGridControl.ActiveFilterCriteria property.

C#

using DevExpress.Data.Filtering;
// ...
            pivotGridControl.ActiveFilterCriteria = new GroupOperator(GroupOperatorType.And,
                new InOperator(fieldTrademark.Name, new string[] { "Chevrolet", "Chrysler", "Dodge", "Ford" }),
                new InOperator(fieldBodyStyle.Name, new string[] { "Coupe" }));

VB.NET

Imports DevExpress.Data.Filtering
' ...
            pivotGridControl.ActiveFilterCriteria = New GroupOperator(GroupOperatorType.And,
                New InOperator(fieldTrademark.Name, New String() {"Chevrolet", "Chrysler", "Dodge", "Ford"}),
                New InOperator(fieldBodyStyle.Name, New String() {"Coupe"}))

The PivotGridControl.ActiveFilterCriteriaChanged event rises when you change a filter condition in the Filter Editor or in code.

See the Pivot Grid Expression Syntax for more information about filter criteria syntax.

Limitations

• Excel-style filters are not available in OLAP mode.
• The Filter Editor cannot filter data fields.
• When you change the field’s name in code, the field’s name in the criterion is not changed. Such names are considered invalid. An error message is displayed in the Filter Panel if you use the name of a column which does not exist.