Sort & Filter Listing Data

You can easily sort and filter listing data using the Sort & Filter menu, without using the CQL Editor to edit the associated statement. Each time you update the sort order or any filters, Workbench updates the applied CQL statement. This allows you to quickly and easily create the view you need. You can then save your listing to return to it later.

To open the Sort & Filter menu, click the column header for the column you want to sort by or apply a filter to.

If a column already has a sort or filter applied, Workbench shows the Sort icon ( for ascending or for descending) and the Filter icon (filter_list). You can click these in the Column Header to edit the sort or filter. You can also sort and filter columns that don’t already have a sort and filter.

How to Sort a Listing

You can sort by a single column using the Sort & Filter menu. If you want to apply a multi-sort (sorting by more than one column), you can use the CQL Editor to update your listing’s ORDER BY clause.

To sort by a column:

1. Locate the column you want to sort by in the datasheet.
2. If the column doesn’t already have a sort or filter applied, hover over the Column Header to show the Filter icon (filter_list).
3. Click the Filter icon (filter_list). This opens the Sort & Filter menu.
4. Under Sort by, select Ascending (A to Z) or Descending (Z-A) for a sort order.
5. Click OK. Workbench sorts the datasheet by the column values in the chosen sort order. Workbench shows a Sort icon ( for ascending or for descending) in the column header.
6. Optional: Save your listing to preserve your sort order.

How to Reset a Column’s Sort Order

To reset (remove) the sort and return the listing to the default order, open the Sort & Filter menu and click Reset.

How to Filter a Listing

You can also filter your listing using the Sort & Filter menu. You can select a comparison operator and, if required, a value to filter by, and use them as a filter.

Note that conditional filters are added to any existing filter (WHERE clause) in the CQL statement. To completely replace the WHERE clause in the CQL statement, you must edit the CQL statement to remove it.

In the current release, for filters applied via the Sort & Filter menu, Workbench treats all data as strings for filtering. You can apply more advanced filters using the WHERE clause in your CQL statement. Consider the following when filtering in the data grid:

• When filtering a date column using the equals (=) operator, you must use the YYYY-mm-dd date format (2020-04-24) when entering Values for the conditional filter.
• You can filter date columns with the like (%) operator on YYYY, mm, and dd format date components, but not on the spelled or abbreviated month. For example, %2020% is allowed, but %Nov%` is not an allowed filter.
• When a column has mixed units (for example, a Height column may have values in both inches and feet), we recommend that you use the CQL Editor to filter the column, as in the current release, Workbench can’t consider the unit of measure when applying filters from the data grid.

To filter your listing with a conditional filter:

1. Locate the column you want to filter by in the datasheet.
2. Hover over the Column Header to show the Filter icon (filter_list).
3. Click the Filter icon (filter_list). This opens the Sort & Filter menu.
4. Under Condition, select an Operator. Workbench uses the entered Value and chosen Operator to compare the values within the column. Learn more about the available comparison operators in the CQL Reference.
5. If required, enter a Value compare values against. Note that you can only use a static value and not a function. For dates, use YYYY-MM-DD.

6. You can also filter by comparisons. After selecting an Operator, click Compare (). Then, you can select from the available columns.

7. Click OK.

How to Reset a Filter

To reset (remove) a filter from a column, open the Sort & Filter menu and click Reset. To reset all filters across the entire listing, open the CQL Editor and remove the WHERE clause from the CQL statement.

Reset All Sort & Filters for a Listing

You can reset the sort and filter across all of the listing columns. This resets the listing to its last saved state.

To reset all columns, click Reset.