wpf-120529-controls-and-libraries-data-grid-filtering-and-searching-filter-dropdown-excel-style-drop-down-filter.md
The GridControl provides an Excel-style drop-down filter:
Run Demo: Excel Style Filtering
Note
Starting from v18.2 , the GridControl supports the FilterPopupMode.ExcelSmart / ColumnFilterPopupMode.ExcelSmart mode. In the previous versions, use the FilterPopupMode.Excel / ColumnFilterPopupMode.Excel mode instead.
The Filter Rules tab shows a list of operators the selected column accepts. A user should select an operator and specify operand values.
The Filter Values tab shows an Excel-style checklist with the filter values. Record counts are displayed next to the filter values. The built-in search panel allows users to find filter values.
Tip
Demos :
You can group filter values in the Filter Values tab to allow users to filter data by multiple columns. Set the ColumnBase.FilterPopupGroupFields property to names of the fields by which filter values should be grouped. Separate names by comma, semicolon, or space. The order of field names determines the hierarchy in the group.
The code sample below displays the available cities below each country in the Ship Country column’s drop-down filter:
<dxg:GridControl>
<dxg:GridControl.Columns>
<dxg:GridColumn FieldName="ProductName"/>
<dxg:GridColumn FieldName="ShipCountry" FilterPopupGroupFields="ShipCountry;ShipCity"/>
<dxg:GridColumn FieldName="ShipCity"/>
</dxg:GridControl.Columns>
<!-- ... -->
</dxg:GridControl>
The customized column’s values are displayed at the root level. You can omit its name ( “ShipCountry” ). The code sample below has the same effect:
<dxg:GridControl>
<dxg:GridControl.Columns>
<dxg:GridColumn FieldName="ProductName"/>
<dxg:GridColumn FieldName="ShipCountry" FilterPopupGroupFields="ShipCity"/>
<dxg:GridColumn FieldName="ShipCity"/>
</dxg:GridControl.Columns>
<!-- ... -->
</dxg:GridControl>
The code sample below displays the available cities below each country in the Ship City column’s drop-down filter:
<dxg:GridControl>
<dxg:GridControl.Columns>
<dxg:GridColumn FieldName="ProductName"/>
<dxg:GridColumn FieldName="ShipCountry"/>
<dxg:GridColumn FieldName="ShipCity" FilterPopupGroupFields="ShipCountry;ShipCity"/>
</dxg:GridControl.Columns>
<!-- ... -->
</dxg:GridControl>
The customized column’s values are not displayed at the root level in this case, its name ( “ShipCountry” ) cannot be omitted.
You can filter data by applying Conditional Formatting rules. The Excel-style Drop-down Filter displays available rules and indicates the number of records that meet rule conditions:
Run Demo: Conditional Formatting
The column’s drop-down filter displays only the values that meet the current filter criteria. To show all values, set the DataControlBase.ShowAllTableValuesInFilterPopup / ColumnBase.ShowAllTableValuesInFilterPopup property to true.
Use the ColumnBase.CustomColumnFilterPopupTemplate property to specify a custom data template. In the template, define a filter element with the PART_FilterElement name and specify its settings.
Run Demo: Excel-Style Drop-Down Filter - Custom Filter Popup Content
The following code sample uses the RangeFilterElement as a custom data template:
<dxg:GridControl x:Name="grid" ItemsSource="...">
<dxg:GridControl.Columns>
<!-- -->
<dxg:GridColumn FieldName="Quantity">
<dxg:GridColumn.CustomColumnFilterPopupTemplate>
<DataTemplate>
<dxfui:RangeFilterElement x:Name="PART_FilterElement"/>
</DataTemplate>
</dxg:GridColumn.CustomColumnFilterPopupTemplate>
</dxg:GridColumn>
<!-- -->
</dxg:GridControl.Columns>
<dxg:GridControl.View>
<dxg:TableView ColumnFilterPopupMode="ExcelSmart" />
</dxg:GridControl.View>
</dxg:GridControl>
Tip
Topic : Custom Drop-down Filter
Run Demo: Excel-Style Drop-Down Filter - Customize the Operator List
The Excel-style Drop-down Filter shows a list of operators the selected field accepts. To customize the operator list, use the ExcelStyleFilterElement as a custom data template and handle the ExcelStyleFilterElement.QueryOperators event.
The code sample below removes all operators except Equal and Not Equal :
<dxg:GridColumn FieldName="OrderDate">
<dxg:GridColumn.CustomColumnFilterPopupTemplate>
<DataTemplate>
<dxfui:ExcelStyleFilterElement x:Name="PART_FilterElement" QueryOperators="OnExcelStyleFilterQueryOperators"/>
</DataTemplate>
</dxg:GridColumn.CustomColumnFilterPopupTemplate>
</dxg:GridColumn>
void OnExcelStyleFilterQueryOperators(object sender, ExcelStyleFilterElementQueryOperatorsEventArgs e) {
if(e.FieldName == "OrderDate") {
e.Operators.Clear();
e.Operators.Add(new ExcelStyleFilterElementOperatorItem(ExcelStyleFilterElementOperatorType.Equal));
e.Operators.Add(new ExcelStyleFilterElementOperatorItem(ExcelStyleFilterElementOperatorType.NotEqual));
}
}
Private Sub OnExcelStyleFilterQueryOperators(ByVal sender As Object, ByVal e As ExcelStyleFilterElementQueryOperatorsEventArgs)
If e.FieldName = "OrderDate" Then
e.Operators.Clear()
e.Operators.Add(New ExcelStyleFilterElementOperatorItem(ExcelStyleFilterElementOperatorType.Equal) With {
.Caption = "Equal"
})
e.Operators.Add(New ExcelStyleFilterElementOperatorItem(ExcelStyleFilterElementOperatorType.NotEqual) With {
.Caption = "Not Equal"
})
End If
End Sub
Run Demo: Excel-Style Drop-Down Filter - Customize the Operator List
You can use the ExcelStyleFilterElement.QueryOperators event to add custom operators. The code sample below adds the Last Years operator:
Create a custom function. Do one of the following:
Call the CriteriaOperator.RegisterCustomFunction method to register the custom function.
Create the ExcelStyleFilterElementOperatorItem and add it to the ExcelStyleFilterElementQueryOperatorsEventArgs.Operators collection. Specify the operator item’s edit settings to define its operands:
Run Demo: Excel-Style Drop-Down Filter - Predefined Filters
You can specify Predefined Filters with the ColumnBase.PredefinedFilters property. The Excel-style Drop-Down Filter displays these filters in the Filter Rules tab:
<dxg:GridColumn FieldName="UnitPrice">
<dxg:GridColumn.PredefinedFilters>
<dxfui:PredefinedFilterCollection>
<dxfui:PredefinedFilter Name="Less than 10" Filter="?p < 10" />
<dxfui:PredefinedFilter Name="Between 10 and 50" Filter="?p > 10 and ?p < 50" />
<dxfui:PredefinedFilter Name="Between 50 and 100" Filter="?p > 50 and ?p < 100" />
<dxfui:PredefinedFilter Name="Greater than 100" Filter="?p > 100" />
</dxfui:PredefinedFilterCollection>
</dxg:GridColumn.PredefinedFilters>
</dxg:GridColumn>
Tip
Topic : Predefined Filters
Run Demo: Excel-Style Drop-Down Filter - Customize Operands
The Excel-style Drop-Down Filter automatically creates operand editors based on the field and operator type. You can customize operand editors.
The following code sample specifies the TrackBarEdit as an operand for the Between and NotBetween operators:
Create a template for the operands. The following models depend on the operator type. Use their properties to bind to operand values in the template:
Handle the ExcelStyleFilterElement.QueryOperators event, get the operator, and assign the created template to the OperatorItemBase.OperandTemplate property.
In Virtual Sources and Server Mode, record counts and custom filter items (for example, Blanks) are not displayed by default. To display record counts, handle the DataControlBase.CustomUniqueValues event. For Virtual Sources, you can alternatively handle the GetUniqueValues event.