Excel-style Drop-down Filter

• May 18, 2023
• 7 minutes to read

The GridControl provides an Excel-style drop-down filter:

Run Demo: Excel Style Filtering

Enable Excel-style Drop-down Filter

• For a specific column, set the ColumnBase.FilterPopupMode property to FilterPopupMode.ExcelSmart.
• For all columns, set the DataViewBase.ColumnFilterPopupMode to ColumnFilterPopupMode.ExcelSmart.

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.

Excel-style Drop-down Filter Elements

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.

Group Filters

Tip

Demos:

• Excel Style Filtering
• Excel-Style Drop-Down Filter - Group Filters

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.

Example 1

The code sample below displays the available cities below each country in the Ship Country column’s drop-down filter:

XAML

<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:

XAML

<dxg:GridControl>
    <dxg:GridControl.Columns>
        <dxg:GridColumn FieldName="ProductName"/>
        <dxg:GridColumn FieldName="ShipCountry" FilterPopupGroupFields="ShipCity"/>
        <dxg:GridColumn FieldName="ShipCity"/>
    </dxg:GridControl.Columns>
  <!-- ... -->
</dxg:GridControl>

Example 2

The code sample below displays the available cities below each country in the Ship City column’s drop-down filter:

XAML

<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.

Conditional Formatting Filters

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

Customize the Excel-style Drop-down Filter

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.

Customize Filter Popup Content

Run Demo: Excel-Style Drop-Down Filter - Custom Filter Popup Content

The following code sample uses the RangeFilterElement as a custom data template:

Xaml

<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

Customize the Operator List

Standard Operators

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:

Xaml

<dxg:GridColumn FieldName="OrderDate">
    <dxg:GridColumn.CustomColumnFilterPopupTemplate>
        <DataTemplate>
            <dxfui:ExcelStyleFilterElement x:Name="PART_FilterElement" QueryOperators="OnExcelStyleFilterQueryOperators"/>
        </DataTemplate>
    </dxg:GridColumn.CustomColumnFilterPopupTemplate>
</dxg:GridColumn>

C#

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));
    }
} 

VB.NET

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 

Custom Operators

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:

1. Create a custom function. Do one of the following:

   • Use the CustomFunctionFactory.Create method.

     The CustomFunctionFactory is an extension of the DevExpress.Xpf.Grid.v24.2.Extensions.dll library. Refer to C:\Program Files\DevExpress 24.2\Components\Sources\XPF\DevExpress.Xpf.Grid\DevExpress.Xpf.Grid.Extensions\ for information on how extension methods work.

     The CustomFunctionFactory.Create method allows you to create a custom function with a maximum of 4 operands.

   • Implement the ICustomFunctionOperator interface. Refer to the Implementing Custom Functions topic for more information.

   C#

   const string CustomFunctionName = "LastYears";
   var currentYear = DateTime.Now.Year;
   
   ICustomFunctionOperatorBrowsable customFunction = CustomFunctionFactory.Create(CustomFunctionName, 
       (DateTime date, int threshold) => {
           return currentYear >= date.Year && currentYear - date.Year <= threshold;
       }
   );
   

   VB.NET

   Const CustomFunctionName As String = "LastYears"
   Private currentYear = DateTime.Now.Year
   
   Private customFunction As ICustomFunctionOperatorBrowsable = CustomFunctionFactory.Create(CustomFunctionName, 
       Function(ByVal date As DateTime, ByVal threshold As Integer) currentYear >= date.Year AndAlso currentYear - date.Year <= threshold)
   

2. Call the CriteriaOperator.RegisterCustomFunction method to register the custom function.

   C#

   CriteriaOperator.RegisterCustomFunction(customFunction);
   

   VB.NET

   CriteriaOperator.RegisterCustomFunction(customFunction)
   

3. Create the ExcelStyleFilterElementOperatorItem and add it to the ExcelStyleFilterElementQueryOperatorsEventArgs.Operators collection. Specify the operator item’s edit settings to define its operands:

   C#

   void OnExcelStyleFilterQueryOperators(object sender, ExcelStyleFilterElementQueryOperatorsEventArgs e) {
       if(e.FieldName == "OrderDate") {
           // ...
   
           var customFunctionEditSettings = new BaseEditSettings[] { 
               new TextEditSettings { MaskType = MaskType.Numeric, Mask = "D", MaskUseAsDisplayFormat = true } 
           };
           e.Operators.Add(new ExcelStyleFilterElementOperatorItem(CustomFunctionName, customFunctionEditSettings) { Caption = "Last Years" });
       }
   } 
   

   VB.NET

   Private Sub OnExcelStyleFilterQueryOperators(ByVal sender As Object, ByVal e As ExcelStyleFilterElementQueryOperatorsEventArgs)
       If e.FieldName = "OrderDate" Then
           ' ...
   
           Dim customFunctionEditSettings = New BaseEditSettings() {New TextEditSettings With {
               .MaskType = MaskType.Numeric,
               .Mask = "D",
               .MaskUseAsDisplayFormat = True
           }}
           e.Operators.Add(New ExcelStyleFilterElementOperatorItem(CustomFunctionName, customFunctionEditSettings) With {
               .Caption = "Last Years"
           })
       End If
   End Sub
   

Predefined Filters

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:

Xaml

<dxg:GridColumn FieldName="UnitPrice">
    <dxg:GridColumn.PredefinedFilters>
        <dxfui:PredefinedFilterCollection>
            <dxfui:PredefinedFilter Name="Less than 10" Filter="?p &lt; 10" />
            <dxfui:PredefinedFilter Name="Between 10 and 50" Filter="?p &gt; 10 and ?p &lt; 50" />
            <dxfui:PredefinedFilter Name="Between 50 and 100" Filter="?p &gt; 50 and ?p &lt; 100" />
            <dxfui:PredefinedFilter Name="Greater than 100" Filter="?p &gt; 100" />
        </dxfui:PredefinedFilterCollection>
    </dxg:GridColumn.PredefinedFilters>
</dxg:GridColumn> 

Tip

Topic: Predefined Filters

Customize Operand Template

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:

1. 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:

   • ConstantFilterModel (e.g., Is null, Is Yesterday)
   • BinaryFilterModel (e.g., Equals, Is greater than)
   • TernaryFilterModel (e.g., Is between, Is between dates)
   • VariadicFilterModel (e.g., Is any of, Is none of)
   Xaml

   <UserControl.Resources>
       <DataTemplate x:Key="ternaryTemplate">
           <dxe:TrackBarEdit Minimum="0" Maximum="300" MinWidth="120" TickPlacement="None">
               <dxe:TrackBarEdit.EditValue>
                   <MultiBinding Converter="{local:TrackBarEditRangeConverter}">
                       <Binding Path="LeftValueItem.Value"/>
                       <Binding Path="RightValueItem.Value"/>
                   </MultiBinding>
               </dxe:TrackBarEdit.EditValue>
               <dxe:TrackBarEdit.StyleSettings>
                   <dxe:TrackBarRangeStyleSettings />
               </dxe:TrackBarEdit.StyleSettings>
           </dxe:TrackBarEdit>
       </DataTemplate>
   </UserControl.Resources>
   

   TrackBarEditRangeConverter

   C#

   public class TrackBarEditRangeConverter : BaseMultiValueConverter {
       public override object Convert(object[] values, Type targetType, object parameter, CultureInfo culture) {
           if(values.Length != 2)
               return new TrackBarEditRange();
           return new TrackBarEditRange(GetApproptiateValue(values[0]), GetApproptiateValue(values[1]));
       }
   
       short GetApproptiateValue(object value) {
           if(value is int)
               return System.Convert.ToInt16(value);
           return value is short ? (short)value : (short)0;
       }
   
       public override object[] ConvertBack(object value, Type[] targetTypes, object parameter, CultureInfo culture) {
           var trackBarEditRange = value as TrackBarEditRange;
           if(trackBarEditRange == null)
               return new object[] { (short)0, (short)0 };
           return new object[] { (short)trackBarEditRange.SelectionStart, (short)trackBarEditRange.SelectionEnd };
       }
   } 
   

   VB.NET

   Public Class TrackBarEditRangeConverter
   Inherits BaseMultiValueConverter
   
       Public Overrides Function Convert(ByVal values As Object(), ByVal targetType As Type, ByVal parameter As Object, ByVal culture As CultureInfo) As Object
           If values.Length <> 2 Then Return New TrackBarEditRange()
           Return New TrackBarEditRange(GetApproptiateValue(values(0)), GetApproptiateValue(values(1)))
       End Function
   
       Private Function GetApproptiateValue(ByVal value As Object) As Short
           If TypeOf value Is Integer Then Return System.Convert.ToInt16(value)
           Return If(TypeOf value Is Short, CShort(value), CShort(0))
       End Function
   
       Public Overrides Function ConvertBack(ByVal value As Object, ByVal targetTypes As Type(), ByVal parameter As Object, ByVal culture As CultureInfo) As Object()
         Dim trackBarEditRange = TryCast(value, TrackBarEditRange)
           If trackBarEditRange Is Nothing Then Return New Object() {CShort(0), CShort(0)}
           Return New Object() {CShort(trackBarEditRange.SelectionStart), CShort(trackBarEditRange.SelectionEnd)}
       End Function
   End Class 
   

2. Handle the ExcelStyleFilterElement.QueryOperators event, get the operator, and assign the created template to the OperatorItemBase.OperandTemplate property.

   Xaml

   <dxg:GridColumn FieldName="Quantity">
       <dxg:GridColumn.CustomColumnFilterPopupTemplate>
           <DataTemplate>
               <dxfui:ExcelStyleFilterElement x:Name="PART_FilterElement" QueryOperators="OnExcelStyleFilterQueryOperators"/>
           </DataTemplate>
       </dxg:GridColumn.CustomColumnFilterPopupTemplate>
   </dxg:GridColumn>
   

   C#

   void OnExcelStyleFilterQueryOperators(object sender, ExcelStyleFilterElementQueryOperatorsEventArgs e) {
       if(e.FieldName == "Quantity") {
           var template = (DataTemplate)FindResource("ternaryTemplate");
           e.Operators[ExcelStyleFilterElementOperatorType.Between].OperandTemplate = template;
           e.Operators[ExcelStyleFilterElementOperatorType.NotBetween].OperandTemplate = template;
       }
   } 
   

   VB.NET

   Private Sub OnExcelStyleFilterQueryOperators(ByVal sender As Object, ByVal e As ExcelStyleFilterElementQueryOperatorsEventArgs)
       If e.FieldName = "Quantity" Then
           Dim template = CType(FindResource("ternaryTemplate"), DataTemplate)
           e.Operators(ExcelStyleFilterElementOperatorType.Between).OperandTemplate = template
           e.Operators(ExcelStyleFilterElementOperatorType.NotBetween).OperandTemplate = template
       End If
   End Sub
   

Limitations

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.