1. Analytics User Guide
  2. Prepare data
  3. Dataset descriptions
  4. Queries dataset

Queries dataset

You can use the Queries dataset to analyze and visualize details and audit history of query data.

Modes

Available in all 3 modes: Testing, Training, and Production

What type of data can I include in a custom report or visualization on queries?

With this dataset, you can:

  • View all queries in a state of Open and Answered to find a quick resolution.
  • Identify form questions and items with the most queries across your study.
  • Identify all questions with queries raised against them.
  • Visualize visit dates alongside with the queries raised against them.

    Note:

    Queries on visit dates are associated with the following metadata:
    • FORM_NAME = 'Visit Date'
    • FORM_REFNAME = 'Visit Date'
    • ITEM_NAME = 'Visit Date'
    • REFERENCE_CODE = 'Visit Date'
    Other columns not applicable to visit date queries are null.

For information about permissions required to access this dataset, see About your access to Oracle Clinical One Analytics.

Note:

This dataset supports data classifications security. All data that you have access to view in Oracle Clinical One Platform will be visible to you.
Browse descriptions of data elements included in this dataset:

Note:

Blank columns in Oracle Clinical One Analytics indicate null or not applicable.

Tip:

In order for Oracle Clinical One Analytics to perform optimally, begin by adding data elements from the Required folder to your workbook. For more information, see Create and edit a data visualization.

Parent topic: Dataset descriptions

Study folder

This table describes the data elements included in the Study folder

Table 2-28 Data elements in the Study folder

Data element Description
STUDY_MODE Indicates the study mode used in the referencing data in a custom report, such as Testing, Training, or Active.
STUDY_ID_NAME A study ID as specified by the study manager when they created the study, such as a protocol acronym and protocol number.
STUDY_TITLE A protocol's title as specified by the study manager.
STUDY_REFNAME Indicates the study's reference name used by the system. This value is composed by the STUDY_ID_NAME converted to uppercase with blank spaces removed. Once created, this value never changes, even if STUDY_ID_NAME is changed.
STUDY_PHASE A study's phase as indicated by the study manager when they created the study.
THERAPEUTIC_AREA Indicates the therapeutic area as specified by the study manager when they created the study.
BLINDING_TYPE Indicates whether the study is an open-label type of study or a blinded study, as specified by the study manager when they created the study.
STUDY_VERSION Indicates the study version number of the referencing data in a custom report.

Parent topic: Queries dataset

Site folder

This table describes the data elements included in the Site folder.

Note:

only a site's primary address is transmitted to Oracle Clinical One Analytics.

Table 2-29 Data elements in the Site folder

Data element Description
PI_PREFIX Indicates the principal investigator's prefix, as configured by a site manager
ADD_SUBJECTS Indicates whether a site is restricted from adding subjects, as configured by a site manager
SCREEN_SUBJECTS Indicates whether a site is restricted from screening subjects, as configured by a site manager
RANDOMIZE_SUBJECTS Indicates whether a site is restricted from randomizing subjects, as configured by a site manager
DISPENSE_TO_SUBJECTS Indicates whether a site is restricted from dispensing kits to subjects, as configured by a site manager
DEA_NUMBER Indicates the DEA Registration Number as defined by a site manager
EXPIRATION Indicates the expiration date of the DEA Registration Number as defined by a site manager
TIMEZONE Indicates the time zone the site is currently placed on as specified by a site manager
INVESTIGATOR A Principal Investigator's Full Name as listed when the site manager created the site
SHIPPING_ATTENTION Indicates the name of the person who will receive shipments at the site, as specified by a site manager
SDV_GROUP_NAME Name of the SDV Strategy, as entered by the study manager
INITIAL_SUBJECTS_COUNT Indicates the number of initial subjects at a site whose data must be verified by a CRA, as specified by a study manager
INITIAL_SUBJECTS_SDV_TYPE Indicates the type of source data verification to be performed by a CRA for the data of initially enrolled subjects, as specified by a study manager
REMAINING_SUBJECTS_PERCENTAGE Indicates the percentage of remaining subjects to be eligible for source data verification, as specified by a study manager
REMAINING_SUBJECTS_SDV_TYPE Indicated the type of source data verification to be performed by a CRA for the data of the remaining subjects, as specified by a study manager
ADDRESS_STREET_1 A site's first address as entered by the site manager when they created or last modified the site
ADDRESS_STREET_2 A site's second address as entered by the site manager when they created or last modified the site
ADDRESS_CITY A site's city as entered by the site manager when they created or last modified the site
ADDRESS_STATE_OR_PROV_OR_CNTY A site's state, province, or county as entered by the site manager when they created or last modified the site
ADDRESS_POSTALCODE The Zip Postal Code associated with a site's address
ADDRESS_COUNTRY A site's country as entered by the site manager when they created or last modified the site. The field display the country ISO code
PHONE The contact phone number as entered by the site manager when they created or last modified the site
FAX The contact fax number as entered by the site administrator when they created or last modified the site
EMAIL Email address of the site as entered by the site administrator when they created or last modified the site
SHIPPING_ADDRESS_1 The first line of a site's shipping address as entered by the site manager when they created or last modified the site
SHIPPING_ADDRESS_2 The second line of a site's shipping address as entered by the site manager when they created or last modified the site
SHIPPING_CITY City associated with the shipping address, as entered by the site manager when they created or last modified the site
SHIPPING_COUNTRY Country associated with the shipping address, as entered by the site manager when they created or last modified the site
SHIPPING_STATE_OR_PROV_OR_CNTY State, province, or county associated with the shipping address, as entered by the site manager when they created or last modified the site
SHIPPING_ZIP Zip Postal Code associated with the shipping address
SHIPPING_PHONE Phone number associated with the shipping address
SHIPPING_FAX Fax number associated with the shipping address
SHIPPING_EMAIL Email address associated with the shipping address
EHR_ENABLED

Indicates if a site is currently enabled for Electronic Health Record (EHR) data import.

No is displayed if EHR has never been enabled for a site or if a site was disabled for EHR.

Parent topic: Queries dataset

Country folder

This table describes the data elements included in the Country folder.

Table 2-30 Data elements in the Country folder

Data element Description
COUNTRY_NAME Indicates a country's two-digit ISO code.

Parent topic: Queries dataset

Subject folder

This table describes the data elements included in the Subject folder.

Table 2-31 Data elements in the Subject folder

Data element Description
SUBJECT_NUMBER The number currently assigned to the subject in the system as identifier.
SUBJECT_STATE A subject's state.
PREVIOUS_SUBJECT_NUMBER When a subject number change is applied, this field holds the number that was assigned to the subject before the change.
SCREENING_NUMBER Always displays the original screening number, assigned to the subject at screening.

Parent topic: Queries dataset

Event folder

This table describes the data elements included in the event folder.

Table 2-32 Data elements in the event folder

Data element Description
VISIT_IS_REQUIRED Indicates if a visit is required.
IS_SCHEDULED_VISIT Indicates if the visit is scheduled.
SCHEDULED_FROM_EVENT_NAME

Displays the EVENT_TITLE (visit title) of the Scheduled From visit as defined in the Visit Schedule.

Note: If a visit is not scheduled or it is the first visit in the schedule, then this element is populated with the visit’s own title. For example, Screening Visit is displayed for the Screening Visit, as it is the first visit in the schedule.

VISIT_STATUS Indicates a visit's status in the system.

Future visits are included with the status of 'SCHEDULED'. Dynamic and cycle visits will not be included until an event happens that causes their creation on the subject's schedule.
VISIT_START_DATE Date stamp of a visit's start date.
VISIT_TYPE Displays the type of visit: Screening, Randomization, Dispensation, Non-Dispensation, Optional, Withdrawal or Study Completion.
EVENT_TYPE
Displays the type of event that primarly impacts a visit's status. Upon selecting this data element, only events that occurred in your study are displayed. For example, you may see some of the following events:
  • Visit_Complete
  • Visit_Date_Changed
  • VisitDateCleared
  • VisitDateEntered
  • Visit_Not_Started
  • Visit_Skip_Undone
  • Visit_Skipped
  • Visit_Started
  • Visit_Inserted: this option refers to new visits inserted into the study's schedule as an Advanced Study Versioning change.
PROJECTED_VISIT_START_DATE Date when the next scheduled visit should start in the study, based on the configured visit schedule.
PROJECTED_VISIT_END_DATE Date when the next scheduled visit should end in the study, based on the configured visit schedule.
PROJECTED_VISIT_DATE Date when the next scheduled visit should take place in the study, based on the configured visit schedule.
DELAY_DAYS The number of days between the prior scheduled visit.
DELAY_HOURS The number of hours between the prior scheduled visit (in addition to the DELAY_DAYS field).
VISIT_WINDOW_BEFORE_DAYS Indicates how many days before the scheduled date and time the visit can occur, as entered by a study designer.
VISIT_WINDOW_BEFORE_HOURS Indicates how many hours before the scheduled date and time the visit can occur, as entered by a study designer.
VISIT_WINDOW_AFTER_DAYS Indicates how many days after the scheduled date and time the visit can occur.
VISIT_WINDOW_AFTER_HOURS Indicates how many hours after the scheduled date and time the visit can occur.
EVENT_TITLE The event's title, defined by the user when an event is created.
EVENT_REFNAME

The event's reference name.

Displays a capitalized version of the (user entered) EVENT_TITLE with blank spaces removed. Oracle Clinical One Analytics generates this value, which is not displayed in the Oracle Clinical One Platform user interface.

Note: This value does not change if the associated EVENT_TITLE is updated in a subsequent Study Version.

EVENT_ID_NAME The event's id as in Oracle Clinical One Platform.
VISIT_ORDER The order in which subject visits occur, as configured in the study design.
SCHEDULED_FROM_EVENT_REFNAME

Displays the EVENT_REFNAME of the Scheduled From Visit as defined in the Visit Schedule.

Note: If a visit is not scheduled or it is the first visit in the schedule, then this element is populated with the visit’s own refname. For example, SCREENINGVISIT is displayed for the Screening Visit, as it is the first visit in the schedule.

Parent topic: Queries dataset

Form folder

This table describes the data elements included in the Event folder.

Table 2-33 Data elements in the Form folder

Data element Description
FORM_NAME The name of the form, as specified by the study designer.
IS_ROLLOVER Indicates whether the form contains a rollover type of question.
IS_REPEATING Indicates if it is a repeating form.
FORM_STATUS Can have one of the following values:
  • COMPLETED
  • COMPLETE_WITH_ERRORS
  • IN_PROGRESS
  • INCOMPLETE
  • INCOMPLETE_WITH_ERRORS
  • SCHEDULED
  • BLANK
FORM_REFNAME A form's reference name.
REPEAT_FORM_NUMBER
Refers to the form instance number of all applicable form types with repeating data:
  • Two section forms: indicates the form instance number.
  • Lab forms: defaulted to a value of 1.
  • Repeating forms: this value will be null.

Parent topic: Queries dataset

Item folder

This table describes the data elements included in the Item folder.

Note:

There is a record for every change applied in Oracle Clinical One Platform. For each query, the query item displays the value at the time the query was raised. If a user updates the item and automatically closes the query or manually updates the query status, a new record is created and, for that record, the item displays its value at the time of the update.

Table 2-34 Data elements in the Item folder

Data element Description
ITEM_NAME Indicates the title of the question, as entered by a study designer.
VALIDATION_STATUS Indicates if a form item passed validation. For example, if the question was entered correctly and a rule was not broken.
VALUE The raw value of the form question value (can be an array in questions with decodes).

For more details see Form item output mapping in data extracts.

MEASURE_UNIT Indicates the measure of unit specified by a study designer for a Number type of question.
NORMALIZED_VALUE Currently not populated.
VALIDATION_FAILURE Reason for failure if validation status is failed or the rule validation failed.
NUM_VALUE If the question type is a calculation, measurement, or number, this field is populated with that number.
FLOAT_VALUE Item value without decimal places, if precision is provided in the study design.
UTC_DATETIME_VALUE Indicates the date and time in UTC for a Date/Time type of question.
MONTH_VALUE If the question type is Date/Time, this field is populated with the month value (1-12).
DAY_VALUE If the question type is Date/Time, this field is populated with the day value (1-31).
YEAR_VALUE If the question type is Date/Time, this field is populated with the year value (i.e. 2021).
HOUR_VALUE If the question type is Date/Time, this field is populated with the hour value (0-23).
MINUTE_VALUE If the question type is Date/Time, this field is populated with the minute value (0-59).
SECOND_VALUE If the question type is Date/Time, this field is populated with the second value (0-59).
ITEM_D Decoded raw value, with additional considerations according to data type. If the question has a code value, it is populated in this field.

For more details see Form item output mapping in data extracts.

ITEM_R The raw value: alphanumeric value as entered in Oracle Clinical One Platform with no conversions. This includes data entry flags.

For more details see Form item output mapping in data extracts.

ITEM_F The formatted value: value as entered in Oracle Clinical One Platform converted to the question data type as per form design.

For more details see Form item output mapping in data extracts.

ITEM_TYPE The form item's question type.
QUESTION_TYPE Indicates the type of question as defined by a study designer.
QUESTION_HINT Indicates information that a study designer provided as a hint to help answer a question.
FORMITEM_IS_REQUIRED Indicates if the question is required. Required questions must be answered in order to save the form that contains it.
READONLY Indicates that the question is marked as read-only by a study designer.
SAS_VARIABLE Indicates the SAS Variable of a form defined by a study designer.
SAS_LABEL Indicates the SAS Label of a form defined by a study designer.
REFERENCE_CODE Indicates a question's reference code.
HIDDEN Indicates if a question is hidden, as marked by a study designer.
FREEZE Indicates if a question is frozen by a data manager or CRA.
VERIFIED

Indicates the question's verification status.

Data element can be populated with the following values:
  • VERIFIED: A question, form, or visit is verified.
  • UNVERIFIED: A question, form, or visit was once verified, then updated making it unverified.

Note: VERIFY_REQUIRED and NOT_APPLICABLE are not currently supported statuses in Oracle Clinical One Analytics.
SIGNED Indicates if a valid casebook signature is applied to the item.

Parent topic: Queries dataset

Query (Required) folder

This table describes the data elements included in the Query (Required) folder.

Data element Description
STATE Indicates a query's status:
  • Opened
  • Answered
  • Closed
  • Candidate query
HAS_QUERY Indicates if there is a query raised against a question, irrespective of the status.
ASSIGNED_ROLES Indicates the roles that are assigned to receive a query.
QUERYAGE Indicates the number of days passed since a query was first opened.
QUERY_COMMENT Indicates a comment associated with a query, as entered by the user who last modified the query.
IS_AUTO_QUERY Indicates whether this is an automated query.
QUERY_TYPE Indicates the query type.
PROPERTY_NAME

Name of the property to which the query is associated to. This only applies to queries on visit dates and the default is visitStartDate.

Note: For other queries, this value is null.

PROPERTY_TYPE

Type of property to which the query is associated to. This only applies to queries on visit dates and the default is visit.

Note: For other queries, this value is null.

Parent topic: Queries dataset

Audit folder

This table describes the data elements included in the Audit folder.

Table 2-35 Data elements in the audit folder

Data element Description
VERSION_START Indicates the date and time of when the data was changed.
VERSION_END Indicates the date and time of when data was changed, if the data is not current.
OPERATION_TYPE Audit trail field that represents the type of operation performed (i.e. create, modify).
OBJECT_VERSION_NUMBER Audit trail field that represents the version number of the data.
REASON Indicates a reason for changes in a subject's data. Populated by drop-down list.
COMMENTS Required comment in a reason for change if ‘Other’ is selected. Populated as Rule Execution for calculated values.
USER_NAME

Audit trail field that represents the user who performed the action.

The value for this column may represent a user's actual username or a user's email address, depending on how the user login was defined in Oracle Life Sciences IAMS.

IS_CURRENT Audit trail field to display either current status or full audit trail of the data.
CURRENT_STUDY_ROLE_NAME Specifies of the role of the user who updated the given record. If the user's study role changes, this field will show the current study role of the given user.
EHR_IMPORTED

Indicates if a question was populated via an Electronic Health Record (EHR) data import.

If EHR data import is disabled after the data is imported, EHR_IMPORTED continues to show Yes.

Parent topic: Queries dataset

Reference folder

This table describes data elements in the Reference folder.

Table 2-36 Data elements in the Reference folder

Data element Description
QUERY_WID A number that represents unique identifier of a query.
STATE_ID Numeric value that represents a query state.

STUDY_WID

A number that represents the unique identifier of the study.

SITE_WID

A number that represents the unique identifier of a site.

SUBJECT_WID

Indicates a subject's numeric identifier.

EVENT_WID

A number that represents the unique identifier of an event.
EVENT_INSTANCE_NUM Indicates the unscheduled visit instance number as designed by the study designer.
FORM_WID A number that represents the unique identifier of a form.
REPEAT_SEQUENCE_NUMBER
Refers to the row instance number of all applicable form types with repeating data:
  • Two section forms: unique numeric identifier of the row in the repeating section.
  • Lab forms: unique numeric identifier of the row in the repeating section that captures lab tests and results.
  • Repeating forms: indicates the repeating form number.
ITEM_WID A number that represents the unique identifier of an item.

SOFTWARE_VERSION_NUMBER

A number that represents an incremental increase every time a data point is modified.

USER_WID

Indicates a user's numeric identifier.

DH_TIMESTAMP

A timestamp that indicates when the data became available in the dataset.
SUBJECT_EVENTINST_FORMITEM_WID A number that represents the unique identifier of an item within a subject form associated with a specific visit instance.
SUBJECT_EVENT_INST_WID A number that represents the unique identifier of a subject event's instance.
PARENT_WID Currently not populated.
ROOT_WID Currently not populated.
SCHEDULED_FROM_EVENT_WID A number that represents the unique identifier of the previously scheduled event.
COUNT Represents the count of queries.

CURRENT_STUDY_ROLE_WID

Numeric identifier of the role of the user who updated the given record. If the user study role changes, this field will show the current study role of the given user.

Parent topic: Queries dataset