Queries in ODM export

  • Published by Viedoc System 2020-06-04
  • Print

Introduction

To include the query information in the exported file, you need to select Queries under Type of data in the Data Export page.

The Queries can be exported to the following export output formats:

  • Microsoft Excel - Office Open Extensible Markup Language (XML)
  • Comma-separated values (CSV)
  • Operational Data Model (ODM) - in this case, the Query history is not optional, but will be included regardless. For this reason it is not displayed as an option.

This lesson describes how and where to find the query related information in the ODM export.

Association

In the ODM export output file, the queries are stored under the Association tag.

As the query history is always included in the ODM export, there is an Association entry for each query state (for example if there is one query that has been through 3 different states namely raised, resolved and approved, there will be 3 different Association entries).

Note! All association elements are sorted by the AuditRecord datetime stamp.

Each Association links an item (identified by the KeySet, see the description below) to a set of properties related to a specific query wrapped under the Annotation tag (see detailed description below).

The image illustrates an example of an ODM export output, with the information within the Study tag at the top of the image and a query information under the Association tag at the bottom of the image:

For each Association entry, the following information is provided:

  • The StudyOID and the MetaDataVersionOID indicate where the respective information is defined.
  • The KeySet is generally used to identify the start and end of the annotated "link". For queries, both key sets are identical, as they identify the same item, namely the item the respective query corresponds to. It contains the following:
    • StudyOID
    • SubjectKey
    • StudyEventOID
    • StudyEventRepeatKey
    • FormOID
    • FormRepeatKey
    • ItemGroupOID - applicable only for queries on item level.
    • ItemOID​​​​​ - applicable only for queries on item level.

Annotation

The Annotation tag wraps the specific information of the respective query, as described below. Since there is only one Annotation within an Association, the SeqNum is always "1".

Comment

The Comment value depends on the query state and is set as follows:

  • If the query state is QueryRaised or PrequeryRaised, the query text is specified here.

  • If the query state is QueryRejected or QueryResolved, the resolution text is specified here, as in our example "I am confirming the data".

  • No comment element is present for other cases than the ones mentioned above.

"CL_ANNOTATION_TYPE"

CodeListOID="CL_ANNOTATION_TYPE" indicates the annotation type, for example "ManualQuery":

All the possible annotation types can be found under the Study tag, under the respective StudyOID and MetaDataVersionOID, respectively:

  • "ManualQuery"
  • "ValidationQuery"
  • "UpgradePending"
  • "MissingData"
  • "SDV"
  • "CRAReview"
  • "DMReview"
  • "MedicalCoding"
  • "Lock"

Of all the above, only the following refer to queries:

  • "ManualQuery"
  • "ValidationQuery"
  • "UpgradePending"
  • "MissingData"
    Note! The "MissingData" annotation type is used both for unconfirmed missing data and missing data, there is no distinction between those in the ODM export.

"CL_QRY_STATE"

CodeListOID="CL_QRY_STATE" indicates the query state, for example "QueryRaised.

All the possible query states can be found under the Study tag, under the respective StudyOID and MetaDataVersionOID, respectively:

  1. "PrequeryRaised"
  2. "PrequeryPromoted"
  3. "PrequeryRejected"
  4. "QueryRaised"
  5. "QueryResolved"
  6. "QueryRejected"
  7. "QueryApproved"
  8. "QueryClosed"
  9. "QueryRemoved"
  10. "PrequeryRemoved"​​

For more details about the query and pre-query states, see Queries overview.

"CL_QRY_ITEM_SEQ_NO"

CodeListOID="CL_QRY_ITEM_SEQ_NO" indicates the sequence number of the respective query for the linked item. This is used to identify the query, meaning that all the Annotation entries corresponding to different states of the same query, will have the same value of CodeListOID="CL_QRY_ITEM_SEQ_NO" and same KeySet.

v4:AuditRecord

v4:AuditRecord contains the audit record for the respective annotation.

Sorting the entries for a query

Since the same query can go through many query states, there can be many Annotations with the same value of CodeListOID="CL_QRY_ITEM_SEQ_NO"and same KeySet. In order to get the latest entry for the same query, you can sort as follows:

  1. First by date and time of the audit record.
  2. For the entries having the same date and time stamp, order by the value of the CodeListOID="CL_QRY_STATE", as listed above.