Using Listings

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.

Vault only displays restricted (blinded) data to users with the Restricted Data Access permission (typically lead data managers). This permission is granted by default to the standard CDMS Lead Data Manager study role. If you are a blinded user (you don’t have the Restricted Data Access permission), Workbench doesn’t display restricted Items in listings, or columns derived from restricted Items, Core Listings for restricted Forms, any @HDR, @Form, or @ItemGroup information for restricted Forms, or any data from restricted Sources.

Availability: In the current release, Vault Clinical DataBase (CDB) is only available to specific early adopter customers. Contact your Veeva Services representative for details.

Types of Listings

There are four (4) 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. System Listings are a specialized type of export listing that Workbench creates automatically. They only expose contextual study information, without any collected data. These listings are only available as part of raw-type export packages.
  • Review listings: These are listings that you can use to review data as it comes into Workbench, including the creation of known discrepancies. Learn how to review data with Review Listings.

Accessing Listings

First, select your Study from the Studies page. Then, open the Listings page either from the Navigation Drawer () or the Study menu.

Listings from the Navigation Drawer Listings from the Study menu

Workbench divides your study listings into four groups. To view listings in a certain group, click to open the appropriate tab.

Listing tabs

  • playlist_add_check Review: These are custom listings that are Review Enabled. These listings allow you to review incoming data and identify any issues. Learn more about reviewing data.
  • Core: Workbench automatically creates a Core Listing for each Form in your study as they are imported into the system.
  • public Public: These are all custom listings that you have permission to view. These include your own listings marked as public and any public listings from other users in your study.
  • lock_outline Private: These are all custom listings that you created and marked as private.

Click on a listing’s Title to open it.

Click the listing's Title to open it

You can access Export Listings (including System Listings) from Export > Definitions.

Search for a Listing

You can search for a specific listing from each of the All Listings page’s tabs. You can search in the Title, Category, Objective, and Description columns. This search uses “contains”.

To search:

  1. Navigate to a Listings page for your Study.
  2. Click Search. Search button

  3. Select a Column Name to search. Select a Column

  4. Enter your Search Text.
  5. Click Search or press Enter to search.
  6. Click a listings Title in the Results section to open it.

Workbench shows your search results in the Results section of the Search Listings panel. Workbench displays an icon with the listing that indicates the listing’s type:

  • playlist_add_check for Review Listings
  • for Core Listings
  • public for Public Listings
  • lock_outline for Private Listings

Click Close () to close the Search Listings panel.

Sort & Filter

You can sort and filter the Listings page by the following columns:

  • Title
  • Category
  • Objective
  • Created On
  • Created By
  • Modified On
  • Modified By
  • Last Uploaded

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

To sort the Listings page:

  1. Navigate to the Listings page for your Study.
  2. Locate the column you want to sort by.
  3. In that column, hover to show the Sort & Filter button.
  4. Click Sort & Filter (filter_list).
  5. Click to expand Sort by.
  6. Select Ascending or Descending for the sort order.

Sort Title Ascending

  1. Click OK.

How to Filter

To filter the Listings page:

  1. Navigate to the Listings page for your Study.
  2. Locate the column you want to sort by.
  3. In that column, hover to show the Sort & Filter button.
  4. Click Sort & Filter (filter_list).
  5. Click to expand Condition.
  6. 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.
  7. If required, enter a Value compare values against. Note that you can only use a static value and not a function. For dates, use the YYYY-MM-DD format.
  8. Click OK.

How to Reset a Filter

To reset (remove) a filter from a column, open the Sort & Filter menu and click Clear.

Viewing Listings

To view the clinical data returned in a listing, click on a listing’s Title to open it.

Listing Header

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 FROM clause.

Listing Header for a Dosing form

Datasheet

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.

Resizing Columns

To set column widths:

  1. Hover over the line between two columns.
  2. Click and drag the line to resize the columns. Screen recording showing the click and drag to resize listing columns

Pagination

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.

Sorting Results

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.

Highlighting

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:

  • The Event Date has an open or answered query:

Open Query highlight on an Event Date

  • The Event was marked as Did Not Occur:

Did Not Occur highlight

  • The Form was marked as Intentionally Left Blank:

Form Intentionally Left Blank highlight

  • The Item was marked as Intentionally Left Blank:

Item Intentionally Left Blank highlight

  • The Item has an open or answered query

Item Query highlight

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 (@HDR.Subject.Name)
  • Event & Event Date (@HDR.Event.Name, @HDR.Event.Date)
  • Item Name (EDC.Item)

In the Cell Details panel, you can check the following statuses. Workbench highlights the review statuses that are complete or applied:

  • SDV
  • DMR
  • Signed
  • Frozen
  • Locked

Cell Details panel

From the Cell Details panel, you can click to view a query in EDC’s Review tab. Click the Form Identifier link to view the query in EDC.

Known Issue: In the current release, Workbench doesn’t show the link to view queries on Event Dates in EDC.

Click the Form Identifier to open the query in EDC

CQL Editor

You can use the CQL Editor to modify the data included in the datasheet. See details below.

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.

Sort & Filter menu

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.

Known Issue: In the current release, you can’t sort by any columns within a repeating Item Group.

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 (ICON). 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.

Applying a sort order using the Sort & Filter menu automatically replaces the ORDER BY clause 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.

Reset the sort order

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. Filter condition, in enrolled__v

  6. Click OK. OK button

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 the sort order

If you use only the Sort & Filter menu to filter your listing, resetting removes all filters from that column. If you’ve used both the Sort & Filter menu and the CQL Editor to filter your listing, then Workbench resets your filters to the WHERE clause of the last applied CQL statement.

View CQL

You can view the CQL statement for a listing without opening that listing.

To view CQL:

  1. Navigate to the Listings page for your Study.
  2. Hover over the listing’s Title to show the Listing menu (). Listing menu

  3. From the Listing menu, select View CQL. View CQL action

  4. Workbench shows the CQL Statement dialog, titled with the listing’s Title. When finished, click Close (). CQL Statement dialog

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:

  1. Navigate to a Listing within your Study.
  2. Click CQL Editor. Vault opens the CQL Editor in the bottom half of your browser window. CQL Editor button

  3. Make your changes to the statement. See the CQL Reference for details about creating a CQL statement.
  4. 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. Apply button

  5. Optional: Click Reset to return the saved statement. Reset button

  6. Click Close (X) to close the CQL Editor.

You can save your result set as a custom data listing, allowing you to return to these results later and share them with other users in your organization. See details in Creating Custom Data Listings.

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:

  1. Open the listing.
  2. From the Listing menu, select Generate CSV. Generate CSV action

  3. 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. CSV generated notification

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.

Tooltip for Latest Download CSV

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.