Filter and Search

• Nov 01, 2023
• 13 minutes to read

When a filter is applied, the View displays only those records that meet the current filter criteria. You can filter data against one or multiple columns. If you filter against multiple columns, the grid applies the AND logical operator between column filters.

Most DevExpress data-aware components (Data Grid, Tree List, Vertical Grid, etc.) have a filter UI and API similar to those described in this topic.

Filter Drop-down Menus (Excel-style)

To invoke a filter drop-down menu for a column, click the filter icon within the column header. In the “Values” tab, users can select specific cell values from those that are displayed by the Data Grid.

Note

Values entered by users are parsed according to Find Panel syntax rules.

The “Filters” tab gives users a wider pool of filter options. For example, when a user filters data by date, you can show only those records that correspond to the previous week.

The content of a filter drop-down menu depends on the type of data displayed by the related grid column. For instance, the figure below illustrates what this menu looks like when data is filtered by a numeric column.

This type of filter drop-down menu is called “Excel-style”, and is the default filter menu type in v17.1 and higher.

Related API

• GridOptionsCustomization.AllowFilter — Disables the data filter feature for the entire Data Grid.
• OptionsColumnFilter.AllowFilter — Disables the filter feature for a specific column.
• ColumnView.ColumnFilterChanged — Occurs when a column’s filter condition changes. This event also raises when the Find Panel finishes its search.
• ColumnViewOptionsFilter.ColumnFilterPopupMode — If your DevExpress installation version is older than v17.1, switch this setting to Excel to enable Excel-style filter drop-down menus.
• ColumnView.OptionsFilter — Provides access to settings that affect filter menus for all columns. See ColumnViewOptionsFilter class members for the list of available settings.
• GridColumn.OptionsFilter — Provides access to filter settings of a specific column. See OptionsColumnFilter class members for the list of available settings.
• ColumnView.ShowFilterPopupExcel — Handle this event to customize Excel-style filters.

See the following topic to learn how to customize Excel-style menus dynamically: Advanced Filter and Search Concepts.

Classic Filter Drop-down Menus

The Data Grid can display classic filter menus instead of Excel-style drop-down menus. Classic filter menus include calendars for DateTime columns and regular drop-down menus for other types of columns.

Related API:

• ColumnViewOptionsFilter.ColumnFilterPopupMode — Set this property to Classic to enable classic filter drop-down menus in v17.1 and later. Classic menus are already enabled in older versions.
• WindowsFormsSettings.DefaultSettingsCompatibilityMode — An alternative property that allows you to enable classic filter drop-down menus. To enable classic filter menus, set this property to v16.
• ColumnViewOptionsFilter.ColumnFilterPopupRowCount — Gets or sets the maximum number of items that regular drop-down menus can display simultaneously.
• ColumnViewOptionsFilter.AllowColumnMRUFilterList — If this property is enabled, regular drop-down lists remember values most recently selected by users. These values are shown before the default “Custom” item.
• OptionsColumnFilter.ShowBlanksFilterItems — Gets or sets whether the “(Blanks)” and “(Non Blanks)” items are available within filter drop-down menus.
• ColumnView.ColumnFilterChanged - Occurs when a column’s filter condition changes. This event also raises when the Find Panel finishes its search.

You can also use checked list menus instead of regular drop-down menus.

Related API:

• OptionsColumnFilter.FilterPopupMode — Set the property to CheckedList to enable this filter menu style.
• ColumnViewOptionsFilter.ShowAllTableValuesInCheckedFilterPopup — Gets or sets whether checked filter drop-down lists display unique values from all data source records or only from those records that meet current filter criteria.
• ColumnView.ShowFilterPopupCheckedListBox — This event allows you to modify items within the checked list filter menu, including the standard “Select All” item.

You can also replace calendar filter menus with checked lists. Additionally, you can select between three types of calendar-based menus: DateSmart (default menu type), DateAlt, and Date. The figure below illustrates the DateAlt menu.

All these menus contain the same calendar that chooses a specific date but with different sets of date intervals. The table below lists several of these intervals.

DateSmart	Date	DateAlt
Check boxes are visible only if there are Data Grid records that fall into corresponding intervals. Beyond this year — Dates that follow the current year. Later this year — Dates of the current year starting with the following month. Later this month — Dates of the current month that occur after the following week. Next week — Dates that occur in the following week. Later this week — Dates of the current week starting with the day after tomorrow. Earlier this week — Dates of the current week that are prior to yesterday. Last week — Dates of the previous week. Earlier this year — Dates of the current year that are prior to the current month. Prior to this year — Dates that are prior to the current year.	Same time intervals as for the DateSmart type. All check boxes are available, even if there is no data that falls into the corresponding date range.	Beyond - Dates that belong to the 3rd month from the current month or beyond. Earlier - Dates that belong to the 7th month from the current month or earlier.

Related API:

• OptionsColumnFilter.FilterPopupMode — Set this property to Date, DateSmart, or DateAlt to choose the required calendar-based drop-down menu.
• OptionsColumnFilter.ImmediateUpdatePopupDateFilterOnCheck — If this setting is enabled, filters are applied immediately after users toggle calendar-based menu check boxes.
• OptionsColumnFilter.ImmediateUpdatePopupDateFilterOnDateChange — If this setting is enabled, a filter is applied immediately after a user selects a date (or a date range) within a calendar-based menu.
• OptionsColumnFilter.ShowEmptyDateFilter — Gets or sets whether drop-down menus display the “Show Empty” filter. Users can check this filter to view records that have no values in the corresponding DateTime column.

Automatic Filter Row

Watch Video

Another way to filter data at runtime is to utilize the Automatic Filter Row. This row is displayed above regular data rows and allows users to type in filter values (including ‘*’ and ‘%’ wildcards) and select filter operators (NotLike, Equals, Between, etc.).

Note that cell editors displayed in the Automatic Filter Row ignore their masks and allow users to enter any characters. The following code snippet demonstrates how to keep the numeric mask active for the Automatic Filter Row:

C#

private void GridView1_ShownEditor(object sender, System.EventArgs e) {
    GridView view = sender as GridView;
    TextEdit edit = view.ActiveEditor as TextEdit;
    if (edit != null && view.IsFilterRow(view.FocusedRowHandle))
        edit.Properties.Mask.MaskType = DevExpress.XtraEditors.Mask.MaskType.Numeric;
}

VB.NET

Private Sub GridView1_ShownEditor(ByVal sender As Object, ByVal e As System.EventArgs)
    Dim view As GridView = TryCast(sender, GridView)
    Dim edit As TextEdit = TryCast(view.ActiveEditor, TextEdit)
    If edit IsNot Nothing AndAlso view.IsFilterRow(view.FocusedRowHandle) Then
        edit.Properties.Mask.MaskType = DevExpress.XtraEditors.Mask.MaskType.Numeric
    End If
End Sub

Note that if an Auto Filter Row cell uses a RepositoryItemRichTextEdit in-place editor, users can select only the “Like” filter condition for this cell. Other conditions (“Starts with”, “Equals”, etc.) are not available for RTF values.

Related API:

• GridOptionsView.ShowAutoFilterRow — Manages the visibility of the Automatic Filter Row.
• OptionsColumnFilter.AllowAutoFilter — Allows you to exclude specific columns from the Automatic Filter Row without hiding the entire row.
• GridView.PopupMenuShowing — Allows you to hide or remove specific conditions.
• GridOptionsFilter.AllowAutoFilterConditionChange — Specifies whether the auto-filter row’s condition selector is visible. If this selector is disabled, entered values will be compared using the following operators: “Equals” (for CheckEdit, LookUpEdit, and ImageComboBoxEdit columns) or “Like” (for other columns).
• OptionsColumnFilter.AutoFilterCondition — Specifies the filter criteria operator for this column (“Equals”, “Like”, “Greater”, etc.).
• OptionsColumnFilter.ImmediateUpdateAutoFilter — If this setting is enabled, the Data Grid filters its records immediately after a user modifies the filter condition. Otherwise, the filter is not applied until a user presses the Enter key or moves focus to another cell.
• GridView.ShowAutoFilterConditionsMenu — Call this method from code to invoke the Automatic Filter Row popup menu for a specific column.
• GridView.GetAutoFilterValue, GridView.SetAutoFilterValue — Methods that allow you to retrieve the currently applied auto-filter row condition and apply a new one from code.

Filter Panel

When you apply a filter, the View shows a filter panel. Users can utilize this panel to modify and remove current filters.

Related API:

• ColumnViewOptionsView.ShowFilterPanelMode — Manages filter panel visibility.
• ColumnView.ActiveFilterEnabled — Allows you to toggle the current filter on or off.
• ColumnViewOptionsFilter.AllowMRUFilterList — Allows you to hide the button that invokes the list with recently applied filters.
• ColumnViewOptionsFilter.AllowFilterEditor — Controls “Edit Filter” button visibility.

Incremental Search

Users can focus a grid column and start typing. The Data Grid focuses and highlights the first matching record. To find other records, press the Ctrl+Up and Ctrl+Down hotkeys.

Related API:

• GridOptionsBehavior.AllowIncrementalSearch — Enables or disables incremental search.
• ColumnView.GetIncrementalText — Returns the text being searched during an incremental search.
• GridView.StartIncrementalSearch, GridView.StopIncrementalSearch — Call these methods from code to manually trigger and cancel incremental search.

Filter Editor

The Filter Editor dialog allows users to build complex filter criteria, combined from multiple expressions. To invoke this dialog, users can do one of the following:

• Click “Edit Filter” in the Filter Panel.
• Select “(Custom)” in a regular dropdown filter menu.
• Right-click a column header and select “Filter Editor”.

You can modify the ColumnViewOptionsFilter.DefaultFilterEditorView property to enable the Filter Editor’s “Text” panel/tab in which users can type filter expressions manually.

In v18.1 and later, the legacy Filter Editor is replaced with its updated version that features the advanced “Text” panel. This panel now supports syntax highlighting and auto-complete to facilitate the input. To revert data-aware controls back to the legacy Filter Editor version, disable the static WindowsFormsSettings.UseAdvancedFilterEditorControl property.

Related API:

• ColumnViewOptionsFilter.AllowFilterEditor — Gets or sets whether the Filter Editor is available.
• ColumnView.CustomFilterDialog — Handle this event to alter the default behavior when users click the “Custom” filter drop-down menu item. See the following topic to learn more: Advanced Filter and Search Concepts.
• ColumnView.FilterEditorCreated — This event fires when the Filter Editor is about to be shown. The event allows you to customize the Editor dialog or prevent it from being displayed.

Show example

The following code snippet allows users to filter the “Price” column data using the SpinEdit and CalcEdit editors.

C#

private void GridView1_FilterEditorCreated(object sender, DevExpress.XtraGrid.Views.Base.FilterControlEventArgs e) {
    e.FilterControl.BeforeShowValueEditor += FilterControl_BeforeShowValueEditor;
}
RepositoryItemSpinEdit reSpinEdit = new RepositoryItemSpinEdit();
RepositoryItemCalcEdit reCalcEdit = new RepositoryItemCalcEdit();
private void FilterControl_BeforeShowValueEditor(object sender, DevExpress.XtraEditors.Filtering.ShowValueEditorEventArgs e) {
    if (e.CurrentNode.FirstOperand.PropertyName != "Price") return;
    RepositoryItemTextEdit item = null;
    if (e.FocusedElementIndex == 2)
        item = reSpinEdit;
    else
        item = reCalcEdit;
    item.Mask.MaskType = DevExpress.XtraEditors.Mask.MaskType.Numeric;
    item.Mask.EditMask = "c";
    e.CustomRepositoryItem = item;
}

VB.NET

Private Sub GridView1_FilterEditorCreated(ByVal sender As Object, ByVal e As DevExpress.XtraGrid.Views.Base.FilterControlEventArgs)
    AddHandler e.FilterControl.BeforeShowValueEditor, AddressOf FilterControl_BeforeShowValueEditor
End Sub

Dim reSpinEdit As New RepositoryItemSpinEdit()
Dim reCalcEdit As New RepositoryItemCalcEdit()
Private Sub FilterControl_BeforeShowValueEditor(ByVal sender As Object, ByVal e As DevExpress.XtraEditors.Filtering.ShowValueEditorEventArgs)
    If e.CurrentNode.FirstOperand.PropertyName <> "Price" Then
        Return
    End If
    Dim item As RepositoryItemTextEdit = Nothing
    If e.FocusedElementIndex = 2 Then
        item = reSpinEdit
    Else
        item = reCalcEdit
    End If
    item.Mask.MaskType = DevExpress.XtraEditors.Mask.MaskType.Numeric
    item.Mask.EditMask = "c"
    e.CustomRepositoryItem = item
End Sub

• ColumnViewOptionsFilter.UseNewCustomFilterDialog — Enable this property to allow users to filter values of a GridColumn not only against a constant custom value, but also against values of other GridColumns.
• ColumnViewOptionsFilter.FilterEditorAggregateEditing — This property specifies whether users can create filters based on collection properties.

Filter Criteria Display Style

The following list demonstrates the available display styles of filter criteria in controls.

• Visual -

  Renders filter criteria in an easy-to-read format and uses skin-based colored highlights to differentiate between column names, functions, and values. In the Filter Panel, the ‘x’ button is displayed when you hover over a condition. This button allows users to remove individual conditions from the filter.

  

• Text -

  Renders filter criteria in a text-based format.

  

Related API:

• WindowsFormsSettings.FilterCriteriaDisplayStyle — Gets or sets the default display style of filter conditions in a control’s Filter Panel, MRU Filter List, and built-in Filter Editor.
• ColumnViewOptionsView.FilterCriteriaDisplayStyle — Gets or sets the display style of filter conditions in the Filter Panel and built-in Filter Editor.

Find Panel

The Find Panel allows users to apply a temporary filter that filters data across all Data Grid columns at once. To invoke this panel, press “Ctrl+F” at runtime. Type the text you need to search for, then press Enter or click “Find”.

Note that the Find Panel does not highlight cells with HTML text.

Related API:

• ColumnViewOptionsFind.AllowFindPanel — Manages the availability of the Find Panel.
• ColumnViewOptionsFind.Behavior — Gets or sets whether records that do not match the query are hidden.
• ColumnViewOptionsFind.ParserKind — If text entered into the Find Panel contains whitespace, the Panel splits this string into separate logical blocks. The ParserKind setting specifies how these blocks are combined into a search query.
• ColumnViewOptionsFind.Condition — Gets or sets the condition according to which the Data Grid searches for the string entered into the Find Panel.
• ColumnViewOptionsFind.FindMode — Gets or sets whether a user must press Enter or click the “Find” button to start a search, or it starts automatically after a certain delay (the ColumnViewOptionsFind.FindDelay property).
• ColumnViewOptionsFind.AllowFindInExpandedDetails — Gets or sets whether to search data in expanded detail views.

See the following topic to learn more: Find Panel.

Search in Column Headers

You can use the GridView.OptionsFilter.InHeaderSearchMode property to enable built-in search boxes within the View’s column headers. The search boxes are used to filter column data or to search in columns, depending on the mode you choose.

‘TextFilter’ Mode

Users can type text in the search boxes to filter against the columns.

‘TextSearch’ Mode

Users can type text in the search boxes to search for column data. The grid highlights cell text that matches the specified search string.

Use the following shortcuts to navigate between search results:

• Ctrl+Down Arrow or F3 — Navigates to the next search result.
• Ctrl+Up Arrow or SHIFT+F3 — Navigates to the previous search result.

The GridColumn.OptionsFilter.AllowInHeaderSearch property allows you to disable search boxes in individual column headers.

Note

The Find Panel and In-Header Search are mutually exclusive options. They are not intended to be used together.

Search Syntax

The following list demonstrates sample search expressions supported in column header search boxes:

• Spa — Finds records whose column values contain this string (in non-numeric columns).
• 10 — Finds records whose column values are equal to “10” (in numeric columns).
• =Spain or =22 — Finds records whose column values are equal to the specified value.

• <>40 — Finds records whose column values are not equal to the specified value.

  Other supported comparison operators include: <, >, <=, >=, and != (equivalent to <>).

• =USA,Spain,Germany or =USA;Spain;Germany — Finds records whose column values are equal to any of these listed values.
• [10:20] or [10..20] — Finds records whose column values belong to the specified inclusive range.

Text search is case-insensitive.

Related API

• GridView.ClearInHeaderSearchText — Clears search text (contents of column header search boxes) for all columns.
• GridView.HideInHeaderSearchTextBox — Hides the currently active column header search box.
• GridView.ShowInHeaderSearchTextBox — Activates a built-in header search box for a specific column.
• GridColumn.SearchText — Gets or sets the text of the column’s built-in header search box. This property is supported in Grid Views and Banded Grid Views.
• GridColumn.OptionsFilter.InHeaderSearchPrompt — Gets or sets a grayed out hint displayed in an empty header search box when the search box is activated.
• GridView.OptionsFilter.ShowInHeaderSearchTextMode — Gets or sets whether to display search text (GridColumn.SearchText) in column header tooltips.

See the following topic for more information: GridView.OptionsFilter.InHeaderSearchMode.

Cheat Sheets and Best Practices

DevExpress data-aware UI controls support a similar filter UI and API. Read the following quick-reference guides for detailed information:

Filter DevExpress Data-Aware Controls