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.

CDB 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.

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.

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

• 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.

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

Search for a Listing

You can search for a specific listing, using the Title, Category, Objective, Description, and Source columns. This search uses “contains”.

To search:

1. Navigate to your Study in Workbench.

2. In the top navigation bar, click Search ().

3. By default, Workbench searches across all object types. You can select and clear checkboxes in the Type drop-down to change which objects Workbench is searching.
   

4. Select the Column to search. The default is Title.
5. Enter your Search Text.
6. Click Search or press Enter to search.
7. Click the listing Title in the Results section to open it.

Workbench shows your search results in the Results section of the Search 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 panel.

Last Listing Visited

For convenience, Clinical Reporting shows a link to the Last Listing Visited. You can click this link to easily navigate to the last Listing you visited.

Favorite Listings

You can mark listings as favorites to easily access them later from the Favorites menu.

To mark a listing as a favorite:

1. Navigate to the listing that you want to favorite.

2. Click Add to Favorites ().

3. You can now access it from the Favorites menu ().

To remove it from your favorites, click Remove from Favorites (), or click Remove () for it in the Favorites menu.

To access your favorites, click to open the Favorites menu () in the top navigation bar after selecting a Study.

You can click the linked Object Name to open that listing.

You can filter the list of objects by type.

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
• Current Status (TST environments only)
• Modified In (TST environments only)

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.

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 or use the calendar picker.
8. You can also filter by comparisons. After selecting an Operator, click Compare (). Then, you can select from the available columns.

1. Click OK.

How to Reset a Filter

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

Hide Listing Columns

You can hide and show columns as needed without removing them from your listing using the Hide Columns option.

This setting persists across the object and Study until you unhide the columns.

CDB represents hidden columns with an orange, dotted line. CDB shows one dotted line for each set of hidden columns (columns next to each other that are all hidden).

To hide a column:

1. Navigate to your listing.
2. Hover over the Column Header to show the Sort & Filter menu (filter_list).

3. Click Hide Column.

4. CDB hides the column. Hidden columns are indicated by a dotted orange line. Click Unhide Columns to show all hidden columns.

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, Objective, and Associated Forms for a listing. The Category and Objective are defined during listing creation, and Workbench generates the list of Associated Forms based on the Forms referenced in the CQL query’s in the FROM clause. The header also displays the date the listing was last modified for all listings. Other dates displayed in the header include the following:

• Last Applied Configuration on [Date]: For core listings that have been configured, the date the configuration was last applied.
• Review Listing last refreshed on [Date]: For review listings, the date the listing was last refreshed.
• Last reviewed on [Date]: For review listings, the date the listing was last reviewed.
• Last deployed on [Date]: For custom listings, the date the listing was last deployed.

Configured core listings will display a Configured pill that indicates the core listing has a customized set of columns

Expand & Collapse the Grid

You can hide the header to see more of the grid. To do so, click Expand Grid. To show the header again, click Collapse Grid.

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. If your listing returns more than 100 result rows, you can page through the result set to view additional results.

Resizing Columns

To set column widths:

1. Hover over the line between two columns.
2. Click and drag the line to resize the columns.

Pagination

Workbench displays up to 100 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.

A listings can show a maximum of 1000 rows in the Workbench UI. If the CQL statement returns more than 1000 result rows, the pagination menu shows 1000+. You can apply filters to the listing to view additional rows.

If there are no filters applied to the listing, the CSV download will include all records.

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:

• The Event was marked as Did Not Occur:

• The Form was marked as Intentionally Left Blank:

• The Item was marked as Intentionally Left Blank:

• The Item has an open or answered query

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

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.

CQL Editor

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

Pin Columns

You can pin columns so that those columns remain visible even when you scroll to the right. By default, the Review column is always pinned.

To pin columns:

1. Click push_pin Columns.
2. Select a Number of columns to pin. This is a count of columns from the left, the leftmost column being 1.
3. Click outside of the Pin Columns menu to close it.

Workbench pins the columns, showing a bolded border on the right side of the rightmost pinned column.

Workbench keeps your pins across sessions. Pinned columns don’t apply to other users.

If a listing is modified with changes to the CQL’s projection (changing which columns are included in the listing), Workbench removes your pinned columns (resets the pin to “1”).

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.

1. 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.

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

3. From the Listing menu, select View CQL.

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

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')

You can drag the top (orange) edge of the CQL Editor to change the size of the panel. You can also open the CQL Editor in a separate window. Click Pop Out () in the CQL Editor.

To edit the CQL statement:

1. Navigate to a Listing within your Study.

2. Click CQL Editor. CDB opens the CQL Editor in the bottom half of your browser window.

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, CDB updates the listing to reflect the results of your statement. If there are errors, CDB displays them in a banner above the Query field. Resolve them, and then click Apply again.

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

6. 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:

1. Open the listing.

2. From the Listing menu, select Generate CSV.

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.

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.