Data listings show the results of CQL (Clinical Query Language) queries in CDB. In addition to the source data of a listing, you can also view review statuses, queries, and context for each data point. CDB automatically creates a Core Listing for each Form (both EDC and third party) in your Study. You can create additional custom listings by modifying the CQL statement of another listing.
Types of Listings
There are three (3) types of listings:
- Core listings: These are the listings that CDB automatically creates when importing data. There is one core listing per Form.
- Custom listings: These are listings with custom CQL applied to display data in the way you want to view it. Learn how to create custom listings.
- Export listings: These are listings that Workbench creates as you add listings to an Export Definition and customize them for export. Note that these are only accessible from Export > Definitions, and not from the Listings area. Learn how to create an Export Definition.
First, select your Study from the Studies page. Then, open the Listings page either from the Navigation Drawer () or the Study menu.
The Listings page is divided into two sub-pages, Public Listings and Private Listings. Public Listings shows all listings that you have permission to view in your Study, both Core Listings and custom listings. Private Listings shows any private, custom listings that you’ve created.
Click on a listing’s Title to open it.
To view the clinical data returned in a listing, click on a listing’s Title to open it.
The Listing page’s header displays the Category and Associated Forms for a listing. The Category is defined on listing creation, and Vault generates the list of Associated Forms based on the Forms referenced in the CQL query’s in the
Each listing represents the results of a CQL query or set of queries. Workbench displays those results in the Datasheet of the listing. Depending on the size of the data set, you may have to scroll horizontally to view all data for a Subject row.
To set column widths:
Vault displays up to 50 rows per page in a listing. Use the left and right Arrow buttons to move between pages. To go directly to a page, enter the Page Number and press Enter.
You can use the Sort & Filter menu to sort by a column, or you can edit the listing’s CQL statement to add an
ORDER BY clause to sort results.
When reviewing data in a listing, you can quickly scan the listing and get key insights about missing data or outstanding queries with listing highlighting. You can hover over a highlighted cell to view the reason for missing data.
Workbench highlights rows and cells in the following cases:
Cell Details Panel
You can view contextual information and review statuses about individual EDC Items (from forms) in the Cell Details panel.
To open the Cell Details panel, click the Cell you want to view in the datasheet. To close the panel, click the Cell a second time.
The cell details panel shows the following information:
- Subject ID (
- Event & Event Date (
- Item Name (
In the Cell Details panel, you can check the following statuses. Workbench highlights the review statuses that are complete or applied:
You can use the CQL Editor to modify the data included in the datasheet. See details below.
Sorting & Filtering
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:
- Locate the column you want to sort by in the datasheet.
- If the column doesn’t already have a sort or filter applied, hover over the Column Header to show the Filter icon (filter_list).
- Click the Filter icon (ICON). This opens the Sort & Filter menu.
- Under Sort by, select Ascending (A to Z) or Descending (Z-A) for a sort order.
- 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.
- Optional: Save your listing to preserve your sort order.
ORDER BYclause in the listing’s CQL statement.
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, you must use the YYYY-mm-dd date format (2020-04-24) when entering Values for the conditional 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:
- Locate the column you want to filter by in the datasheet.
- Hover over the Column Header to show the Filter icon (filter_list).
- Click the Filter icon (filter_list). This opens the Sort & Filter menu.
- 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.
- 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.
WHEREclause of the last applied CQL statement.
Using the CQL Editor
You can use the CQL Editor to edit the CQL statement, changing which information is displayed in the listing, renaming columns, and more.
Data Workbench applies this statement to the listing by default:
select @HDR, * from EDC.Adverse_Events where @Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v')
To edit the CQL statement:
- Navigate to a Core Listing within your Study.
- Make your changes to the statement. See the CQL Reference for details about creating a CQL statement.
Click Apply. If there are no errors in your statement, Vault updates the listing to reflect the results of your statement. If there are errors, Vault displays them in a banner above the Query field. Resolve them, and then click Apply again.
- Click Close (X) to close the CQL Editor.
Export Listing Data to CSV
You can export your listing’s data to a CSV file for offline review or use in an external system. When Workbench exports listing data, it includes all data shown in the listing, according to the currently applied CQL statement.
To generate a CSV file of your listing:
- Open the listing.
- Workbench begins a job to create the CSV file. When finished, Workbench shows a notification with a link to download the CSV in the bottom-left corner of the screen. You can click this link to download the CSV file.
Once any user generates a CSV for a public listing, Workbench shows a download link for that CSV in the Latest Download column of the Listings page. Click the Download icon () to download the CSV file.
Data Listing Categories
Any listings that Workbench creates automatically during import from Vault EDC or 3rd party data import are assigned the Core Listings category. Any custom listings have a category assigned by the user who created the listing. Your organization can create these categories when creating custom listings. See details in Creating Custom Data Listings.