Filter

  • 5 minutes to read

In Excel-style mode, filters can be applied in the Filter Editor and Filter Panel at runtime or in code.

TIP

Demo: Excel Style Filtering module in the XtraPivotGrid MainDemo

Requires installation of WinForms Subscription. Download.

To enable the Excel-style mode, set the PivotGridOptionsFilterPopup.FieldFilterPopupMode option to FieldFilterPopupMode.Excel.

pivotGridControl.OptionsFilterPopup.FieldFilterPopupMode = FieldFilterPopupMode.Excel;

The Pivot Grid synchronizes filters applied in the field filter and filter Editor. You can customize the resulting filter sting or clear it to reset the applied filters. Classic pop-up filters do not synchronize their values with Prefilter values and display filters for a specific field only.

NOTE

Excel-style filters are not available in OLAP mode.

Filter Editor

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

xtrapivotgrid_prefilter

If PivotGridOptionsFilterPopup.FieldFilterPopupMode is set to classic, a Prefilter is available instead.

classic-prefilter

The PivotGridControl.DefaultFilterEditorView property specifies the Filter Editor’s display style.

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

TIP

Demo: Filtering module in the XtraPivotGrid MainDemo

Requires installation of WinForms Subscription. Download.

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.ShowInPrefilter property to hide a field and its criteria in the Filter Editor.

The table below lists the 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 pop-up filter 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

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

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

WinPivot_Filtering_FilterPanel

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

Use the PivotGridAppearances.PrefilterPanel property to customize the Prefilter Panel’s appearance.

In classic mode, the filter panel looks as follow:

pivotgrid_PrefilterPanel

The following options are available:

Option Description
Prefilter expression Displays active filters.
Edit Prefilter button Invokes a Filter Editor.
Enable Filter checkbox Enables/disables the current filter.
Clear Filter button Closes the panel and clears the filter.

Filtering in Code

Use the PivotGridControl.Prefilter property to access settings and specify filter criteria in code. The returned object exposes the following properties and methods:

API Description
Prefilter.Criteria, BaseFilter.CriteriaString Gets or sets a filter expression.
Prefilter.ChangePrefilterVisible Toggles the Filter Editor’s visibility.
BaseFilter.Enabled Gets or sets whether filtering is enabled.

When you create a filter expression in code, use the PivotGridFieldBase.PrefilterColumnName property to refer to a field.

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

using DevExpress.Data.Filtering;

//...

pivotgridControl1.Prefilter.CriteriaString = "[" + fieldCountry.PrefilterColumnName + 
                "] = 'UK' OR [" + fieldCountry.PrefilterColumnName + "] = 'USA'";

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" }));

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

NOTE

The Filter Editor cannot filter data fields. An error message is displayed in the Filter Panel if you use the name of a data field or a column which does not exist. When you change the field’s name in code, the criteria are not changed in the Filter Editor because they are considered invalid. Use the PrefilterBase.State property to get the filter’s state.

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

See Also