Filtering

When a grid contains thousands of records, it may be hard to find the needed ones using scrolling or pager navigation. In those cases, the dxDataGrid widget offers its filtering capabilities.

View Demo

Filter Row

One of the filtering capabilities that the dxDataGrid provides for end users is a filter row. To filter records by a column, an end-user must type a value or a part of it (depends on the data type) in a corresponding filter row cell. If any records are found, then the grid will contain only them. Otherwise, it displays the "No Data" message.

DevExtreme DataGrid FilterRow

NOTE
Filtering is performed ignoring the letter case.

Usually, a filter row cell is a text box. But in some cases, a filter row cell is represented by a control more suitable for filtering in the particular column. For example, when the dataType option of a column is 'date', its filter row cell contains a calendar that allows a user to choose a date. Another example is a select box that appears in a filter row cell if the column's dataType is 'boolean', or if a lookup column is used.

Different filter operations may be applied to a column. To change the current filter operation, the user must choose a required item from a filtering menu. This menu is invoked when the user hovers the mouse pointer over the magnifying glass icon of a filter row cell. Operations available using this menu depend on the data type of a column and vary from type to type, as well as the default filter operation. After a filter operation is applied, the default operation can be restored by choosing the "Reset" item in the filtering menu. The following table represents filter operations by data types.

dataType Available filter operations Default filter operation
'string' 'contains' | 'notcontains' | 'startswith' | 'endswith' | '=' | '<>' 'contains'
'number' '=' | '<>' | '<' | '>' | '<=' | '>=' '='
'date' '=' | '<>' | '<' | '>' | '<=' | '>=' '='

To make a filter row available for end users, set the filterRow | visible option to true.

See Also

For information on how to configure the filter row, refer to the Filter Row topic.

If the filter row is visible, filtering is enabled for all grid columns. However, you can disable it for individual columns by setting their allowFiltering option to false.

JavaScript
var gridOptions = {
    // ...
    filterRow: { visible: true },
    columns: [{
        allowFiltering: false,
        // ...
    }, //...
    ]
};

The set of filter operations appearing in the filtering menu may be corrected. For this purpose, assign an array of required operations to the filterOperations option of a column. To apply one of these operations initially, specify the selectedFilterOperation option of the same column.

JavaScript
var gridOptions = {
    // ...
    columns: [{
        filterOperations: [ 'contains', 'startswith', '=' ],
        selectedFilterOperation: 'contains',
        // ...
    }, //...
    ]
};

There may be cases when a user must be able to apply a filter, but not to change the filter operation. In these cases, assign an empty array to the filterOperations option and specify the current filter operation using the selectedFilterOperation option.

JavaScript
var gridOptions = {
    // ...
    columns: [{
        filterOperations: [],
        selectedFilterOperation: 'contains',
        // ...
    }, //...
    ]
};

If a filter must be applied to a column initially, set the filterValue option of this column. This value is applied using a filter operation specified by the selectedFilterOperation option. Note that the allowFiltering option of this column must be true. The following code leaves only those records, whose value in the "Data" column equals 10.

JavaScript
$("#gridContainer").dxDataGrid({
    // ...
    columns: [{
        dataField: 'Data',
        selectedFilterOperation: '=',
        filterValue: 10
        // ...
    }, //...
    ]
});

To make set filtering impossible for a user to change, hide the filter row by setting the filterRow | visible option to false.

Column Header Filter

To filter data against a specific column, an end user can invoke the column's header filter. The filter dropdown displays unique column values. The end user can select any item(s) from this list. This action automatically filters grid data (displays records that have the selected value(s) in the current column).

DevExtreme DataGrid Header Filter

To make column header filtering available for end users, set the grid's headerFilter | visible option to true.

By default, the filter button is available for all grid columns. However, you can hide it for individual columns by setting their allowFiltering option to false.

JavaScript
var gridOptions = {
    // ...
    headerFilter: { visible: true },
    columns: [{
        allowFiltering: false,
        // ...
    }, //...
    ]
};

You can specify whether to include or exclude the records with the values selected in the filter dropdown list of a particular column. For this purpose, use the column's filterType option.

JavaScript
var gridOptions = {
    // ...
    headerFilter: { visible: true },
    columns: [{
        filterType: 'exclude',
        // ...
    }, //...
    ]
};

If a filter must be applied to a column initially, set the filterValues option of this column. Note that the allowFiltering option of this column must be true. The following code leaves only those records, whose value in the "City" column equals 'Los Angeles'.

JavaScript
$("#gridContainer").dxDataGrid({
    // ...
    columns: [{
        dataField: 'City',
        filterValues: ['Los Angeles']
        // ...
    }, //...
    ]
});

To make changing the filtering that is set initially impossible for a user, make the column header filters invisible by setting the headerFilter | visible option to false.

Searching

Filtering can be performed in one or several columns at a time. In addition, dxDataGrid provides you with the capability to filter records by all columns at once. In dxDataGrid, searching is performed using a search panel. To search through records, a user must type in text in the search panel. Records that match this text are displayed in a grid.

DevExtreme DataGrid SearchPanel

Searching is conducted differently in columns with different data types. To find a record by a numeric, boolean or date value, a user must enter the full value into the search panel. To find a record by a string value, entering a part of this value is sufficient.

The search panel is available for end users when the searchPanel | visible option is set to true.

See Also

To find information on configurable features of the search panel, refer to the Search Panel topic.

Although by default searching is performed in all columns, you can disable it in certain columns. For this purpose, set the allowSearch option to false.

JavaScript
var gridOptions = {
    // ...
    searchPanel: { visible: true },
    columns: [{
        allowSearch: false,
        // ...
    }, //...
    ]
};

You can also specify an initial search string using the searchPanel | text option.

JavaScript
$("#gridContainer").dxDataGrid({
    // ...
    searchPanel: {
        text: 'Search string'
    }
});

Searching can also be conducted in code using the searchByText(text) method. To clear search settings, call the same method with an empty string as the argument.

To try searching in action, open the example below and type in text in the search panel.

Filtering within Calculated Values

Filtering performed by using a filter row or the search panel may operate incorrectly in a column with calculated values. To restore this functionality, implement the calculateFilterExpression callback function. This function must return an array of the following items.

  1. The data source field or a function providing actual values for a grid column. Commonly, the calculateCellValue function is passed here.
  2. The comparison operator. The following operators are available: "=", "<>", ">", ">=", "<", "<=", "startswith", "endswith", "contains", "notcontains".
  3. The value typed into a filter row cell. The actual values will be compared to this value.

The following code snippet demonstrates a common example of implementing the calculateCellValue and calculateFilterExpression functions.

JavaScript
var gridOptions = {
    // ...
    columns: [{
        calculateCellValue: function (rowData) {
            return rowData.Amount * rowData.Cost;
        },
        calculateFilterExpression: function (filterValue, selectedFilterOperation) {
            return [this.calculateCellValue, selectedFilterOperation || '=', filterValue];
        },
        // ...
    }, //...
    ]
};

Filtering in Code

To apply a filter in code, call the filter(filterExpr) method. Pass an array with the following members to this method.

  1. The data source field by which data items are filtered.
  2. The comparison operator. The following operators are available: "=", "<>", ">", ">=", "<", "<=", "startswith", "endswith", "contains", "notcontains".
  3. The value with which data source field values should be compared.

For example, the following call leaves only those records that have a value of more than 20 in the 'Amount' field.

JavaScript
dataGridInstance.filter(['Amount', '>', 20]);

The filter(filterExpr) method applied the specified filter to the grid's data source. The filtering that is applied by end users is added to the data source's filter. To clear all the applied filters, call the clearFilter() method.

JavaScript
dataGridInstance.clearFilter();

A user-specified filter can also be cleared from code. For this purpose, assign undefined to the filterValue option of the required column using the columnOption method.

JavaScript
dataGridInstance.columnOption('columnId', 'filterValue', undefined);

To get the filter applied to the grid's data source, call the grid's filter() method without parameters. To get the total filter combined from the data source filter and the filters applied in a UI, use the grild's getCombinedFilter() method.

View Demo