Excel export

  • Published by Viedoc System 2022-05-05
  • Print

Introduction

Viedoc uses Microsoft Excel Open Extensible Markup Language (XML) format which is compatible with Excel version 2007 and later.

When selecting Microsoft Excel as Output format in the Data export page, you have different options for grouping data and for the layout, as described in the following sections.

For general information about data export in Viedoc, see Exporting data.

Note! Since the maximum number of rows supported for Excel is 1048576, in case data in a sheet exceeds this number, data will be split into multiple sheets.

File structure

The Excel export contains the following sheets:

  • README - always the first sheet in the Excel export output, with general information about:
    • the Viedoc output version (for details see Exporting data)
    • the time zones used for date/time fields
    • the meaning of the signature, only if the Review status was selected to be included in the export
  • Depending on the selected Data grouping:
    • If Group data by form is selected, there is one separate sheet for each form, as described below in Group data by form.
    • If Do not group data is selected, there is one sheet called Data that contains all exported data. See Do not group data.
  • Items - after the data sheet(s), there is the Items sheet, that lists all the existing items in the exported data with the following information (columns):
    Note! This sheet is not included when selecting the one row per item layout.
    • ID - the item ID, as set in the study design
    • Label - the field label, as set in the study design
    • Data type - the type of data, as set for the respective item in the study design. Can be one of the following:
      • integer
      • double
      • text
      • string
      • date
      • datetime
      • base64Binary (for File upload items)
    • Mandatory:
      • True - if the item was set as mandatory in the study design
      • False - if the item was not set as mandatory in the study design
    • Decimals - the number of allowed decimals in the data content.
    • Min Length - the minimum required length for the respective field, if set in Viedoc Designer. For checkbox items, this is the minimum number of checkboxes required to be set, a set in Viedoc Designer.
    • Max Length - the maximum number of characters that can be entered, if set on item level in Viedoc Designer.
    • Format Name - the format name, if set in Viedoc Designer (under Outputs and Validation > Formats). For the codes of the checkbox or radio button items, a default value is generated even if no format name is set in Viedoc Designer.
    • Content Length - the maximum number of characters of the data content. That is, this is set by the length of the content
  • CodeLists - the last sheet in the export, containing all the code list items in the exported data with the following information:
    Note! This sheet is not included when selecting the one row per item layout.
    • Format Name
    • Data Type
    • Code Value
    • Code Text

The table below lists which sheets are included in the Excel file, depending on the selected Grouping and Layout:

Group data by form Do not group data
one row per subject
  • README
  • one separate sheet for each form
  • Items
  • CodeLists
  • README
  • Data
  • Items
  • CodeLists
one row per activity
  • README
  • one separate sheet for each form
  • Items
  • CodeLists
  • README
  • Data
  • Items
  • CodeLists
one row per item
  • README
  • one separate sheet for each form
  • README
  • Data

Header rows

The headers are always represented by the first two rows in a sheet, as illustrated in the following image:

1. Human-readable format

2. Machine-readable format

An item that was changed within a new/revised study design version will have a "__n" suffix added, where n is incremented for each study design version where the respective item was changed:

Data filtering - Type of data

Under Type of data, you can filter the data to be exported. If you filter data for Signed data, Not signed data, SDV performed or NA, or SDV pending, certain cells in the data sheets in the exported Excel file may appear empty. The data rows that contain empty cells due to the filtering are marked by an “X” in the last column of the data sheets that is named Empty cells on row may be due to export filter.

For example, let's say that we have an Add Patient event, and the Date of Birth is one of the data entered during this event. For a particular subject, this data was entered, signed by the Investigator, and afterwards modified, but not signed after the change. We perform an export that includes only the signed data, as illustrated in the image below:



The value of the Date of birth field that was recently changed and not signed is not included in the export (the cell appears empty). The data row containing the empty cell is marked by a "X" the Empty cells on row may be due to export filter, as shown below:

Data grouping

You can select whether the data should be grouped by form or not, from the Data grouping dropdown list.

Note! The data grouping is available only for the Excel/Comma-Separated Values (CSV) output.

Group data by form

When grouping the data by form, a separate sheet is created for each form. The sheet name is the Form ID, as set in the study design (in Viedoc Designer).

In each form sheet, the first columns (to the left) are the same for all the forms and provide information about the site, subject, event, activity and design version:

Column Description
Site Sequence number Counter that identifies the site globally within the study.
Site name The site name, as set in Viedoc Admin.
Site code The site code, as set in Viedoc Admin.
Subject sequence number Counter that identifies the subject within the site.
Subject Id The Subject ID, in the format configured in Viedoc Designer. The Subject ID is the subject identifier displayed in Viedoc Clinic on the subject card, subject details page, and so on.
Event sequence number Counter that identifies the event within the sequence of events for the same subject.
Event Id The event ID, as set in the study design (in Viedoc Designer).
Event name The event name, as set in the study design (in Viedoc Designer) and displayed in Viedoc Clinic.
Event date The event date, as set in Viedoc Clinic when the event is initiated.
Activity Id The activity ID, as set in the study design (in Viedoc Designer).
Activity name The activity name, as set in the study design (in Viedoc Designer) and displayed in Viedoc Clinic.
Form sequence number

Counter that identifies the instance of the respective form within the respective activity. This is mostly used for repeating forms.

For non-repeating forms, this is "1". If a form is reset and then saved again the new form has sequence number "2", and so on.

Form sequence number increases one step every time reset/initiate occurs.
Subject form sequence number Counter that uniquely identifies the instance of a specific form on a subject level, that is, it starts with 1 and it is incremented each time a new instance of the form is created for that subject.
Origin Subject form sequence number For a copied form instance, it identifies the form instance from which data was copied for the first time. For the first instance of the form (that is, not copied) it gets the value of the SubjectFormSeqNo.
Source Subject form sequence number For a copied form instance, a counter that identifies the source of a copied form instance (the form instance the data was copied from). It gets the value of the SubjectFormSeqNo from which the form instance was copied. For the first instance of the form (not copied) it is empty (null).
Design version The design version used at the time of data edit for the respective form.

The example in the image below shows an export with the default settings for the Layout, that is, 1 row per activity.

The following columns are specific to each form, one column for each item in the respective form. Each column has the <Item name>, as set in the study design (in Viedoc Designer) as column header.

Do not group data

If you choose not to group the data, then all data from all forms will be exported in the same sheet (Data) of the output file.

The example in the image below shows an export with the default settings for the Layout, that is, 1 row per activity.

In the Data sheet, the first columns (to the left, marked in green) are the common for all the forms and provide information about the site, subject, event and activity.

The following columns (to the right, marked in orange) contain form-specific information for all the forms within the event. For each of the forms, the following columns are added:

  • <FormName>_Design Version - the form name, as set in the study design (in Viedoc Designer) and displayed in Clinic. In the example in the image, the form name is Demographics.
  • <FormName>(<Form Repeat Key>)_<ItemName> - for each item in the respective form. The form name and item name, as set in the study design (in Viedoc Designer) and displayed in Clinic. The Form Repeat Key identifies the instance of the form (for repeating forms). For non-repeating forms, the Form Repeat Key is always 1.

Layout

In the Layout section, you can select whether the data should be organized in the output file as:

One row per subject

The output in this case will look as shown in the below image. The example shows an export performed with all the default settings except for the Layout which is set to 1 row per subject.

There is one sheet for each form, as the default setting is to Group data by form.

There is one row per subject, that is, one row for each SubjectID (that uniquely identifies the subject).

The first columns provide information on the site and subject:

Column Description
Site name The site name, as set in Viedoc Admin.
Site code The site code, as set in Viedoc Admin.
Subject sequence number Counter that identifies the subject within the site.
Subject Id The Subject ID, in the format configured in Viedoc Designer. The Subject ID is the subject identifier displayed in Viedoc Clinic on the subject card, subject details page, and so on.

The following columns are the item-specific values, one set as described below for each item in the exported data. The order of the items is by event, as set in the study workflow.

  • In case Do not group data is selected under Data grouping (see Do not group data):
    <Event name>(<Event Repeat Key>) - <Activity name> <Form name> <Item name> <Code list value>, where:
    • <Event name> - the event name, as set in the study design and displayed in Clinic.
    • <Event Repeat Key> - the event repeat key, applicable only for the unscheduled/common events.
    • <Activity name> - the activity name, as set in the study design
    • <Form name> - the form name, as set in the study design and displayed in Clinic.
    • <Item name> - the item label, as set in the study design and displayed in Clinic.
    • <Code list value> - applicable only for the checkbox items. This is the code list value set in Viedoc Designer for each choice of the respective checkbox item
  • In case Group data by form was selected under Data grouping (see Group data by form), the columns are named similar as for the not grouped data above, without the <Form name>, as the form is identified by the sheet name.

Note! The columns Event sequence number, Event Id, Activity Id, Form sequence number, Subject form sequence number, Origin Subject form sequence number, and Source Subject form sequence number are not included when you have selected 1 row per subject.

One row per activity

The output in this case will look as shown in the below image. The example shows an export performed with all the default settings except for the Layout which is set to 1 row per activity.

There is one sheet for each form, as the default setting is to Group data by form.

The data is grouped so that, for each subject (1), there is one row for each activity (2).

One row per item

The output in this case will look as shown in the below image. The example shows an export performed with all the default settings except for the Layout which is set to 1 row per item.

There is one sheet for each form, as the default setting is to Group data by form.

The data is grouped so that there is one row for each item (3) within an activity (2) for a subject (1).

The data is sorted by: site, subject, event date, event repeat key, form repeat key, form ID, item group ID, item ID.
If the Include history option is selected (see following section), the data is ordered from the oldest to the current item data (that is, by the Edit sequence number).

Include history

When selecting 1 row per item, the option to Include history becomes available. If selected, the edit history information (audit trail) will be included in the exported output (that is, the information shown in Viedoc Clinic on form level when selecting Show history).

The following information (columns) is added for each entry in the output file:

  • Edit sequence number - a counter for each change per item.
  • Edit reason - reason for change (initial or given reason at data edit).
  • Edit by - the user who performed the changes (user name and user id in parentheses).
  • Edit date/time (UTC) - edit date/time (Coordinated Universal Time (UTC)).

The items belonging to a reset or deleted form/event/subject are included as well in the export, together with a full history that gives the reason for resetting or deleting the form/event/subject.

Checkboxes

Checkbox items are output as one row per code list item. All code list items are listed, regardless if they contain data or not. Each row is labeled, in the Item Id column, with the item Object Identifier (OID) and an 1-based index, as illustrated in the following image:

If the option to Include history is selected, then the code list items are ordered by the time of data entry (that is, by the Edit sequence number).

Form link items in the export output

Form link items can be organized in the Output format as:

  • One row per activity (default)
  • One row per item
  • One row per subject

One row per activity

Selecting 1 row per activity generates the output as shown in the image below. The exported file contains two columns per linked form instance, the Data column and the Identifier column, (the header is labelled Identifier).

In the example below:

  • Each linked form instance shows its display value (as displayed in Clinic) and the Identifier.
  • The medication Paracetamol in this example is linked to two different entries in the Adverse Event log.
  • The format for the identifier: EventId-EventSeq-ActivtyId-FormId-FormSeq.

There are also two header rows in the output:

Header rows, one row per activity
Row 1: Data column Item Label, Counter of the selected link starting at one
Row 1: Identifier column Item Label, Counter of the selected link starting at one, Identifier
Row 2: Data column Item ID, Counter of the selected link starting at one
Row 2: Identifier column Item ID, Counter of the selected link starting at one, ID

One row per item

Selecting 1 row per item generates the output as shown in the image below. The exported file contains two additional columns with the headers Item value and Item code, and one row per linked form instance.

Note! In the export preview the form identifier column is excluded by default. The order the form link item was added (time of data entry) is followed in the export.

One row per subject

Selecting 1 row per subject generates the output as shown in the image below. The exported file adds two columns per linked form instance to the exported file, the Data column and the Identifier column:

There are also two header rows in the output:

Header rows, one row per subject
Row 1: Data column Event Label (event counter), Activity label (activity counter), Item label (counter of the selected link.)
Row 1: Identifier column Event Label (event counter), Activity label (activity counter), Item label (counter of the selected link), Identifier
Row 2: Data column Event ID (event counter), Activity ID (activity counter), Item ID (counter of the selected link.)
Row 2: Identifier column Event ID (event counter), Activity ID (activity counter), Item ID, (counter of the selected link), ID

Recurring events in the export output

Recurring events are identified in the export output by the StudyEventRepeatKey.

The image illustrates the form Vital Signs in the Excel export output. The form is used in three events (Visit 1, Visit 2 and Visit 3), of which Visit 3 is a recurring event. The four instances of Visit 3 are identified by the StudyEventRepeatKey that is listed in the Event sequence number (EventSeq) column:

Note! Support for recurring events has been added in Viedoc release 4.39. That means that if you would like to export recurring events, you should select Viedoc version 4.39 or later in the Output version dropdown menu under Output format.

Repeating forms in the export output

Repeating forms are identified in the export output by the FormRepeatKey.

The image illustrates the repeating form Lab in the export to Excel. The instances of the form are identified by the FormRepeatKey that is listed in the Form sequence number (FormSeq) column:

Note! Support for repeating forms has been added in Viedoc release 4.39. That means that if you would like to export repeating forms, you should select Viedoc version 4.39 or later in the Output version dropdown menu under Output format.

Forms initiated by copying data from previous event

The following form sequence numbers are used to make it easier to track different form instances at subject level, which are useful especially for the form instances initiated by copying the data from previous event.

  • FormRepeatKey - Counter that identifies the specific instance of a repeating form within a specific activity. This is available in the export output for Viedoc output version 4.39 and onwards.
  • SubjectFormSeqNo – Counter that uniquely identifies the instance of a specific form on a subject level, that is, it starts with 1 and it is incremented each time a new instance of the form is created for that subject. This is available in the export output for Viedoc output version 4.51 and onwards.
  • OriginSubjectFormSeqNo – For a copied form instance, it identifies the form instance from which data was copied for the first time. For the first instance of the form (that is, not copied) it gets the value of the SubjectFormSeqNo. This is available in the export output for Viedoc output version 4.51 and onwards.
  • SourceSubjectFormSeqNo – For a copied form instance, a counter that identifies the source of a copied form instance (the form instance the data was copied from). It gets the value of the SubjectFormSeqNo from which the form instance was copied. For the first instance of the form (that is, not copied) it is empty, that is, null. This is available in the export output for Viedoc output version 4.51 and onwards.

The example below illustrates how the values for these sequence numbers are assigned. The demo form used is set as repeatable and copyable and is included in Visit 1, Visit 2 and Visit 3.

We perform the following actions in Viedoc Clinic:

1 Initiate Visit 1 and fill-in three instances of the Demo form, these instances will get the sequence numbers as illustrated below:
2 Initiate Visit 2. Demo form will be available to be initiated by copying data from one of the previously filled-in form instances within Visit 1, so all the three instances will be shown as ghost forms:
3 Create an instance of Demo form within Visit 2 by copying the data from the third instance of the form filled in within Visit 1. This will result in the new form instance getting the sequence numbers as illustrated below:
4 Initiate Visit 3. Demo form will be available to be initiated by copying data from one of the previously filled-in form instances within Visit 1 and Visit 2, as below:
5 Create an instance of Demo form within Visit 3 by copying the data from the form filled in within Visit 2. This will result in the new form instance getting the sequence numbers as illustrated below:

These sequence numbers are available to be used within expressions only to get the value of the sequence number for a specific form instance, that is, by using {SubjectFormSeqNo}, {OriginFormSeqNo}, {SourceFormSeqNo}.

In the above example, the form Summary format was configured by using these sequence numbers as below:

Form Repeat Key {FormRepeatKey}, SubjectFormSeqNo {SubjectFormSeqNo}, OriginFormSeqNo {OriginFormSeqNo}, SourceFormSeqNo {SourceFormSeqNo}

Notes!

  • Only the FormRepeatKey is used to identify a specific instance of the form in data mapping for data import,as well as in the item identifier used in JavaScript (for example EventID.FormID$ActivityID[FormRepeatKey].ItemID).
  • When resetting a form, the sequence numbers are still allocated to it, and the next available ones are used for the new instances.

In the excel export output, these form sequence numbers allows to track, for the form instances that were initiated by copying data from previous events, where the data originates from, as below:

Analyzing the values of the form sequence numbers, only the form instances that were initiated by copying the data from previous visits have values populated in the Source Subject form sequence number column, that is, the last two rows in the example. The data was copied from the form instance having the same Subject form sequence number value, highlighted in green in the above image. The form instance that the data was copied for the first time is identified by the value of the Origin Subject form sequence number, that is, "3" in our example.