listing data, meta-data and audit-trail

On this page we discuss a utility with which you can print all items that were entered. It's built in MsAccess, so you have to have MsOffice installed to use it. We use the word utility on purpose, because it is not an official report and certainly not an analysis of any kind. It is nothing more than a list of data, as shown in fig. 1

fig. 1: this is what you get on paper

What we see is the data-items, ordered per Subject, per Event, per Form, including some information about the Subject and the status of both Event and Form. As you can see the so-called Decoded Value is displayed, so if you set up a ResponseOptions-list for an Item, either in combination with radio, single-select or multi-select, the full text is displayed. Everything is displayed in one enormous list, even if you defined column-numbers, or if your items are in a repeating group.

isn't that a standard report in OpenClinica?

Yes, that is a standard report in OpenClinica, starting from version 3.2. You get it by clicking in the SubjectMatrix on the magnifier-icon in the right-most column. This opens the ViewSubject-form, that lists all Events with all CRF's. And at bottom of this form you see a link called Subject Casebook and if you click that, you get the option to print all available data of a Subject

fig. 2: how to get the standard OC-reort

where do we start?

If you want to use the MsAccess-utility, you start with downloading the datalisting-file, unzipping it and opening the accdb-file.

(The version above is for OC 3.2 and up. If you are looking for the old version of this utility, suitable for pre OC3.2, you can find it here.)

A menu-form opens, as shown in fig. 3. (If it does not open automatically, click on tab Forms and then double-click frmGetClinicalData.

fig. 3: the menu

follow the buttons

From here it's just a matter of clicking buttons: start at the top and work down to the bottom. Seriously.
Of course you must have an ODM-file to work with, so take one of your extract-datasets and export it in CDISC ODM XML 1.3 Full with OpenClinica extensions. Download the file and unzip it.

1. Click the button browse or type in the location of your xml-file.
2. Now click the button get metadata. In the bottom of your screen you will see what activity is running. The scripts goes through the xml-file and reads all the definitions: Events, Forms, ItemGroups, Items, CodeLists and MultiSelectLists.
3. Clicking get clinicaldata will read all clinical-data in your xml and put that in tblClinicalData.

And that was it: you can now print a report. You can choose one Subject, using the select, or you can leave the box Only for this subject blank and get the data for all Subjects: just click on the button open report.

As an extra help: there will be a D to the right of the items that have a Discrepancy.

and what about that last button?

The last button is an extra: if you want the ClinicalData in csv-format, type in location and name of a file to export to. If you click the button use friendly names, you do not get the OID's, but StudySubjectID's, EventNames, CRFNames and ItemNames.

fig. 4: output to csv for one Subject

If the file has been created you can double-click it and it will open in Excel.

fig. 5: Subject F3012 in Excel

hold on, there's three more tabs

Yes, there are three more tabs in the utility: one that gives a list of the CRF's per Event and a sort of annotated CRF. And again: it is a sort of annotated CRF.

fig. 6: CRF characteristics

RBM or just clever monitoring

The one-but-last tab, audit data gives you information about who did what. That sounds like a detective, but in fact you can use it in your monitoring process. For example, you are doing partial SourceDataVerification and based on that you see that one of the dataentry-persons makes more errors than the rest. In that case it makes sense to have a list of all the data that person has touched. That sounds like RiskBasedMonitoring or if not that, it is at least clever monitoring.

In fig. 7 you see a selection of all the data that Joe Satriani created or modified between 2014-04-05 and 2014-06-13, plus the output.

fig. 7: what did Joe do?

For this page we had lots of input, including code, from Krister Kristianson and Lindsay Stevens: many thanks!

Other how-to-pages can be found here.

this page was last reviewed September 2021