2 View data listings and create discrepancies

• Choose a listing type and view data
  
• Search for data
  
• Review data
  
• Create discrepancies
  
• Export data to Excel or CSV
  
• Create a custom listing
  
• Copy a custom listing
  
• Use the Expression Builder
  
• FAQs
  

Choose a listing type and view data

You can choose different methods to view data and show records. For example, you can view any of the following:

• Default listings to show the current data in a table
• Validation check listings to show records that include a discrepancy identified by a validation check
• Custom listings to show records that satisfy criteria that you or others set

See the following procedures for details.

• Default listings
  Default listings shows the current data in a table.
• Validation check listings
  Validation check listings show records that include a discrepancy identified by a validation check.
• Custom listings
  Custom listings show records that satisfy criteria that you or others set.

Default listings

Default listings shows the current data in a table.

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. Under Default Listings, select a listing.

   To narrow the choice you can enter part of a listing (table) name in the Search Default Listings field and press Enter.

Validation check listings

Validation check listings show records that include a discrepancy identified by a validation check.

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. Select Validation Check Listings from the bottom of the left pane.

   Tip:

   You can choose to show disabled listings. Listings are disabled if a table they read from is not used in the study.

6. Expand the node of a validation check batch.
7. Select one validation check in the batch to see:

   • All records with discrepancies identified by the validation check.

   • Any other discrepancies on the records.

   If a data item has been fixed and the discrepancy closed, the record is not displayed.

   Tip:

   You may need to scroll down to see Validation Check Listings in the left pane.

Validation checks run in batches as configured in the Home page.

Custom listings

Custom listings show records that satisfy criteria that you or others set.

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. Select Custom Listings at the bottom of the left pane.
6. Do one of the following:

   • To view a saved listing, select it in the Custom Listings pane. The current data appears on the right.

   • To copy a saved custom listing, click the More Actions icon in the Custom pane, then click Copy Custom Listings. You can modify the copy.

   • To create your own listing, click the Add icon. See "Create a custom listing".

     Tip:

     • You can choose to show disabled listings. Listings are disabled if a table they read from is not used in the study.
     • You may need to scroll down to see Custom Listings in the left pane.
     • To view deleted InForm data create a custom listing but do not install it. The system displays all records that meet the criteria, including deleted records. After you install it the deleted rows are not displayed.

Search for data

You can choose different methods to search for data. For example, you can use the following:

• Find feature to search for column values
• Filters to find data and discrepancies, save filters and filter groups, and use public filters

See the following procedure for details.

• Use the Find feature
  

Use the Find feature

Use the Find feature to search on column values in the current records.

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. In the left panel, select Default, Validation Check, or Custom and then select a listing.
6. From the Filters drop-down list, select Find.
7. To find records that satisfy at least one Find criterion, select Any.

   To find records that satisfy all Find criteria, select All.

8. Enter or select column values to search for.
9. To use number, character, or date operators on each column, click Advanced.

   To add criteria to a field already used, click Add Fields.

10. Click Search.

Review data

• View discrepancies in the Listings page
  
• Show all discrepancies on one data item
  
• View discrepancy details
  
• Assign and remove flags
  
• Show flags assigned to a record
  
• View data lineage
  
• View data in InForm or another system
  
• Reprocess discrepancies that failed to be sent to InForm
  

View discrepancies in the Listings page

Discrepancy Indicators: Records with one or more discrepancies have a yellow rectangle on the left and the word Yes in the DISCREPANCY_EXISTS column.

Data items with discrepancies are highlighted in yellow. You may need to scroll to the right to see them.

State Icons: Hover over the icon to see the discrepancy's state. If a data item has multiple discrepancies, the system displays the state icon for the discrepancy that requires the most work to resolve. In order: Open, Candidate, Answered, Cancelled, Closed.

Discrepancy Filter Indicator: If you have applied a discrepancy-type filter, data items with discrepancies that meet the filter criteria have a Star icon.

Show all discrepancies on one data item

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. In the left panel, select Default, Validation Check, or Custom and then select a listing.
6. Select the data item.
7. Right-click and select Show All Discrepancies.

   Tip:

   To view details or act on one of the discrepancies, right-click the data item, and click Go to Discrepancies.

View discrepancy details

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. In the left panel, select Default, Validation Check, or Custom and then select a listing.
6. Select one or more data items with a discrepancy.
7. Right-click and select Go to Discrepancies.
8. Select one discrepancy and see its details in the lower panes.

Assign and remove flags

Use flags to help track and move the data review process. You can filter by flag in the Listings and Discrepancies pages. See Flag FAQs for more details.

• Assign a flag to a record
  
• Remove a flag
  

Assign a flag to a record

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. In the left panel, select Default, Validation Check, or Custom and then select a listing.
6. Select a record by clicking one of the small rectangles on the left side of the row.

   Tip:

   To assign the same flag to multiple records, select the records by pressing the Shift or Ctrl key while clicking the rectangles.

7. Right-click and select Mark Flags.
8. Select the flag to assign.
9. Click the flag state to assign.

   Tip:

   If a data load has completed while you were viewing the page, a message appears telling you to refresh the page, and the flag is not assigned. Click the Refresh icon and check the data. If the flag still applies, assign it again.

Remove a flag

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. In the left panel, select Default, Validation Check, or Custom and then select a listing.
6. Select one or more records by clicking one of the small rectangles on the left side of each row.
7. Right-click and select Mark Flags, then the assigned flag, then Clear Flag.

   You cannot remove flags assigned in InForm. InForm flags have the prefix Inf_.

Show flags assigned to a record

Flagged records have an icon on the left for the highest priority flag currently applied:

• High priority flag

• Medium priority flag

• Low priority flag

To see which flags are applied to a record:

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a flagged record by clicking one of the small rectangles on the left side of the row.
5. Right-click and select Show Flags.

View data lineage

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. In the left panel, select Default Listings, Validation Check Listings, or Custom Listings and then select a listing.
6. Click on a data item.
7. Right-click and select View Data Lineage.
8. Select one:

   • View Source Data to see upstream data that contributed to the selected data item.

   • View Target Data to see downstream data that the selected data item contributes to. Target data is not available in the Validation Check Listings page.

   • View Preferred Path If there are multiple source data items, one must be designated as preferred when the discrepancy is created, by either a validation check or a person. The system applies the discrepancy to the data items in the preferred path.

• Data lineage display
  

Data lineage display

The selected data item is in the top row.

1. Expand the node in the Name column.
   • If you selected View Source Data, the system displays the data item(s) that contributed to the current item in the immediate upstream clinical data models.
   • If you selected View Target Data, the system displays the data item(s) that this item contributes to in the immediate downstream clinical data model(s).
   • If you selected View preferred path, the system displays the one source data item designated as the preferred one.
2. Expand the node of the immediate source or target, if any, to see the next source or target data item, and so on.

For each data item, the window displays:

• Name: Displayed as data_model_name.table_name.column_name.

  An asterisk (*) means the system cannot display a data item and the rest of its path. This may be because:

  • The data item has been deleted or unmapped.
  • You do not have privileges to view the data item.
• Is Masked: A $ means the table contains masked data. This particular data item may not be masked.
• Is Staging: A # means the data item is in a staging table used in an intermediate transformation, not in a clinical data model table.
• Value: The data value.
• If the data originated in InForm, click the View Data in InForm icon to open InForm and see the data in context there.
• Preferred Path:
  • Yes if the data item is on the preferred path or the only path.
  • No if there are multiple paths and the data item is not on the preferred one.
• Primary Key: The primary key values of the record, in column order as column1_name:column1_value,column2_name:column2_value and so on.

See Data lineage FAQs.

View data in InForm or another system

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. In the left panel, select Default Listings, Validation Check Listings, or Custom Listing and then select a table, validation check, or custom listing.
6. Select a data item that originated in InForm.
7. Right-click and select View Data in External Source.
8. Log in to InForm or whatever EDC system the data came from.

   If the selected DMW data item has more than one source data item, the system displays the one on the preferred path.

   Tip:

   If you see a discrepancy in DMW that should have been sent to InForm or another source but wasn't, it may be that the process of sending the discrepancy to InForm failed. You can check this and fix it on the Home page. See Reprocess discrepancies that failed to be sent to InForm.

Reprocess discrepancies that failed to be sent to InForm

1. Go to the Home page, Validation Checks tab.
2. Click the Failed to send Discrepancies icon in the upper right corner.

   For each source data system, DMW displays counts for the selected study and lifecycle stage:

   • Processing forSystem shows the number of validation check discrepancies currently being sent to the system.

   • Failed Processing forSystem shows the number of discrepancies that should have been sent to the system but weren't.

3. If any discrepancies failed to be sent, click the Reprocess Discrepancies icon to send them again. Click the Refresh icon to see progress.

Create discrepancies

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. In the left panel, select Default, Validation Check, or Custom listings and then select a table, validation check, or custom listing.
6. Select the data item(s) to create a discrepancy on. You can select multiple data items if they all have the same problem.
7. Right-click and select Create Discrepancy.
8. Fill in the fields.

   Tips:

   Hover over field names with dotted lines for information about the field.

   Discrepancy Count displays the number of discrepancies currently open on the same data item.

9. Click OK.

Export data to Excel or CSV

Select an option from the Export drop-down list:

• Export All to Excel generates an .xls file that includes all data that satisfies the current filters (if any).

• Export Current Page to Excel generates an .xls file that includes the currently visible data.

• Export All to CSV generates a comma-delimited text file that includes all data that satisfies the current filters (if any).

• Export Current Page to CSV generates a comma-delimited text file that includes the currently visible data.

Create a custom listing

Show me how!

• Define query details
  
• Select columns to display
  
• Define table aliases
  
• Specify and test criteria
  
• Save and install a custom listing
  

Define query details

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select a clinical data model from the Model Name drop-down list.
5. Select Custom Listings at the bottom of the left pane.
6. Click the Add icon.
7. Enter a name and description for the new custom listing.

   Tip:

   Make the name 25 characters or less to avoid scrolling to read the name.

8. Select Mark as Public to enable all data reviewers to use this custom listing.
9. Select Authorize access to this listing for users without Blind Break rights if you know that only nonblinded data will be displayed in the listing, even though at least one source table contains blinded data. Take care to select columns that do not contain blinded data.

   If any source table is blinded in any way and this setting is not selected, the system blinds the entire target table, so that only users with Blind Break privileges can view any data.

10. Click OK.

Next: Select columns to display

Select columns to display

Identify the columns to display in the listing and write an expression to change data display (if needed, for example, to mask blinded data, or to append a string to all values in the column, or to concatenate two column values in a single column).

1. Drag the tables or columns you want to display from the Source pane into the Selected Columns tab.

   Tip:

   To select multiple columns, use Ctrl+click or Shift+click.

   Tables and columns that are marked Not Used in the transformation that writes to this model are not displayed here.

2. If you need an Expression to operate on a column(s) to affect the way data is displayed, you must create a 3-character Table Alias for its source table in the Define table aliases tab, then select it from this drop-down list.

3. Enter the Column Header for Display in the Custom Listings page.

4. Enter a Sort Order number to determine the column's display order relative to other columns.

5. Select Ascending (ASC) or Descending (DESC) Sort Type for the data display.

6. If you need an Expression to operate on the column in the SELECT clause, do one:

   • Enter the expression in the Expression field.

     If you need to use a function and you write the expression in free text, open the Select Packages tab and select the packages you will use. This enables the system to generate the query code.

   • Click the Modify Expression icon to Use the Expression Builder. You can edit code generated by the Expression Builder in this field afterward.

   To write an expression that operates on multiple columns, add all columns in the expression to the same row in the Selected Columns tab:

   1. Add one column to Selected Columns and highlight it there.

   2. Select the additional column(s) in the Source pane and click the Use in Expression icon in the Source pane.

      Note:

      If you select a column that contains masked data, write an expression for the column to mask values.

Next: Define table aliases.

Define table aliases

Table aliases are required only if you are using a self-join or writing a SELECT expression on a table column.

1. In the Define Table Alias tab, select the table from the drop-down list.
2. Enter an alias. The alias cannot be longer than three characters.

   The system displays the alias in the Selected Columns tab.

3. If you need another alias, click the Add Table Alias icon.
4. Click OK.

Next: Specify and test criteria.

Specify and test criteria

Specify the data condition the listing will look for.

1. Select the Criteria tab.
2. Build the WHERE clause to determine which records appear in the listing. Click the Add or Modify Criteria icon and Use the Expression Builder.
3. In the custom listing Query Details pane, view and test the generated code:

   • Click View Source. The system generates and displays the PL/SQL code.

   • Click Test: The system generates PL/SQL code, validates it, and displays either an error message or the records retrieved.

4. Click OK.

Next: Save and install a custom listing.

Save and install a custom listing

1. In the Custom Listings pane, select the listing.
2. Click the More Actions icon, then:

   • Click the Save as Query icon to save the listing for use in another session. For new queries, the Save operation includes installation.

   • Click the Install Custom Listing icon to install the listing. This is required only for copied queries.

Copy a custom listing

1. Click the Data Management icon at the top of any page.
2. Select Data Review.
3. Make sure the Listings tab is open.
4. Select Custom at the bottom of the left pane.
5. Click the More Actions icon, then click Copy Custom Listings.
6. Select a project (or other study grouping).
7. Select a model.
8. Select one or more custom listings within the model.
9. Click OK. The system searches the current model for the tables and columns that the selected custom listings read from.

   • If the tables or columns do not exist, the Copy operation fails with an error message.

   • If they exist but are marked Not Used in the transformation that writes to the model, the system copies the listings as disabled.

Use the Expression Builder

There are two ways to add an expression, with different advantages:

• Use the Expression Builder user interface. This more cumbersome process makes the transformations, validation checks, and custom listings that use it easier to copy and map in the new study.

• Enter code as free text directly in the Expression Text field. This is a simpler process for a programmer but results in a less easily reusable transformation, validation check, or custom listing.

  Important:

  • If you reference a static package or function in free text, you must select it in the Selected Packages tab.

  • In free text, use just the column name, not the table.column format, unless you need to use an alias, as in a self-join. In that case the alias.column format is required.

To use the Expression Builder:

1. In the Expression Criteria pane, select the following as needed to build the expression from left to right.

   • Add Group to add the parentheses () that surround a phrase in an expression or group smaller units of logic.

   • Add Item to add a unit of logic smaller than a group.

2. To add a phrase within a group, click the parentheses ().

   To add a phrase outside a group, click Expression.

3. To add an item, in the Expression Item pane select either Column, Function (for functions written by your company), or Standard Function (for Oracle SQL functions).

   To create an expression using column values:

   1. For Item Type, select Column.

   2. Click the Select Column icon.

      In the Select Column window, you can filter above any of the attribute columns to find the table column you want. Select a column and click OK.

   3. If needed, select an operator from the list.

   4. If needed, enter a data value. The system encloses the value you enter in single quotes.

   5. If needed, select a conjunction from the list.

      Tip:

      If you select a conjunction within a group, it appears within the group, at the end. If you need a conjunction outside the group, click Expression above, then select the conjunction.

   6. Click Add. The system generates the SQL expression and displays it in the Expression Text pane.

      Note:

      You can edit the generated code in the Expression Text pane, but if you do, you cannot continue to build the expression in the user interface.

      Click Validate to check the generated code.

   To use a function in your library:

   1. For Item Type, select Function. The Select Function window appears, displaying a list of Oracle functions.

   2. Select a function and click OK.

   To use a standard SQL function:

   1. For Item Type, select Standard Function.

   2. Click the Select Standard Function icon. A search window appears. To filter, enter all or part of the name in the field above. You can use the wildcard %.

   3. Select a function and click OK.

4. Define additional groups and items to complete the expression as necessary.

5. Click Save.

6. Click Validate. The system validates the code and displays any errors or warnings.

   To make a correction in the Expression Builder:

   1. Select the faulty item in the Expression Criteria pane. An Update button appears in the Expression Item pane.

   2. Make your changes in the Expression Item pane and click Update.

      For more information, see Pass data as input parameter values and Pass constant values.

• Pass data as input parameter values
  
• Pass constant values
  

Pass data as input parameter values

Use curly brackets ("{" and "}") as delimiters and the fully qualified format (model.table.column) to indicate input parameter values to SQL functions or custom functions in the expression. The default input is the column value if no metadata is specified after the column name.

For example, to calculate a subject's age from his date of birth:

round((sysdate - {Review.LAB_SRC.dob})/365)

where Review is the data model name, LAB_SRC is the table name, and dob is the column name. No metadata follows the column name, so by default the system passes the Date of Birth (dob) data value to the expression.

Pass constant values

You can hard-code a value for a target column using an expression that contains only a constant value or by calling a SQL function based on constants, for example:

round(3.14 * power(10, 2))

FAQs

• Listings FAQs
  
• Flag FAQs
  
• Data lineage FAQs
  

Listings FAQs

• What is a clinical data model?
  
• One of the rows says "Yes" under the Discrepancy_Exists Column, but no cells are highlighted yellow. Where is the discrepancy?
  
• How can I see only open validation check discrepancies when I am viewing a Validation Check listing?
  
• What is a disabled listing?
  
• Where can I copy a custom listing from?
  
• What happens if I modify a Table Alias?
  
• How can I view blinded data?
  
• How can I resend data that needs coding to TMS?
  

What is a clinical data model?

A clinical data model is a logical set of tables of study data. They were either loaded together into DMW from a single source or they contain data transformed by your study configurator for a purpose such as data review or analysis.

One of the rows says "Yes" under the Discrepancy_Exists Column, but no cells are highlighted yellow. Where is the discrepancy?

An active discrepancy filter has probably identified a discrepancy in a column that is not displayed.

How can I see only open validation check discrepancies when I am viewing a Validation Check listing?

You can apply a Discrepancy States and Tags filter showing only open discrepancies. However, you will see all records with any open discrepancies, even if the discrepancy raised by the validation check on the record has been manually closed, and even if the column with the open discrepancy is not displayed in the listing.

What is a disabled listing?

Listings are disabled if a table they read from is not used in the study.

Where can I copy a custom listing from?

You can copy custom listings from another study or from a different clinical data model in the same study. The system checks if the required source tables are available in the current model.

What happens if I modify a Table Alias?

If you are creating the listing and modify the alias after defining an expression or criterion that refers to the original alias, you must update the expression or criterion manually before saving.

If you are modifying a saved listing and modify an alias, the system will update the expression or criterion when you save, as long as the expression or criterion was originally created using the Expression Builder.

How can I view blinded data?

When you choose a listing that contains blinded data and you have privileges to view it, DMW will ask you to confirm that you want to access the blinded data.

After clicking Yes, you can use the padlock icon in the top right of the table to toggle between viewing real, blinded data and viewing the masking values (or, in the case where a whole table or row is blinded, seeing no data).

How can I resend data that needs coding to TMS?

If you make structural changes like updating a dictionary or adding columns to hold derived data from TMS, you can send all data that has been designated as needing coding by running the Force Rederivation job.

1. Go to the Home page.
2. Select a study.
3. Click the Modify icon in the Studies pane.
4. Click the TMS tab.
5. Click Force Rederivation to run Rederivation once, immediately.
6. A confirmation message appears because the job may take a long time. You can still work while it runs. Click OK.

Flag FAQs

• Why can't I assign a flag?
  
• Can the flags I assign be seen in InForm?
  
• I wish a different flag were available.
  
• Why can't I change some flags?
  

Why can't I assign a flag?

The Assign Flags function is available only if flags have been defined for the current data model type and if you have the privileges required to assign flags.

Can the flags I assign be seen in InForm?

No. DMW flags stay in DMW.

I wish a different flag were available.

Your administrator creates the flags you can assign in DMW.

Why can't I change some flags?

Flags that start with Inf_ are CRF form and section states imported from InForm as flags. After the Inf_ the name is the same as in InForm. You cannot change these flag assignments in DMW. Each of these flags has two states: Yes an No (Y and N).

Data lineage FAQs

• What is data lineage?

• When I create a discrepancy against a data item, how does that affect the data lineage?

• Why does the wrong source data item have the discrepancy?

• What is data lineage?
  
• When I create a discrepancy against a data item, how does that affect the data lineage?
  
• Why does the wrong source data item have the discrepancy?
  

What is data lineage?

A single data item, such as a subject's weight, appears in DMW first as loaded from InForm or another system and then in subsequent downstream clinical data models. DMW maintains tracks each data item as it appears in each model.

The column name may change from one model to the next, for example from WT to WEIGHT, and the value may be converted to different units and used to derive other values, such as Body Mass Index (BMI).

When I create a discrepancy against a data item, how does that affect the data lineage?

The system immediately displays the discrepancy against the corresponding data items upstream and downstream.

Why does the wrong source data item have the discrepancy?

When a data item like a derived BMI has more than one source data item, the validation check must designate a single column as the one against which to create the discrepancy, even if it is impossible to know in advance which one is faulty. A BMI that is out of range might be due to a bad value for height, weight, or unit, but one of them must be preselected. However, all relevant values can be displayed.

See also "Why is a query on a different item in InForm than in DMW?".