Sort & Filter Listing Data

Veeva CDB enables you to quickly and easily sort and filter listing data by 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 CQL statement applied to that listing. After you sort and filter your listing, you can save it and 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 order or filter applied, Workbench displays 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 must 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 filter your listing using the Sort & Filter menu. To filter, you select a comparison operator and, if required, select or enter a value to filter by.

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.

Available Filters

The following filters are available depending on the data type of the column:

Filter	Data types	Explanation	CQL Operator or Function
Is exact match	String	Returns all records that match the entered string.	=
Does not match	String	Returns all records that do not match the entered string.	<>
Is empty	All	Returns records that do not contain any data in the field.	IS NULL
Is not empty	All	Returns records that contain data in the field.	IS NOT NULL
Is one of selected	All except date and datetime items	(For non-date and datetime items) Returns records that match the selections. When selected, all existing unique values are displayed. Select one or more checkboxes to filter records. Can be used for global fields (Subject Status, Country, Site Number, Site Name) and header picklists regardless of record count, and for all columns when fewer than 1,000 records are displayed.	IN()
Not in	String, number	Enter one or more comma delineated numbers or strings. Returns records that do not contain any of the entered numbers or strings. Case sensitive.	((`Item` IS NULL) OR (NOT (`Item` IN ())))
Starts with	String	Returns records that start with the entered string.	REGEXP '^[uncapitalized|Capitalized]{1}'
Ends with	String	Returns records that end with the entered string.	REGEXP '[uncapitalized|Capitalized]{1}$'
Contains	String	Returns records that contain the entered string.	REGEXP '[uncapitalized|Capitalized]{1}'
Does not contain	String	Returns records that do not contain the entered string.	(NOT `Item` REGEXP '[uncapitalized|Capitalized]{1}')
Is equal to	Number	Returns records that equal the entered number	=
Is not equal to	Number	Returns records that do not equal the entered number	<>
Is greater than	Number	Returns records that are greater than the entered number	>
Is less than	Number	Returns records that are less than the entered number	<
Is greater than or equal to	Number	Returns records that are greater than or equal to the entered number	>=
Is less than or equal to	Number	Returns records that are less than or equal to the entered number	<=
Is between	Number, date	Returns records that fall within the selected range.	`Item` BETWEEN 'Lower' AND 'Upper')
Is on	Date	Returns records that match the selected date.	=
Is not on	Date	Returns records with dates that do not match the selected date.	<>
Is after	Date	Returns records with later dates than the selected date.	>
Is before	Date	Returns records with dates that are before the selected date.	<
Is after or on	Date	Returns records with dates that are later than or match the selected date.	>=
Is before or on	Date	Returns records with dates that are before or match the selected date.	<=