windowsforms-402069-common-features-filtering-and-search-in-data-controls-excel-style-column-pop-up-filter-menus.md
Data controls support column filter menus inspired by Microsoft Excel.
The Excel-style filter menu has two tabs:
Values — users can select specific values or value ranges in this tab.
Filters — users can create a custom filter in this tab.
Tip
Run the following demos to see Excel-style filter menus in action:
To view the source code, click Open Solution in the ribbon UI. Note that DevExpress WinForms Components must be installed.
Use the following properties to customize/configure filter settings for all data columns.
To override an option for a specific column, use the following properties:
When a user opens a filter menu, the active tab depends on the column data type. Use the following properties to specify the active tab for a specific column:
using DevExpress.Utils.Filtering;
bcSalesDate.OptionsFilter.PopupExcelFilterDefaultTab = ExcelFilterDefaultTab.Values;
Imports DevExpress.Utils.Filtering
bcSalesDate.OptionsFilter.PopupExcelFilterDefaultTab = ExcelFilterDefaultTab.Values
The Values tab can display a track bar or a list for numbers. Use the following properties to specify the UI for a particular column:
Note that the track bar does not allow a user to select the Null value even if the column contains it. To allow a user to select the Null value, arrange values in a list instead.
using DevExpress.Utils.Filtering;
bcDiscount.OptionsFilter.PopupExcelFilterNumericValuesTabFilterType = ExcelFilterNumericValuesTabFilterType.List;
Imports DevExpress.Utils.Filtering
bcDiscount.OptionsFilter.PopupExcelFilterNumericValuesTabFilterType = ExcelFilterNumericValuesTabFilterType.List
The Values tab can arrange dates in a regular or tree list. Use the following properties to specify the UI for a particular column:
using DevExpress.Utils.Filtering;
bcSalesDate.OptionsFilter.PopupExcelFilterDateTimeValuesTabFilterType = ExcelFilterDateTimeValuesTabFilterType.List;
Imports DevExpress.Utils.Filtering
bcSalesDate.OptionsFilter.PopupExcelFilterDateTimeValuesTabFilterType = ExcelFilterDateTimeValuesTabFilterType.List
The Values tab can display a track bar or a tree list. Use the PreferredTimeValuesTabFilterType property to specify the UI for a particular column. The following code snippet changes the default (Range) value to Tree:
using DevExpress.Utils.Filtering;
ExcelFilterOptions.Default.PreferredTimeValuesTabFilterType = ExcelFilterOptions.TimeValuesTabFilterType.Tree;
Imports DevExpress.Utils.Filtering
ExcelFilterOptions.Default.PreferredTimeValuesTabFilterType = ExcelFilterOptions.TimeValuesTabFilterType.Tree
For string type columns, the following properties specify whether to show pattern-matching (e.g., Is Like) and relational (e.g., Greater Than) operators:
The following properties specify whether to show the Greater Than, Greater Than Or Equal To, Less Than, Less Than Or Equal To, and Between operators:
When a user selects a filter in the menu, it can be applied immediately or when the menu is closed. Use the following properties to specify this behavior for a particular column:
bcSalesDate.OptionsFilter.ImmediateUpdatePopupExcelFilter = DevExpress.Utils.DefaultBoolean.True;
bcSalesDate.OptionsFilter.ImmediateUpdatePopupExcelFilter = DevExpress.Utils.DefaultBoolean.True
The following events fire before the menu is displayed and allow you to override settings for a particular column:
The example below applies filter settings to specific columns.
using DevExpress.Utils.Filtering;
using DevExpress.Utils.Filtering.Internal;
private void TreeList_ShowFilterPopupExcel(object sender, FilterPopupExcelEventArgs e) {
if (e.Column == bcName || e.Column == bcTrademark) {
e.ShowCustomFilters = false;
e.DefaultFilterType = CustomUIFilterType.BeginsWith;
}
if (e.Column == bcModification) {
e.ShowFiltersTab = false;
e.IsRadioMode = true;
}
}
Imports DevExpress.Utils.Filtering
Imports DevExpress.Utils.Filtering.Internal
Private Sub treeList1_ShowFilterPopupExcel(ByVal sender As Object, ByVal e As DevExpress.XtraTreeList.FilterPopupExcelEventArgs)
If e.Column Is bcName OrElse e.Column Is bcTrademark Then
e.ShowCustomFilters = False
e.DefaultFilterType = CustomUIFilterType.BeginsWith
End If
If e.Column Is bcModification Then
e.ShowFiltersTab = False
e.IsRadioMode = True
End If
End Sub
If the ShowPredefinedFilters option is enabled, you can display custom filters when a user selects the Predefined Filters item in the Filters tab. To create custom filters, handle the following events:
For example, to show predefined filters similar to those in the figure below, use the following code.
void gridView_FilterPopupExcelData(object sender, FilterPopupExcelDataEventArgs e) {
string fieldName = e.Column.FieldName;
if(e.Column == bcModification) {
e.AddFilter("<image=A><nbsp>Automatic Transmission (6-speed)", "Contains([" + fieldName + "], '6A')", true);
e.AddFilter("<image=A><nbsp>Automatic Transmission (8-speed)", "Contains([" + fieldName + "], '8A')", true);
e.AddFilter("<image=M><nbsp>Manual Transmission (6-speed)", "Contains([" + fieldName + "], '6M')", true);
e.AddFilter("<image=M><nbsp>Manual Transmission (7-speed)", "Contains([" + fieldName + "], '7M')", true);
e.AddFilter("<image=V><nbsp>Variomatic Transmission", "Contains([" + fieldName + "], 'VA')", true);
e.AddFilter("<b>Limited Edition</b>", "Contains([" + fieldName + "], 'Limited')", true);
}
if(e.Column == bcMPGCity) {
e.AddFilter("Fuel Economy (<color=green>High</color>)", "[" + fieldName + "]<=15", true);
e.AddFilter("Fuel Economy (<color=orange>Medium</color>)", "[" + fieldName + "]>15 AND [" + fieldName + "]<25", true);
e.AddFilter("Fuel Economy (<color=red>Low</color>)", "[" + fieldName + "]>=25", true);
}
}
Private Sub gridView_FilterPopupExcelData(ByVal sender As Object, ByVal e As Views.Grid.FilterPopupExcelDataEventArgs) Handles bandedGridView1.FilterPopupExcelData
If e.Column Is bcModification Then
e.AddFilter("<image=A><nbsp>Automatic Transmission (6-speed)", "Contains([" & e.Column.FieldName & "], '6A')", True)
e.AddFilter("<image=A><nbsp>Automatic Transmission (8-speed)", "Contains([" & e.Column.FieldName & "], '8A')", True)
e.AddFilter("<image=M>Manual Transmission (6-speed)", "Contains([" & e.Column.FieldName & "], '6M')", True)
e.AddFilter("<image=M>Manual Transmission (7-speed)", "Contains([" & e.Column.FieldName & "], '7M')", True)
e.AddFilter("<image=V>Variomatic Transmission", "Contains([" & e.Column.FieldName & "], 'VA')", True)
e.AddFilter("<b>Limited Edition</b>", "Contains([" & e.Column.FieldName & "], 'Limited')", True)
End If
If e.Column Is bcMPGCity Then
e.AddFilter("Fuel Economy (<color=green>High</color>)", "[" & e.Column.FieldName & "]<=15", True)
e.AddFilter("Fuel Economy (<color=orange>Medium</color>)", "[" & e.Column.FieldName & "]>15 AND [" & e.Column.FieldName & "]<25", True)
e.AddFilter("Fuel Economy (<color=red>Low</color>)", "[" & e.Column.FieldName & "]>=25", True)
End If
End Sub
You can display a filter based on a custom function. For example, the following menu displays the Black Friday Discount filter. This filter is based on its corresponding custom function:
See the following help topic for more information: Custom Function Based Filters.
A column’s filter menu shows only values available in that column. To filter data by multiple columns, invoke each column’s filter menu.
It is also possible to group filter values in the current column’s filter menu by another column. This allows you to filter data for multiple columns from a single menu.
The following properties specify data fields (columns) by which you can group filter 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 assigned tasks below each employee as illustrated in the figure above.
//Customize the Employee column's filter menu.
colEmployee.OptionsFilter.PopupExcelFilterGrouping = "Employee;Task";
//As values of the customized column are displayed at the root level, you can omit the column name ("Employee").
//The code below has the same effect.
colEmployee.OptionsFilter.PopupExcelFilterGrouping = "Task";
'Customize the Employee column's filter menu.
colEmployee.OptionsFilter.PopupExcelFilterGrouping = "Employee;Task"
'As values of the customized column are displayed at the root level, you can omit the column name ("Employee").
'The code below has the same effect.
colEmployee.OptionsFilter.PopupExcelFilterGrouping = "Task"
You can specify two or more data fields (columns) to group filter values by multiple columns. The field name order determines the group hierarchy. To show assigned tasks below each employee in the Task column’s filter menu, use the following code:
//Customize the Task column's filter menu.
//As values of the customized column are not displayed at the root level in this case, its name ("Task") cannot be omitted.
colTask.OptionsFilter.PopupExcelFilterGrouping = "Employee;Task";
'Customize the Task column's filter menu.
'As values of the customized column are not displayed at the root level in this case, its name ("Task") cannot be omitted.
colTask.OptionsFilter.PopupExcelFilterGrouping = "Employee;Task"
The result is that values from the Employee column are shown at the root level.
Tip
To view grouped filters in action, refer to the Task column’s filter menu in the following demo: Hierarchy Column Module in the XtraTreeList Main Demo.
If you have a Code First data source, you can annotate data fields with the FilterGroup attribute.
[Utils.Filtering.FilterGroup("Employee;Task")]
public string Task { get; set; }
public string Employee { get; set; }
<Utils.Filtering.FilterGroup("Employee;Task")>
Public Property Task As String
Public Property Employee As String