Creating Custom Listings

Workbench uses data listings to generate complex clinical data reports across a study. You can create a CQL query to return the clinical data you want to review, view the results, and save that query as a custom listing. You can also assign a category to a custom listing to categorize it according to your organization’s business practices. You can choose to share their listing with others in your organization, or mark it as a private listing.

Availability: Clinical DataBase (CDB) is only available to CDB license holders. Contact your Veeva Services representative for details.

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, which are not review enabled, that you have permission to view. These include your own listings marked as public and any public listings from other users in your study. Workbench displays custom listings that are review enabled in the Review group.
  • 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.

Creating a Custom Listing

  1. Navigate to your Study in Workbench.
  2. From the Create New menu, select Listing.
  3. Drag and drop Properties from Available Properties to Selected Properties to add them to your listing. You can drag a group of properties, or click Expand to choose individual properties.
  4. Click Remove () to remove a property from your listing.
  5. Click Items in the left-hand Listing Builder menu.
  6. Drag and drop Forms from Available Properties to Selected Properties to add them to your listing. You can drag an entire Form, or click Expand to choose individual Items on that Form.
  7. Click Remove () to remove an item from your listing.
  8. Click Arrange in the left-hand Listing Builder menu.
  9. Drag and drop properties, forms, and items to reorder them.
  10. To add Views to your listing, click Views and repeat steps 7 through 9.
  11. Click Rows in the left-hand Listing Builder menu.
  12. Select a Row Structure:
    • By Subject: This option attempts to display data from multiple forms across events in a single row for the subject, independent of the study schedule, and displays all data where subjects match across events. Any selected schedule-related fields, such as Event Date or Event Name are set to null in the listing.
    • By Schedule: This option displays form data from different events on separate rows. We recommend this option if schedule-related fields are required. This is the default option for all core listings.
  13. Click Columns in the left-hand Listing Builder menu.
  14. Select a Column Structure:
    • Wide (Side by Site): With this option, each column represents a single unique item or item property.
    • Stacked (Union): With this option, each column can contain multiple items or item properties stacked together, where the user can include up to five (5) items and/or item properties per column.
  15. Click Aliases in the left-hand Listing Builder menu.
  16. Enter a Column Alias for any columns that you want to use non-default headers.
  17. Click Sort & Filter in the left-hand Listing Builder menu.
  18. Use the Sort & Filter menu to apply sort orders and filters to any columns.
  19. Optional: Click Preview to preview your listing. This opens a preview of your listing in a new window.
  20. Click Validate and Save.
  21. Enter a Title for your listing. Note that this Title must be unique within your Study.

  22. Choose a Category from your listing. Add Category option

  23. Optional: To create a new category, clear the Category field’s text and enter your category name. If a category with the same name doesn’t already exist, press Enter or select Add Category from the drop-down.

  24. Optional: By default, your listing is private, meaning that only you can see it. To create a public listing, select Public for Listing Access. Public listings are visible to anyone with access to Workbench and your Study. Otherwise, leave this set to Private. Choose Public or Private for Listing Access

  25. Optional: Select Enabled for Review to enable this listing for review. Otherwise, leave this set to Disabled to create a regular public or private listing.
  26. Enter a Description.
  27. Click Save. Workbench saves your custom listing and opens it.

Listing Builder: Selecting Items and Properties

Last Updated: 23R3

This video demonstrates how to select Items and Properties when building a listing, check, or view in the Listing Builder.

Listing Builder: Setting Listing Structure

Last Updated: 23R3

This video demonstrates how to set listing structure in the Listing Builder.

Listing Builder: Validations

Last Updated: 23R3

This video reviews validations in the Listing Builder.

Creating a Custom Listing with CQL

To create a custom listing with the CQL Editor:

  1. Navigate to your Study in Workbench.
  2. Open the Listings page from either the Study menu or the Navigation Drawer ().
  3. Choose a Core Listing, or an existing custom listing, to start from.
  4. Click that listing’s Title to open it.
  5. Click CQL Editor.
  6. Edit the CQL query to show the desired data in the listing.
  7. Click Apply.
  8. Review the listing. If needed, edit the CQL query again and reapply until you’re satisfied with the data shown in the listing.

  9. From the Listing Menu, choose Save As. Save As menu option

  10. Enter a Title for your listing. Note that this Title must be unique within your Study.
  11. Choose a Category from your listing.

  12. Optional: To create a new category, clear the Category field’s text and enter your category name. If a category with the same name doesn’t already exist, press Enter or select Add Category from the drop-down. Add Category option

  13. Optional: By default, your listing is private, meaning that only you can see it. To create a public listing, select Public for Listing Access. Public listings are visible to anyone with access to Workbench and your Study. Otherwise, leave this set to Private. Choose Public or Private for Listing Access

  14. Optional: Select Enabled for Review to enable this listing for review. Otherwise, leave this set to Disabled to create a regular public or private listing.
  15. Enter a Description.
  16. Click Save. Workbench saves your custom listing and opens it.

Copy a Single Listing into a Study

You can copy a single Listing Definition into another Study.

To copy a Listing:

  1. Open the Listing that you want to copy.

  2. From the Listing () menu, select Copy to.

  3. In the Copy to Study dialog, select a Study.
  4. Click Copy.
  5. In the Save Listing As dialog, modify the properties as needed.
  6. Click Save.

If there are any study design differences between the two Studies, CDB may mark your listing as invalid. Use the CQL Editor or the Listing Builder to revise the listing until all errors are resolved and the listing is valid.

Editing a Custom Listing

If you created your listing in the Listing Builder, you can edit it with the Listing Builder or by modifying its CQL statement in the CQL Editor. Otherwise, you can only edit the listing’s CQL or its properties.

To modify a listing in the Listing Builder:

  1. Navigate to your Study in Workbench.
  2. Open the Listings page from either the Study menu or the Navigation Drawer.
  3. Locate the listing you want to edit.
  4. Click the listing’s Title to open it.
  5. Click Modify.
  6. Make your changes.
  7. Optional: Click Preview to preview your listing. This opens a preview of your listing in a new window.
  8. Click Validate and Save.

To edit a custom listing’s CQL query:

  1. Navigate to your Study in Workbench.
  2. Open the Listings page from either the Study menu or the Navigation Drawer.
  3. Locate the listing you want to edit.
  4. Click the listing’s Title to open it.
  5. Click CQL Editor.
  6. Edit the CQL query.
  7. From the Listings menu, select Save.

To edit a custom listing’s properties:

  1. Navigate to your Study in Workbench.
  2. Open the Listings page from either the Study menu or the Navigation Drawer.
  3. Locate the listing you want to edit.
  4. Click the listing’s Title to open it.
  5. Click the Arrow () to open the Listing menu.
  6. Select Properties.
  7. In the Properties dialog, click Edit.
  8. Make your changes.
  9. Click Save.

Invalid Listings: You can’t edit the properties of a listing that is invalid. To edit the properties, update the listing’s CQL statement so that it is valid.

Listing Builder: Modify Listings

Last Updated: 23R3

This video demonstrates how to modify exisitng listings with the Listing Builder.

Deleting a Custom Listing

You can delete custom listings that you created. Note that you can’t delete Core Listings, or any custom listings that another user created.

To delete a custom listing:

  1. Navigate to your Study in Workbench.
  2. Open the Listings page from either the Study menu or the Navigation Drawer ().
  3. Locate the listing you want to delete.
  4. Hover over the Title of that listing to show the Listing Menu.

Listing menu

  1. Click the Arrow () to open the Listing menu.
  2. Select Delete Listing.

Delete Listing menu option

  1. Workbench deletes your custom listing. If your listing was the only one in its category, Workbench also deletes the category.

Example Custom Listings

The examples below are custom listings for Verteo Pharma’s Deetoza study.

Note that these examples use a fictional study, Deetoza. To recreate any examples in your own study, update any references to Items, Item Groups, Forms, and Events to point to definitions that exist in your own study.

Example: Dosage

The example listing below is “Dosage”. This listing shows the following items from the Dosing form: Actual Dose, Units, Route, and Frequency. Because the Deetoza study reuses one Dosing form definition for each Treatment Visit event, the listing also shows the Name of the event and the Event Date. This means that the listing includes a row for each treatment visit completed by each subject.

This listing excludes any forms not in the Submitted status or any forms within an Event marked as Did Not Occur.

This example listing is in the Analysis category. Verteo Pharma categorizes any listings used for analysis with this category.

Dosage custom listing

The Dosage custom listing uses the following CQL query:

select @HDR.Subject, @HDR.Event.Name as Event, @HDR.Event.Date, dosing.start_date_and_time as Start_DateTime, dosing.stop_date_and_time as Stop_DateTime, dosing.actual_dose as Actual_Dose, dosing.dose_units as Units, dosing.dose_route as Route, dosing.dose_frequency as Frequency 
from EDC.Dosing
where @Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v')

This query uses aliases (as) to shorten column names. Learn more about aliases in the CQL Reference.

Example: Subject Gender Report

The example listing below is “Subject Gender Report”. This listing shows the percentage of Subjects by gender, based on the Subject Gender (subject_gender) item from the Demographics form. The query uses the Round() and Count() functions and arithmetic operators to calculate the percentage of the total number of Subjects that make up each gender.

This Subject Gender Report listing uses the Reports category. Verteo Pharma categorizes any listings meant for reporting within this category.

This listing excludes any Subjects with their Demographics form not in the Submitted status or any within a Screening event marked as Did Not Occur.

Subject Gender Report listing

The Subject Gender Report custom listing uses the following CQL query:

select `subject_gender` as Gender, Count(@HDR.Subject.Name) as CNT,
(Round(Count(@HDR.Subject.Name)/(Select Count(@HDR.Subject.Name) from EDC.demographics ttl_subj)*100,2)) as Percentage
from EDC.demographics
where @Form.Status = 'submitted__v' or @HDR.Event.Status in ('did_not_occur__v')
group by `subject_gender`
order by Percentage desc

Download a Listing Definition

You can download the LIsting Definition (CQL statement) as a text (.txt) file. CDB names the file “Vault ID-Study ID <Listing Name> Definition datetime.txt”.

To download the Listing Definition:

  1. Open the Listing that you want to download.

  2. From the Listing () menu, select Download Definition.

  3. Save the file and open it in your preferred text editor.

Copy a Single Listing into a Study

You can copy a single Listing Definition into another Study.

To copy a Listing:

  1. Open the Listing that you want to copy.

  2. From the Listing () menu, select Copy to.

  3. In the Copy to Study dialog, select a Study.
  4. Click Copy.
  5. In the Save Listing As dialog, modify the properties as needed.
  6. Click Save.

If there are any study design differences between the two Studies, CDB may mark your listing as invalid. Use the CQL Editor or the Listing Builder to revise the CQL statement until all errors are resolved and the listing is valid.