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.
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.
- The Show Filter context menu command can be used if PivotGridOptionsFilterPopup.FieldFilterPopupMode is set to Excel.
- The Edit Filter button in the Filter Panel invokes the Filter Editor or PivotGrid Prefilter dialog (depends on the PivotGridOptionsFilterPopup.FieldFilterPopupMode value).
If PivotGridOptionsFilterPopup.FieldFilterPopupMode is set to classic, a Prefilter is available instead.
The PivotGridControl.DefaultFilterEditorView property specifies the Filter Editor’s display style.
Filter Editor - Text Filter Criteria Display Style
Filter Editor - Visual Filter Criteria 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.
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:
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.