Queries in ODM export
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 theMetaDataVersionOID
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
orPrequeryRaised
, the query text is specified here. -
If the query state is
QueryRejected
orQueryResolved
, 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:
"PrequeryRaised"
"PrequeryPromoted"
"PrequeryRejected"
"QueryRaised"
"QueryResolved"
"QueryRejected"
"QueryApproved"
"QueryClosed"
"QueryRemoved"
"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:
- First by date and time of the audit record.
- For the entries having the same date and time stamp, order by the value of the
CodeListOID="CL_QRY_STATE"
, as listed above.