A newer version of this page is available. Switch to the current version.

Excel-style Drop-down Filter

  • 7 minutes to read

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

TIP

Demo: Excel Style Filtering

Requires installation of WPF Subscription. Download

Enable Excel-style Drop-down Filter

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 ExcelSmart drop-down filter does not work in Server Mode and Virtual Sources. Use the 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.

NOTE

Record counts are not displayed if the GridControl works in Server Mode or is bound to a Virtual Source.

Group Filters

TIP

Demos:

Requires installation of WPF Subscription. Download

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 shows how to display the available models below each trademark in the Trademark column's drop-down filter:

<dxg:GridControl>
  <dxg:GridControl.Columns>
    <dxg:GridColumn FieldName="ID" />
    <dxg:GridColumn FieldName="Model"/>
    <dxg:GridColumn FieldName="Trademark" FilterPopupGroupFields="Trademark;Model" />
  </dxg:GridControl.Columns>
  <!-- ... -->
</dxg:GridControl>

The customized column's values are displayed at the root level by default; you can omit its name ("Trademark"). The code sample below has the same effect:

<dxg:GridControl>
  <dxg:GridControl.Columns>
    <dxg:GridColumn FieldName="ID" />
    <dxg:GridColumn FieldName="Model"/>
    <dxg:GridColumn FieldName="Trademark" FilterPopupGroupFields="Model" />
  </dxg:GridControl.Columns>
  <!-- ... -->
</dxg:GridControl>

Example 2

The code sample below shows how to display the available models below each trademark in the Model column's drop-down filter:

<dxg:GridControl>
  <dxg:GridControl.Columns>
    <dxg:GridColumn FieldName="ID" />
    <dxg:GridColumn FieldName="Trademark" />
    <dxg:GridColumn FieldName="Model" FilterPopupGroupFields="Trademark;Model" />
  </dxg:GridControl.Columns>
  <!-- ... -->
</dxg:GridControl>

The customized column's values are not displayed at the root level in this case, its name ("Model") cannot be omitted.

Customize the Excel-style Drop-down Filter

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

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> 

Customize the Operator List

Standard Operators

TIP

Demo: Excel-Style Drop-Down Filter - Customize the Operator List

Requires installation of WPF Subscription. Download

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

Custom Operators

TIP

Demo: Excel-Style Drop-Down Filter - Customize the Operator List

Requires installation of WPF Subscription. Download

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.v19.1.Extensions.dll library. Refer to c:\Program Files (x86)\DevExpress 19.1\Components\Sources\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.

    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;
        }
    );
    
  2. Call the CriteriaOperator.RegisterCustomFunction method to register the custom function.

    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:

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

Predefined Filters

TIP

Demo: Excel-Style Drop-Down Filter - Predefined Filters

Requires installation of WPF Subscription. Download

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

Customize Operand Template

TIP

Demo: Excel-Style Drop-Down Filter - Customize Operands

Requires installation of WPF Subscription. Download

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:

    <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="Left"/>
                        <Binding Path="Right"/>
                    </MultiBinding>
                </dxe:TrackBarEdit.EditValue>
                <dxe:TrackBarEdit.StyleSettings>
                    <dxe:TrackBarRangeStyleSettings />
                </dxe:TrackBarEdit.StyleSettings>
            </dxe:TrackBarEdit>
        </DataTemplate>
    </UserControl.Resources>
    
    TrackBarEditRangeConverter
    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 };
        }
    } 
    
  2. Handle the ExcelStyleFilterElement.QueryOperators event, get the operator, and assign the created template to the OperatorItemBase.OperandTemplate property.

    <dxg:GridColumn FieldName="Quantity">
        <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 == "Quantity") {
            var template = (DataTemplate)FindResource("ternaryTemplate");
            e.Operators[ExcelStyleFilterElementOperatorType.Between].OperandTemplate = template;
            e.Operators[ExcelStyleFilterElementOperatorType.NotBetween].OperandTemplate = template;
        }
    }