Queries dataset
You can use the Queries dataset to analyze and visualize customized query data in Oracle Clinical One Analytics.
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.
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:
- Study folder
- Site folder
- Country folder
- Subject folder
- Event folder
- Form folder
- Item folder
- Query (Required) folder
- Audit folder
- Reference folder
Parent topic: Dataset descriptions
Study folder
This table describes the data elements included in the Study folder
Table 2-26 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 | The STUDY_ID_NAME data element that is converted to uppercase and spaces are removed. This value never changes after created even if STUDY_ID_NAME changes. |
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.
Table 2-27 Data elements in the site folder
Data element | Description |
---|---|
ADD_SUBJECTS | Flag that enables or prevents site users from adding subjects at one or multiple sites. |
ADDRESS_CITY | A site's city as entered by the site manager when they created or last modified the site. |
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. |
ADDRESS_POSTALCODE | The Zip Postal Code associated with a site's address. |
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_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. |
DEA_NUMBER | The DEA registration number. |
DISPENSE_TO_SUBJECTS | Flag that enables or prevents site users from dispensing kits, devices or performing dose changes for subjects at one or multiple sites. |
DRUG_DESTRUCTION_CAPABLE | Flag that defines if the kit type is destructible at the site or not. |
Email address of the site as entered by the site administrator when they created or last modified the site. | |
EXPIRATION | Indicates the expiration date of the DEA Registration Number as defined by a site manager. |
FAX | The contact fax number as entered by the site administrator when they created or last modified the site. |
INITIAL_SUBJECTS_COUNT | Number of initial subjects included in the SDV strategy. |
INITIAL_SUBJECTS_SDV_TYPE | Type of Source Data Verification: All Questions or Critical Questions. |
PHONE | The contact phone number as entered by the site manager when they created or last modified the site. |
PI_PREFIX | The principal investigator's prefix at the site. |
RANDOMIZE_SUBJECTS | Flag that enables or prevents site users from randomizing subjects at one or multiple sites. |
REMAINING_SUBJECTS_PERCENTAGE | Number of remaining subjects included in the SDV strategy. |
REMAINING_SUBJECTS_SDV_TYPE | Type of Source Data Verification: All Questions or Critical Questions. |
SCREEN_SUBJECTS | Flag that enables or prevents site users from screening subjects at one or multiple sites. |
SDV_GROUP_NAME | Name of the SDV Strategy, as entered by the study manager. |
SHIPPING_ADDRESS_1 | A site's first shipping address as entered by the site manager when they created or last modified the site. |
SHIPPING_ADDRESS_2 | A site's second shipping address as entered by the site manager when they created or last modified the site. |
SHIPPING_ATTENTION | Indicates the name of the person who will receive shipments at the site, as specified by a site manager. |
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_EMAIL | Email address associated with the shipping address. |
SHIPPING_FAX | Fax number associated with the shipping address. |
SHIPPING_PHONE | Phone number associated with the shipping address. |
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. |
SITE_ID_NAME | Indicates the site ID as entered by a site manager when they created or last modified a site. |
SITE_STATUS | Indicates the status of a site whether it is New, Active, or Retired. |
SITE_STUDY_VERSION | The study version assigned to the site, as configured 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. |
SITE_NAME | Indicates the site's name as entered by a site manager when they created or last modified a site. |
SITE_TYPE | Indicates the type of organization. |
Parent topic: Queries dataset
Country folder
This table describes the data elements included in the Country folder.
Table 2-28 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-29 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. |
Parent topic: Queries dataset
Event folder
This table describes the data elements included in the event folder.
Table 2-30 Data elements in the event folder
Data element | Description |
---|---|
VISIT_IS_REQUIRED | Indicates whether or not a visit is required. |
IS_SCHEDULED_VISIT | Indicates whether the visit is scheduled or not. |
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 (visit started, visit completed). |
PROJECTED_VISIT_START_DATE | Date when the next scheduled dispensation should start in the study. |
PROJECTED_VISIT_END_DATE | Date when the next scheduled dispensation should end in the study. |
PROJECTED_VISIT_DATE | Date when the next scheduled dispensation should take place in the study. |
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-31 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 whether or not it is a repeating form. |
FORM_STATUS | Can be valid or invalid. Invalid form status indicates there are items within the form with a validation failure. |
FORM_REFNAME | A form's reference name. |
REPEAT_FORM_NUMBER |
Refers to the form instance number of all applicable
form types with repeating data:
|
Parent topic: Queries dataset
Item folder
This table describes the data elements included in the Item folder.
Table 2-32 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) |
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 whether the question is required or not. 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 whether a question is hidden or not, as marked by a study designer |
FREEZE | Indicates whether a question is frozen or not by a data manager or CRA |
VERIFIED |
Indicates whether a question is verified or not by a CRA. Data element can be populated with the following
values:
|
SIGNED | Indicates whether a question is signed by a PI or not |
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:
|
HAS_QUERY | Indicates whether there is a query raised against a question or not, 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 autommated query. |
QUERY_TYPE | Indicates the query type. |
Parent topic: Queries dataset
Audit folder
This table describes the data elements included in the Audit folder.
Table 2-33 Datat 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. |
Parent topic: Queries dataset
Reference folder
This table describes data elements in the Reference folder.
Table 2-34 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:
|
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