Report Builder

_{Share or Open on Web}

Ad Hoc Reporting via the Report Builder is both simple and very powerful. It can deliver very fine, granular data sets comprised of fields joined from multiple forms. 

Users can produce simple listing reports with little knowledge of the database.  Users can also produce highly granular reports, but having a basic understanding of the database will be a great help.

Access

Web Browser:

Mobile App:

Open

The initial creation of a new report simply involves defining the basic parameters:

• The forms that data needs to be included from

• The user roles that will have access to view the report

A couple things to notes:

• Users must first have access to the report builder. That is enough to access reports which are targeted to their role.

• Site level users will only be able to see data for their own site(s) when executing reports

Creating A New Report

Creating reports is also unique permission. Without it, users can only execute reports targeted to their own role. To create a new report, follow the steps annotated below:

1. Name the report and choose what type of data to include.  The name can be any name with a maximum of 100 characters. The report name is displayed when the report is executed so the name should be representative of the data in the report as closely as possible. Options of what to include:

   • Use reporting label instead of the field name, as defined in the form builder

   • Include records saved as a draft (not applicable if the study does not include draft saving)

   • Exclude Admin site data. By default, reports include both Provider and Admin site data.

2. Choose which form type to build a report from.  Subject forms (data collected in the subject manager) or Site forms (data collected in site documents).

3. Select which role(s) to make the report visible to - assuming the selected role also has access to the report builder. User roles that don’t have the ability to define their own reports will need reports created for them and deployed to their role

4. Save the report to populate the table. Now the report can be set up with specific data and filters as described below.

On the mobile app:

Tap the New Report button or an existing report in the table at the top of the screen:

Edit an Existing Report’s Included Forms and Targeted Roles

In the existing reports table:

1. To edit a report's basic parameters, click the "Edit" link for the corresponding report in the table.

2. Depending upon the rights granted for the user role, the Setup, Edit, or Delete controls may not be available as shown in the figure below. If the right to "Create or Setup a Report" has not been granted to your role, then the Setup and Edit controls will not be displayed in the Ad-Hoc Reports data table. 

3. If the right to "Delete Report" has not been granted to your role, then the Delete control will not be displayed in the Ad-Hoc Reports data table. Remember, if your role has been given the "Grant Access to Report Builder" right then the right to Execute a report has also been granted to your role.

Report Setup - Defining the Data

After creating or adding a new report to the Ad-Hoc Reports data table the report must be set up prior to executing the report. The "Setup" control is used to designate the report’s forms and filters. Remember, the forms that will be used in the report were selected when the report was created. Now TrialKit needs to know exactly how to use those forms for the report.  To set up a report, click the Setup link on the corresponding row of the report that is to be set up as shown in the figure below.

Clicking the Setup link will display the Report Setup page

On the mobile app, tap/hold/drag items from one box to the other

Field Selection

This area of the page allows the user to select the fields to include in the report along with the fields to use as report filters.  A report filter allows the user to select a subset of the data based on the field being filtered. Please refer to the “Execute a Report” section of this help page to see how filters work.

Each form that was included when the report was initially included will appear in the Form drop-down list. When the user selects a form from the drop-down list of forms, the fields that belong to that form will populate the Fields to Include In Report - Not Included list and the Fields to Include In Filter - Not Included list. To include fields, the user must move fields from the “Not Included” list box to the “Included” list box which is explained in the following section.

Adding Fields to a Report or Filter

There are two sections for selecting fields for the report of a filter. Each section has two list boxes as shown in the figure below.  To include a field, click on the desired field and then click the Add button. This adds the field from the “Not Included” list box to the “Included” list box. There are several ways to include fields as either columns in a report or filters for that report:

To select multiple consecutive fields, hold down the shift key and cursor down or up over the fields that are to be selected. When all desired fields are selected, click the Add button to add all the fields at once. By holding down the shift key, the user can highlight multiple fields by using any standard cursor movement including Home and End.

To select multiple fields that are not listed consecutively, hold down the CTRL key and click each field with the left mouse button. This will select each field. Then click the Add button to add those fields at one time.
To insert fields into the “Included” list box, highlight the field in the “Included” list box that the field should be inserted before. Next, select the fields in the “Not Included” list box and click the Add button. This will insert those fields before the highlighted field in the “Included” list box.

Each field that is included in the report will appear as a separate column. Each field that is included in the Filter, will appear below the report and allow the user to filter the data by that field.

Some field names appear that are not defined in the form. These fields come from system tables and are automatically joined into the report and made available.

The figure below displays the output of a highly granular report. It highlights all of the key components of the report output.

Reports can join multiple forms. There are two types of joins that Ad Hoc utilizes:

1. Standard JOIN – When the user joins two database tables, at least 1 row must exist in each table or the row will be eliminated from both tables.

2. LEFT OUTER JOIN – Opposite of the Standard JOIN. The left table row will still be included in the report even if the joined table has no matching row.  Joins and their importance when building reports are discussed in more detail in the Setup Report Forms and Filters section of this help file. Ad Hoc makes joining tables with either method as simple as checking a box.

Form Configuration

Because of the longitudinal nature of clinical trial data, it is very important to understand the relationship between the forms. Imagine a typical trial. The central entity is the subject. Case Report Form data is captured concerning the subject. How that CRF data relates to that subject is very important.

The form can basically have 3 types of relationships:

One to One
One to Many
Many to Many

One-to-One Relationship

This is the simplest of all relationships. It means there is exactly one record per subject per form. A good example might be the demographics form. Normally, there is only a single demographics form to complete per subject. If the enrollment or registration data is the master subject record, then the demographics record can be joined right alongside it.

One to Many Relationship

Things get complicated when the user wants to relate data from two forms containing many records per subject. Fortunately, Report Builder makes this easy to configure. A good example of a "Many to Many" relationship might be comparing medications to adverse events. For example, show the user all subjects (one) that had at least one medication.  The user also wants to see all the related adverse events to that medication.  Let’s assume that a related Adverse Event may be an event that occurs within 7 days of a medication start date.

Form Configuration – Know Your Forms and Relationships

The figure below shows the Form Configuration Table. This table allows the user to define how the forms relate and how to query the data from each form.

Here are some basic rules when creating a report:

1. All forms are related to the subject form unless otherwise stated.

2. The sequence in which forms appear is determined by the order designated in the Form Configuration Table.

3. If required is not checked for a form, the subject will appear in the report with empty columns if the given form does not exist for the subject. If required is checked for a form and the form does not exist, the subject will not appear in the report.

4. The user relates one form to another and uses the Days Before and Days After to select which forms are related.

Reports can be simple or highly granular. The Report Builder easily handles both and allows for an amazing amount of flexibility when creating reports. Most reports are designed to be granular data sets that can be exported to a more industrial-sized reporting engine. This alleviates the statistician much of the burden of figuring out how the data model is designed.

After making the Field and Form selections for the report be sure to click the "Save Form Configuration" button.   

Execute an Existing Report

Once a report has been created for targeted user roles, the report can be executed from the Ad Hoc Reporting Page or the Report Setup page.

When viewing the Ad-Hoc Reporting page, locate the Execute link in the existing reports data table as shown in the figure below. Clicking the Execute link will execute the corresponding report.

On the mobile app, tap any report in the table at the top of the screen and then tap the ‘play’ icon on the upper right:

Report execution can also be done from the setup screen.

Using Reports for subject casebook review

The same reports are available directly from subject casebooks on the web. The benefit with this is the report will auto-filter for the current subject’s data. This alleviates the need to review data manually through the individual forms.

From the subject’s casebook screen, access the reports from the right side

This will open a list of reports that are available to the current user’s role.

Related articles