Creating Listings, Checks & Views with the Listing Builder

With the Listing Builder, you can create listings, checks, and views in a UI interface, instead of creating them with CQL. The method is the same for all three objects.

Create a Listing, Check, or View

To create a listing, check, or view:

  1. Navigate to the Workbench tab.
  2. On the Studies listing, click your Study to open it.
  3. From the Create New () menu in the top navigation bar, select the object you want to create: Listing, Check, or View.

  4. This opens the Listing Builder. Follow the instructions on this page to select the data to display, define the structure of your listing, and configure your display options.

Select Properties & Items to Display

The first stage of listing creation is to select those properties and items to display.

For a complete list of properties, see the CQL Reference.

Properties are divided into two groups: Study and Items. Study properties are operational projection elements (@HDR properties) and form or item group properties (@Form and @ItemGroup). Items are data collection Items from individual Forms.

How to Add Properties

Drag and drop properties from Available Properties to Selected Properties to add them to your listing. You will be able to reorder these properties in a later stage. As you add properties, CDB will show a count of properties added in the Listing Builder Navigation.

Study properties are grouped by object: Study, Site, Subject, EventGroup, Event, Form, and Item Group. You can drag over an Object to add all of its properties, or you can click to expand the object and drag individual properties.

Listings automatically include certain study properties. These defaults are defined by administrators in the Core Data Listings configuration settings.

Item properties are grouped by form and view. You can drag and drop a Form to add all of its items, and you can drag and drop a View to add all of its columns. You can also search for and select individual Items using Search for properties.

Enter your item’s Name in the Search for properties field. CDB searches as you type.

When you search for an Item with Forms selected, CDB searches for Items on Forms. When you search for an Item with Views selected, CDB searches for Items on Views. If the same Item is used in multiple locations, CDB also displays the Item Group for the Item in the search results.

Form Limit: You can only select Items from up to 10 (ten) Forms.

Once you add an Item, you can expand it in the Selected Properties area to show additional options. These add additional columns for the Item based on the item’s data type, such as additional date formats or operational details. Certain columns are included by default. These defaults are defined by administrators in the Core Data Listings configuration settings. In the example below, CDB will include a column for both the Imputed Date and the SDTM Date for the aestart item.

How to Remove Properties

You can remove properties by clicking Remove () in the Selected Properties area.

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.

Define the Structure of the Listing

In the Structure stage, you can define structural options for your rows and columns.

Row Structure

Selecting the row structure enables you to choose whether or not to align the data dependent or independent of the study schedule. There are two options available:

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

For example, a listing includes Items from two Events: Visit 1 (Gender item) and Visit 2 (Test Result). If you choose By Subject for row structure, the listing will have one row per subject, displaying each Item as a column in the listing with the values from the appropriate event populated. If you choose By Schedule for row structure, CDB considers the study schedule having two events, and so creates a row for each Event. For the row where the Event doesn’t include an Item, CDB will have a null value (“–”) in that item’s column.

Column Structure

Defining the column structure enables you to combine for instance data from different sources into stacked columns lending to a more narrow readable Listing. There are two options available:

  • Wide (Side by Side): 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.

Creating Stacks

When you choose Stacked (Union) for row structure, you can stack multiple Items or Item Properties together.

  1. For Column Structure, select Stacked (Union).
  2. Select Columns to stack. These columns must use the same data type.
  3. Optional: To add additional rows to your stacks, click Stack. To remove a row, click Delete.

You can include up to five (5) stacks.

Listing Builder: Setting Listing Structure

Last Updated: 23R3

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

Configure the Display Options

There are three configurable display options: Aliases, Arrange, and Sort & Filter.

Aliases

You can define aliases (column headers) in the Aliases step. This is the same as using AS in CQL. Enter a value for Column Alias in for each column that you want to rename. By default, stacked columns use the name of the top item, so we recommend using an alias that describes the entire stack.

Alias Uniqueness: An alias must be unique at the listing level.

Arrange

In the Arrange step, you can reorder your columns and stacks. Drag and drop columns and stacks into the desired order. You can also use the Move to Top, Move Up, Move Down, and Move to Bottom buttons to move columns and stacks into the desired position.

Sort & Filter

How to Sort

You can sort your listing by multiple columns [Q: How is order determined?]

  1. Locate the column you want to sort by.
  2. In that column, hover to show the Sort & Filter button.
  3. Click Sort & Filter.
  4. Click to expand Sort by.
  5. Select Ascending or Descending for the sort order.

To reset (remove) a sort order from a column, open the Sort & Filter menu, expand Sort by, and click Reset.

How to Filter

You can apply filters to your columns to limit the data returned. Note that these are AND filters.

  1. Locate the column you want to sort by.
  2. In that column, hover to show the Sort & Filter button.
  3. Click Sort & Filter.
  4. Click to expand Condition.
  5. 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.
  6. 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.
  7. You can also filter by comparisons. After selecting an Operator, click Compare. Then, you can select from the available columns.

  1. Click OK.

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

Preview the Listing

You can preview your listing to check your configuration prior to saving. Click Preview. This opens a preview listing in a new window with up to 25 records from your Study.

You can keep this preview window open while making changes to your listing. To update the preview with your changes, click Refresh in the preview window.

Validate & Save

Once you have finished your listing configuration, click Validate and Save. First, CDB will check your listing, check, or view for any errors. If there are no errors, CDB will open a dialog for you to define listing properties and save.

Workbench will show a Warning icon in the Listing Builder Navigation for each step that contains a validation error. You can hover over the icon to view the error message.

Listing Builder: Validations

Last Updated: 23R3

This video reviews validations in the Listing Builder.

How to Modify via the Listing Builder

You can use the Listing Builder to modify your listings, checks, and views even after saving. To do so, navigate to the listing, check, or view, and click Modify.

Modify via Listing Builder: You can only use the Listing Builder to modify listings, checks, and views that you created in the Listing Builder. For any listings created via the CQL Editor, you must use the CQL Editor to modify.