Creating Views

You can create Views, which are virtual tables based on the result set of a CQL statement. A View can combine Forms to create View Listings. Each view contains rows and columns, where the fields are from one or more real tables in the study database. A View Listing always shows up-to-date data, decorations, and access to the associated queries. Each time you open a view, Workbench recreates the data, using the view’s CQL statement.

Some example use cases for views include:

  • Restricting Data Access: Views provide an additional level of table security by restricting access to a predetermined set of rows and columns.
  • Hiding Data Complexity: A view can hide the complexity that exists in a multi-table join.
  • Rename Columns: Users can rename a column within the context of the View, without affecting the base tables.
  • Store Complex Queries: Users can store more complex CQL in Views.
  • Simplify Commands for the User: Users can select information from multiple tables without needing to know how to actually perform a join.
  • Multiple View Facilities: Workbench supports different views created on the same table for different users.

Prerequisites

Users with the standard CDMS Super User study role can perform the actions described below by default. If your vault uses custom Study Roles, your role must grant the following permissions:

Type Permission Label Controls
Functional Permission Workbench Tab

Ability to access and use the Data Workbench application, via the Workbench tab

Functional Permission Browse View

Ability to access the Views tab within Workbench and browse Views. Ability to save a View as a Check

Functional Permission Create View

Ability to create new Views in Workbench

Functional Permission Modify View

Ability to edit (modify) existing Views in Workbench

Functional Permission Delete View

Ability to delete Views in Workbench

If your Study contains restricted data, you must have the Restricted Data Access permission to view it.

Learn more about Study Roles.


Accessing Views

You can access views from the Views area of CDB. You can navigate to the Views area from the Navigation Drawer () or, after you select your Study, from the Study menu () on the Studies page.

How to Open a View

From the Views page, you can click a view’s Title to open the View. You can also open a View from the View menu:

  1. Navigate to Views for your Study.
  2. Locate the View that you want to open in the list.
  3. Hover over the Title to show the View menu.
  4. From the View menu, select Open.

Search for a View

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

To search:

  1. Navigate to Views for your Study.
  2. Click Search.
  3. Select a Column Name to search.
  4. Enter your Search Text.
  5. Click Search or press Enter to search.
  6. Click a view’s Title in the Results section to open it.
  7. Click Close (X) to close the Search Views panel.

Sort & Filter

You can sort and filter the Views 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 Views page:

  1. Navigate to the Views 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 Views page:

  1. Navigate to the Views 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.

Hide View Columns

You can hide and show columns as needed without removing them from your view 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 view.
  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.

Show the CQL for a View

To show the CQL for a view:

  1. Navigate to Views for your Study.
  2. Locate the View that you want to check in the list.
  3. Hover over the Title to show the View menu.
  4. From the View menu, select Show CQL.

How to Create a View

To create a new View:

  1. Navigate to the listing from which you want to create a View.
  2. From the Listing menu (), select Save as View.
    Save as View
  3. Enter a Title. The title can’t contain any spaces. Note that this value must be unique within the Study.
  4. Optional: Enter a Short Title.
  5. Optional: Enter a Description.
  6. Click Save.

CQL Limitations when Defining Views

The following CQL functionality isn’t supported when defining a View:

  • select *
  • UnPivot()
  • Duplicate column aliases
  • @Form attributes other than @Form.SeqNbr in the projection
  • @ItemGroup attributes other than @ItemGroup.SeqNbr in the projection

CQL Limitations when Referencing Views

The following are limitations when referencing a View in a custom listing:

  • Referencing a View inside another View isn’t supported.
  • Inclusion of a view’s @Form and @ItemGroup attributes in the projection or filter (WHERE clause) isn’t supported.
  • If using On Subject ALIGN, the View must not use UNALIGN.
  • When joining a View with another Form or View using On Subject, the View must use On Subject.

Pin Columns in a View

You can pin columns in your view so that those columns remain visible even when you scroll to the right.

To pin columns:

  1. Navigate to the View you want to modify.
  2. Click push_pin Columns.
  3. Select a Number of columns to pin. This is a count of columns from the left, the leftmost column being 1.
  4. 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.

How to Edit a View

Properties

To edit a view’s properties:

  1. Navigate to the View you want to edit.
  2. From the View menu, select Properties.
  3. In the Properties dialog, click Edit.
  4. Make your changes.
  5. Click Save.

CQL Statement

To edit the CQL statement for a view:

  1. Navigate to the View you want to edit.
  2. Click CQL Editor. Workbench 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.
    Apply button
  5. Optional: Click Reset to return the saved statement.
    Reset button
  6. Click Close (X) to close the CQL Editor.
  7. To save the changes to your View, click Save.
  8. In the confirmation dialog, click Save.

Copy a View with Save As

You can create a copy of a View using the Save As option.

To copy a View:

  1. Navigate to the View you want to copy.
  2. From the View () menu, select Save As.
  3. Enter a Title for your new view.
  4. Enter a Short Title.
  5. Optional: Enter a Description.
  6. Click Save.

How to Delete a View

You can delete views. If you delete a view, any custom listings or export definitions that reference the deleted View are marked as Invalid.

To delete a view:

  1. Navigate to the Views tab.
  2. Locate the View you want to delete in the list.
  3. Hover over the Title to show the View menu ().
  4. From the** View** menu, select Delete.
    Delete View
  5. In the confirmation dialog, click Delete.

Example: Simply CQL for Two Adverse Event Forms

If your Study uses two different Adverse Event forms (Adverse_Event and Serious_Adverse_Event), you could use UnionAll() to create a Custom Listing showing data from both forms.

select @HDR.Site.Number as `Site Number`
     , @HDR.Subject.Name as `Subject ID`
     , @HDR.Event.Name as `Event Name`
     , AETERM as AE_1
     , AESEV as AE_2
     , AESDTC as AE_3
from Adverse_Event as AE,
(select distinct @HDR.Subject.Name as SubjNamne, @Form.SeqNbr as AEID, AETERM)
from AE3001_LV1
WHERE (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v')) AND AETERM is not NULL) as AE

union all

select @HDR.Site.Number as `Site Number`
     , @HDR.Subject.Name as `Subject ID`
     , @HDR.Event.Name as `Event Name`
     , AETERM as AE_1
     , AESEV as AE_2
     , AESDTC as AE_3
from Adverse_Event as SAE,
(select distinct @HDR.Subject.Name as SubjNamne, @Form.SeqNbr as AEID, AETERM)
from AE3001_LV1
WHERE (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v')) AND AETERM is not NULL) as SAE

However, this is a complicated CQL statement and may be difficult for users to work with. You can create a View based on this listing (View_AE), and then modify the Custom Listing that references the View as the source, instead of both Forms. This significantly shortens the CQL statement.

View_AE CQL:

select @HDR.Site.Number as `Site Number`
     , @HDR.Subject.Name as `Subject ID`
     , @HDR.Event.Name as `Event Name`
     , AETERM as AE_1
     , AESEV as AE_2
     , AESDTC as AE_3
from Adverse_Event as AE,
WHERE (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v')

union all

select @HDR.Site.Number as `Site Number`
     , @HDR.Subject.Name as `Subject ID`
     , @HDR.Event.Name as `Event Name`
     , AETERM as AE_1
     , AESEV as AE_2
     , AESDTC as AE_3
from Adverse_Event as SAE,
WHERE (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v') 

Custom Listing Referencing View_AE:

select V_AE.`Site Number`
     , V_AE.`Subject ID`
     , V_AE.`Event Name`
     , V_AE.`AE_1`
     , V_AE.`AE_2`
     , V_AE.`AE_3`
from View_AE as V_AE,
(select distinct @HDR.Subject.Name as `Subject Name`, @Form.SeqNbr as AEID, AETERM
from AE3001_LV1
WHERE (@Form.Status = 'submitted__v' or @HDR.Event.Status IN ('did_not_occur__v')) AND AETERM is not NULL) as AE
WHERE V_AE.@HDR.Subject.Name = AE.@HDR.Subject.Name