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.

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, 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 view Title in the Results section to open it.

Workbench shows your search results in the Results section of the Search panel.

Click Close () to close the Search 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 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 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

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 view. You can drag a group of properties, or click Expand to choose individual properties.
4. Click Remove () to remove a property from your view.
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 view. 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 view.
8. Click Arrange in the left-hand Listing Builder menu.
9. Drag and drop properties, forms, and items to reorder them.
10. Click Rows in the left-hand Listing Builder menu.
11. 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.
12. Click Columns in the left-hand Listing Builder menu.
13. 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.
14. Click Aliases in the left-hand Listing Builder menu.
15. Enter a Column Alias for any columns that you want to use non-default headers.
16. Click Sort & Filter in the left-hand Listing Builder menu.
17. Use the Sort & Filter menu to apply sort orders and filters to any columns.
18. Optional: Click Preview to preview your view.
19. Click Validate and Save.
20. Enter a Title for your view. Note that this Title must be unique within your Study.
21. Enter a Short Title.
22. Choose a Category from your view.
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. Enter a Description.
25. Click Save. Workbench saves your view and opens it.

How to Create a View with CQL

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.

   

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

If you created your view 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 view’s CQL or its properties.

To modify a view in the Listing Builder:

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

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.

   

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

   

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.

   

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