Ad Hoc Reporting - Overview
Ad Hoc Reporting is both simple and very powerful. Ad Hoc can be thought of as a powerful data mining tool. It can deliver very fine, granular data sets comprised of fields joined from multiple forms. Users can produce simple listing reports with a 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.
A report consists of a number of components. These include:
- Report Name
- Report Type
- Targeted Roles
- Included Forms
- Fields to Include In Report
- Fields to Filter Report
- Form Configuration
The initial creation of a new report simply involves defining the basic parameters:
The forms where data will be coming from.
The user roles which will have access to this report if they have access to the report builder.
Creating a New or Editing an Existing Report
Create or Add New Report
On the Ad-Hoc Reporting page, the user must complete the entire Add New Report form. The form fields that must be completed are shown in the figure below. The first step is to name the report. 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. The checkbox located below the Report Name text field, when checked, informs the system to "Use Label as Header (as opposed to field name)." This means the Field Label assigned to the field when the form was created in the Form Builder will be used instead of the field name (also assigned to the field when the form was created in the Form Builder).
The report types are "Subject" or "Site". The report type selected from the dropdown list will determine which forms are available for the report. Once the Report Type is selected, the Included Forms checkboxes populate the section. The user can then check the desired boxes to select which forms to include in the report. The figure displayed below shows the Included Forms checkboxes when "Subject" is selected from the Report Type dropdown.
To complete the setup of a new report the "Add New Report" button must be clicked. All parameters must be completed or the save will not be successful. Once saved, the report will populate the Ad-Hoc Reports data table above the Add New Report form.
Edit an Existing Report
At the top of the Ad-Hoc Reporting page is the Add-Hoc Reports data table which contains all existing reports. To edit a report, click the "Edit" control in the desired row in the Ad-Hoc Reports data table. 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. 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 Buider" right then the right to Execute a report has also been granted to your role.
After clicking the "Edit" control, the Report Name, Use Label as Header checkbox, and Included Forms checkboxes become editable fields as shown in the figure below. By editing any of these fields the user can change the information included in the report. This allows the user to enhance and improve their reports over time. Note, when clicked the Edit control changes to "Update Cancel;" clicking the Update control will save any changes made to the editable fields. Clicking the Cancel control will leave the fields unchanged.
Setup Report Forms and Filters
After creating or adding a new report to the Ad-Hoc Reports data table the report must be setup 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 Clinical Studio needs to know exactly how to use those forms for the report. To setup a report, click the Setup link on the corresponding row of the report that is to be setup as shown in the figure below.
Clicking the Setup link will display the Report Setup page as shown in the figure below. There are two main areas to this page that operate independently of one another. These two areas are the field selection and the form configuration as shown in the following figure.
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 dropdown list. When the user selects a form from the dropdown 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 to the report of 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:
- 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.
- 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 Set up Report Forms and Filters section of this help file. Ad Hoc makes joining tables with either method as simple as checking a box.
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.
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
The one to many relationship means there are “many” CRFs related to the subject. A good example of a one to many would be Medications or Adverse Events. Most subjects are prescribed multiple medications during the course of a trial. Oftentimes a single subject may have multiple adverse events during the trial as well. These are one to many related forms. In the context of a report, if the user wants to list all subject medications, the user would expect multiple rows in the report per subject as shown in the following figure.
The word “many” refers to more than one. Therefore two or more is considered many.
Many 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:
- All forms are related to the subject form unless otherwise stated.
- The sequence in which forms appear is determined by the order designated in the Form Configuration Table.
- 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.
- The user relate one form to another and use the Days Before and Days After to select which forms are related.
Below is an example of a more complex report. The report will show all medications that were prescribed within 7 days of the implant date. The report setup is very simple as shown below. The setup relates the medications form to the implant form and stipulates only medications within 7 days to appear in the report.
The report output set up above appears like the figure below. Two medications were returned and both have a start date within seven days of the implant date.
To setup a report to see all of the Adverse Events that occurred within 14 days of a given medication add the Adverse Event form as part of the report in the Report Builder main page. Then add the desired fields from the Adverse Event form. Next, configure the report setup so that the Medications Log form is related to the Implant form and Adverse Events form is related to the Medications Log form. The report setup should like the figure displayed below.
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 the user has created and setup a report, 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.
The Right Column, when on the Setup Report Fields and Filters page, contains the "Execute this Report" box as shown in the following figure. The "Execute This Report" link can be used to execute the selected report.
When either Execute link is clicked, an Execute Report screen similar to that shown in the figure below will be displayed.
The report output will display each field that was selected during report setup as a column. All of the columns are sortable. Depending on the number of columns in the report, the scrollbar may need to be used to view all the columns in the report.
Below the report output are the report filters. The Report Filter(s) will vary depending on field type. For example, a text field or numeric field will allow the user to filter by a range. All range filters are inclusive. The means that the filters check for the value being Greater Than or Equal To, or Less Than or Equal To, within the filter. A dropdown or radio field allows the user to filter based on an item in the list. Any field in the report or any of the forms that have been selected for the report can be a report filter.
To filter the report, select the desired filter settings and click the "Filter" button located below the report filters. The report will be immediately filtered and displayed in the results data table according to the selections made. Empty filter settings will be ignored when filtering the report.
Because the Ad-Hoc Report Builder allows the user to join forms in many ways, it alleviates the burden of the database programming. Therefore, it is a very powerful tool for creating data sets for exporting. An Export to Excel link allows the user to export the entire report to an Excel data set.
The links located in the Right column can be used to navigate back to the Report Builder (Define Forms) or Report Setup (Define Fields and Filters) as shown in the figure below.