[Expand]General Information
[Collapse]WinForms Controls
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Collapse]Data Grid
   [Expand]Getting Started With Data Grid and Views
   [Expand]Data Binding
    Unbound Columns
   [Expand]Data Editing and Validation
   [Collapse]Filter and Search
     Advanced Filter and Search Concepts
   [Expand]Focus and Selection Handling
    Formatting Cell Values
   [Expand]Master-Detail Relationships
   [Expand]Asynchronous Image Load
   [Expand]Export and Printing
   [Expand]Appearance and Conditional Formatting
    Split Presentation
    Row Preview Sections
   [Expand]Batch Modifications
   [Expand]Hit Information
    Popup Menus
   [Expand]Saving and Restoring Layouts
   [Expand]Visual Elements
   [Expand]Design-Time Features
   [Expand]End-User Capabilities
    Included Components
  [Expand]Vertical Grid
  [Expand]Property Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Map Control
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]PDF Viewer
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Report and Dashboard Server
[Expand]eXpressApp Framework
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Advanced Filter and Search Concepts

Expanded Filter Expressions

A filter expression is a formula (or a set of formulas) that specifies how data should be filtered. Each expression contains three parts:

  • a data field whose values should be filtered;
  • a filtering value that should be compared to records stored in the data field;
  • an operator that compares data field values with a filtering value.

For example, the following expression selects all the "Count" data field values that are greater than 5 but less than 20:

[Count] Between ('5', '20')

Expanded Filter Expression Syntax

The table below enumerates most frequently used operators. To learn more, see the Criteria Language Syntax article.

= Equals. Selects data field values that equal the entered filtering value. [OrderDate] = #2016-01-01#
<> Does not equal. Selects data field values that are not equal to the entered filtering value. [OrderDate] <> #2016-01-01#
> Is greater than. Selects data field values that are greater than the entered filtering value. [OrderDate] > #2016-01-01#
>= Is greater than or equal to. Selects data field values that are greater than the entered filtering value or equal to it. [OrderDate] >= #2016-01-01#
<= Is less than or equal to. Selects data field values that are less than the entered filtering value or equal to it. [OrderDate] >= #2016-01-01#
< Is less than. Selects data field values that are less than the entered filtering value. [OrderDate] >= #2016-01-01#
Between Is between. Selects data field values that belong to the specific value interval. [CustomerID] Between ('1', '100')
Not Between Is not between. Selects data field values that lie outside the specific value interval. Not [CustomerID] Between ('1', '100')
Contains Contains. Selects data field values that contain the filtering value. Contains([ShipCountry], 'land')
Not Contains Does not contain. Selects data field values that do not contain the filtering value. Not Contains([ShipCountry], 'land')
Starts with Begins with. Selects data field values that start with the filtering value. StartsWith([ShipCountry], 'G')
Ends with Ends with. Selects data field values that ends with the filtering value. Ends with([ShipCountry], 'ia')
In Is any of. Selects data field values that equal any of the entered filtering values. [ShipCountry] In ('Germany', 'Italy', 'USA')
Not In Is none of. Selects data field values that do not equal any of the entered filtering values. Not [ShipCountry] In ('Germany', 'Italy', 'USA')
Like Is like. Selects data field values that contain the filtering value.
Accepts wildcards: '_' to replace a single character, '%' to replace any number of characters.
[OrderDate] Like '%2011'
same as
Contains ([OrderDate], '2011')
Not Like Is not like. Selects data field values that do not contain the filtering value.
Accepts wildcards: '_' to replace a single character, '%' to replace any number of characters.
Not [OrderID] Like '103__'
same as
Not [OrderID] Between ('10300', '10399')
Is Null Is null. Selects null values. [ShipRegion] Is Null
Is Not Null Is not null. Excludes null values. [ShipRegion] Is Not Null
  • String values must be enclosed within single quote characters. If a single quote character needs to be included as a literal to a filter, it must be doubled (e.g., [ProductID] LIKE 'Uncle Bob''s%');

  • Date-time values must be wrapped with the '#' characters and represented using a culture-independent (invariant) format. The invariant culture is based on the English culture, but some of the idiosyncratic English formats have been replaced by more globally-accepted formats. Below are some of the culture-independent formats for representing date-time values.

    MM/dd/yyyy — 07/30/2008
    dd MMM yyyy — 30 JUL 2008
    yyyy-MM-dd — 2008-07-30
    yyyy-MM-ddTHH:mm:ss — 2008-07-30T22:59:59
    yyyy-MM-dd HH:mm:ssZ — 2008-07-30 15:59:59Z

Expanded Filtering Data in Code

ColumnView.ActiveFilterString and ColumnView.ActiveFilter
Allow you to pass a filtering expression as a string in order to apply data filtering to the entire View. The following sample applies filtering by the "Order Date" and "Ship Region" fields.

Allows you to filter data by specific columns. The FilterInfo property accepts objects of the ColumnFilterInfo type.
A ColumnFilterInfo object can hold any valid filter expression, including an expression that filters another column's data.

ViewFilter.NonColumnFilter (accessed through the ColumnView.ActiveFilter property)
Utilize this property to apply a filter expression that is not associated with any specific grid column. As a result, end users cannot use the column's drop-down filter to clear or modify this filter.

Allows you to hide particular rows that exist in the data source or make them visible (regardless of the grid's filter).

Expanded Override Current Grid Filters

Handle the ColumnView.CustomRowFilter event to manually show or hide Data Grid rows, even if they do not match active Grid filters. The following sample makes rows with the “USA” value in the “Country” column always visible:

Expanded Filter Modes: Display Text vs Value

The figure below illustrates a classic drop-down menu invoked for a DateTime "Order Date" column. This column has a custom 'yyyy' display format, which leaves only years visible. However, these dates are still unique and since the Data Grid filters data by values by default, the pop-up filter menu displays an item for each date. From end-users’ perspective, items are duplicated multiple time (left figure). Utilize the GridColumn.FilterMode property to switch this filter mode to DisplayText. In this mode, the Data Grid filter menus are populated with records’ display text strings (right figure).

An automatic filtering row has its own dedicated property for changing the filtering mode: WindowsFormsSettings.ColumnAutoFilterMode.

Expanded Customize Filtering Dropdown Menus

ColumnView.ShowFilterPopupExcel, ExcelFilterOptions
Allow you to customize Excel-style menus. The sample below demonstrates how to remove the "Is Null" and "Is Not Null" items.

Allows you to modify and remove items within Excel-styled filter dropdowns, as well as add custom items that apply specific filtering conditions. The code snippet below adds custom filtering options to the "Modification" and "MPG City" columns.

Regular filter menus display the "(Custom)" item. Clicking this item invokes a Filter Dialog that allows end-users to build filtering criteria from multiple simple expressions. The CustomFilterDialog event allows you to customize this dialog or change the "(Custom)" item's action. The code sample below suppresses the Filter Dialog when end users click "(Custom)" for the "Unit Price" column. Instead, clicking this item applies a $10 ~ $30 price filter.

Handle this event to modify regular drop-down list menus. The sample below demonstrates how to add the "Important" item to a filtering menu invoked for the "Priority" column. Applying this custom filter selects records with Medium and High priorities.

Allows you to customize checked list box menus. The code below removes items that start with "Help" and disables items that start with "Data".

Allows you to customize calendar filters. The code sample below adds a custom "Last Year" filtering item to calendar menus.

Expanded Automatic Filtering Row API

Specifies whether or not the auto-filtering row's condition selector is visible.

Specifies the filter criteria operator for this column ("Equals", "Like", "Greater", etc.).

If this setting is enabled, the Data Grid filters its records immediately after end-users modify the filter condition. Otherwise, filtering will not be applied until a user presses the Enter key or moves focus to another cell.

Call this method from code to invoke the automatic filtering row popup menu for the specific column.

GridView.SetAutoFilterValue, GridView.GetAutoFilterValue
Methods that allow you to retrieve the currently applied auto-filtering row condition and apply a new one from code.

Clears all data filtering applied through the automatic filtering row.

Expanded Filtering Attributes Support

In code-first data sources, you can mark specific properties with DevExpress filtering attributes to change editors that appear inside Excel-style filtering menus. For example, in the code sample below the integer "ID" property is recognized as a numeric property, and a corresponding Excel-style filter shows two text boxes and a range trackbar for this property. Declaring the FilterLookup attribute before this property allows you to change these default editors with a check-list box control.

Expanded Searching for Records

Allows you to manually start a search.

Initially, this property is set to "*". In this instance, all visible columns/card fields are searched. To search specific columns/fields, assign the corresponding field names to the FindFilterColumns property, delimiting them with the ";" character. This property also allows you to limit the search columns o prevent highlighting text in non-searchable columns.

ColumnView.LocateByValue, ColumnView.LocateByDisplayText
Utilize these methods to find rows with the values and\or display text.

Expanded Group Filters

Allows you to group values in a column's filter menu by values in another column. When this feature is enabled, users can use a filter menu to filter data by multiple columns.

The OptionsColumnFilter.PopupExcelFilterGrouping property specifies data fields (columns) by which to group values in a specific column's filter menu. Data fields (columns) should be specified by their names as strings separated by a comma, semicolon, space or tab character. The code below shows how to display available models below each trademark as illustrated in the figure above.

You can specify two or more data fields to group filter values by multiple columns. The field names' order determines the group hierarchy. To show available models below each trademark in the Name column's filter menu, change the group hierarchy using the following code:

As a result, values from the Trademark column are shown at the root level.

In the case of a Code First data source, you can annotate data fields with the FilterGroup attribute using the same syntax in the attribute parameter.

Expanded See Also

Is this topic helpful?​​​​​​​