Version 9.8.15 of the Windows GUI introduces a new feature – Excel-style filtering. The feature is named Excel-style filtering because it aims to offer users a filtering experience which is similar to that of Excel and feels familiar and intuitive. Excel-style filtering provides a way to filter individual columns on multiple values, something which is frequently requested by our users. This feature is not available for the Web GUI at the moment, but it will be added in the future.

Where to find it

Excel-style filtering can be accessed through a new menu which is available on the column headers of the grid. This menu can be opened by right-clicking a column header or by clicking on the menu button on a column header, shown in the image below. The column header menu also provides a new entry point for existing features such as grouping, sorting, opening the filter dialog and clearing all filters.

Column header menu button

What it looks like

The way in which the values are grouped as a tree of checkboxes in the column header menu depends on the datatype of the corresponding column. The image below shows the column header menu of a column which has a fixed enumeration of values (elements). Since the number of unique values in such enumerations is typically small, the column header menu shows all unique values in the dataset on a single level in the tree of checkboxes.

Enumeration column header menu



However, when the corresponding column has, for instance, a DATE datatype, then the number of unique values could easily run into the thousands. The unique values in the dataset will then be grouped by year and by month, as can be seen in the image below. The column header menu distinguishes between numerical, enumeration, text, date, time and datetime columns.

Date column header menu

Interoperability with other filters

The Excel-style filtering feature provides interoperability with quick filters and filters applied in the traditional filter dialog or filter panel. All filters applied in the filter dialog or panel, as well as all quick filters, can be accurately visualized by the checkboxes in the column header menu. Conversely, if you only check or uncheck a single checkbox in the column header menu, even if it’s a checkbox which groups many others, the filter dialog will display that filter condition correctly as well. However, since the filter dialog is unable to visualize conditions on multiple values (OR-conditions), these conditions are not shown by the filter dialog.

How to use it in your application

The column header menu can be turned on by setting the ini-parameter/configuration-option showcolumnheadermenu to true. However, whether the column header menu will actually be available is determined by whether it’s allowed to group, sort or filter a given column. If none of these features are available for a column, then the column header menu won’t be available either. If some features are available while others are not, then only the available features can be used from the column header menu. Excel-style filtering is turned off for columns whose datatype does not allow filtering (such as binary types) or if the control of the display-column is a type of multiline control (including HTML and RTF) or password control.