Please note: A PDF version of this guide is available for download at the bottom of the article.

This manual takes you through the iRIS Report Builder tool located in the System Administration section of iRIS™. This tool allows you to build reports, set-up report templates, and associate user access to the reports.

As you work through this manual, it is recommended that you follow along in the Report Builder to gain a better understanding of this tool. It may seem complicated at first; however, it can be a robust and powerful instrument for your institution when you learn how to use it correctly. It is recommended that you design your own basic report as you read along.

Note: iRIS™ comes with many pre-canned reports that are available for your use. The report you are trying to build may already exist in some form. Please check the Reports Administration area for reports that may be available but hidden in your system.

It is highly recommended that the report builder only be utilized by higher level System Administrators who already have a very strong working knowledge of iRIS™. The Report Builder can be used in basic report creation (first half of the manual) for less advanced users or using SQL Queries by more advanced users (second half of the manual).

Report Builder Wizard

The following sections will take you through the process of creating basic reports using the iRIS Report Builder Wizard.  Clicking on the menu link for the iRIS Report Builder or the icon in the System Administration Workspace will bring you to the screen shown below.

 

The components of this screen as described as follows:

• Report SQL Template – This area allows you to select what area in the system you want to pull data from for your report. For basic reports, always select the main iRIS Database template. More advanced information can be found in the Template Wizard section later in this manual.
• SQL Import – Import SQL is a utility that allows users to write customized SQL queries to build reports (for advanced users with SQL knowledge only), import SQL from other reports, and analyze queries to work with Jasper Reports.
• Start – By selecting this button, you will enter the report builder wizard.

• Sync with Forms - Clicking on Sync with Forms synchronizes the report builder with the System Form Designer. If you added/removed/modified a data value for a question in a form and you would like to report on that data value, you can click on the Sync with Forms button. This will be covered in more detail in the coming sections.
• Filters – Filters is a shortcut to access the filters that are available in the system. Filters are the interface of the report before being executed (e.g., drop down lists, radio buttons, check boxes, and text fields). This will be covered in more detail in the coming sections.
• My Reports – This area will populate with a list of reports that you have been working on or the reports that other users gave you access to view in the report builder. As shown in the screenshot below, you can search and filter the list of reports. You can also edit, run, download, and schedule a report using the report builder.

iRIS Report Builder:  Generating a Report

The process of generating a basic report is very simple and provides results to users in PDF, HTML or EXCEL format (depending on the access set for the report).

Step 1: Choose Your Template or Click on “Start”

Before the user can begin generating reports, the System Administrator may need to first define the SLQ Template which houses the database that the report should be pulling from (see Template Wizard within the System Administration section of this manual for additional information on configuring additional Template Wizards). To start building a report click on one of the Report Templates already provided by the system (iRIS Database is recommended) or click on the Start button.

Note: For basic users it is advised to select the Start button and build your report on the values available instead of existing templates.

Wizard Navigation

After you have selected Start on the main Report Builder page, the Database Wizard will display. The steps in the wizard are displayed above the data columns as shown below.

This Back button will return the user to a previous step in the iRIS Database Wizard. If you are at the first step in the wizard, the Back button will take you back to the previous page. This button will be available whenever the system is able to return to a previous page. In many cases, you are not allowed to go back to the previous page; specifically when the current operation involves writing files or creating binary and executable codes.

The Home button will return the user back to the main Report Builder page, prompting the user first as shown below.

Note: Clicking on Home and quitting the wizard will result in losing current session values.

This Next button will take the user to the next step in the iRIS Database Wizard. If you are at the last step in the wizard, the Next button will take you to the SQL Editor page.

Basics of the Database Wizard

The iRIS Database Wizard consist of a series of steps that guide you in the process of building a Report based on your system database. Administrators can build reports, organize, group, change the column names, modify what columns the user should see, and determine the data inside the final Report (query) using the available wizard tools.

When the user hovers over a step in the iRIS Database Wizard with their mouse, a help tip will appear listing the specific areas in iRIS™ where the database columns originate from, as seen in the image above.

To minimize the iRIS Database Wizard, you can click on the minimize tab located on the top right corner of the wizard window.

You can also jump back and forth between steps by simply selecting a specific step in the wizard.

                                            

 

My Columns will display the columns selected as you proceed through the wizard.

If you right click on any of the column names, a text box will appear and allow you to change the column name. Type in the name you want to appear on your report and then click the plus icon. The name change will only apply to the report you are setting up - it is not a permanent change. Click the red X to close the text box without making changes.

When you left click on a column name, a pop-up window will appear allowing you to edit or set conditions for your selections in that column. Below is an example:

For more information on setting Conditions on columns see below in the sections regarding generating a Report - Step 3.

Step 2: Pick Your Columns

Columns are data elements that will appear in your report. Each step is basically a category containing columns that are related to each other and grouped together. For example, in the first step all the columns refer to basic study information like the study title, status, date created, etc. Certain areas of the database cannot be represented because they are system related and will not display.

Hover your mouse on the step number and a small box will appear listing the category name and the groups of columns in that step that you can include in your report. For example, the yellow box below shows the column groups available in Step 3 of the “iRIS Database Wizard”.

The user has the ability to jump back and forth between steps as needed.

Search

The Search button is located at the top right of the page and allows the user to search the wizard by keyword.  

If you already know the column names that you would like to include in your report, simply search for those columns by keyword instead of going through the wizard step-by-step. The search popup window is shown below.

For example, you can search the iRIS database for column name ‘study id’. Below is the screenshot that shows the results of that search.

As you can see from the search results above, you might see two or three column names repeated, but be aware that each one belongs to a different table name. Based on the table name you can make your choice. For example, the keyword ‘study id’ could be found in tables STUDY, STUDY_DEVICE, STUDY_DRUG, etc. We strongly recommend that you check the table name and association before picking the column.

To pick a column, click on the checkbox and click close to add the column(s) to your report.

Step 3: Customize Your Selections (Optional)

When you pick certain columns to include in your report, you can customize each column. This means you can put a condition on what items you want to include from the column when you run your report. For example, if you are including the study status as a column in your report, you can customize this column to include only studies with a status of “Closed” when you run the report.

When you left click the column name, a new window will display as shown below.

Here you can either type in the SQL Code for the condition you need, or you can select the value and the relation. In the case of adding the Study Status column and customizing that field, this list would populate with all study statuses in the system. Sometimes this list is populated with numbers because the column stores the data as a numerical value in the database. These numbers correspond to keywords (System Constants) that iRIS™ uses behind the scenes for information processing. A key listing common keywords and their numeric values can be found in Appendix A of this manual.

Once you have chosen the Value, you can select the Relation. In our example, we have chosen the study status Value to be “Draft”. Now we’ll set the Relation to be “Equal“. This means that the report will only contain studies that have the status of “Draft”.

When you have specified both the Value and the Relation click the Generate SQL Condition button. The condition will display in the SQL Code box (See screen shot above). Click Save to assign the condition to this column. You can add additional conditions in the same way and join them using the drop-down list labeled “Join” that will appear to the right of the Relation drop down list. Selecting “And” from the Join drop-down list will only bring studies that meet both conditions into the query. Selecting “Or” from the Join drop down list will bring studies that meet either condition into the query. When you use the “Or” Join, put the entire condition in parentheses. Example:  

(STUDY.STUDY_STATUS='Draft' OR STUDY.STUDY_STATUS='Open').

It is also recommended to use additional parentheses as needed to specify the order of operations for your query.  

For advanced users: If you intend to include a chart in your report, it is highly recommended that you put a condition on the values that will be used to group your data for the chart. This condition should bring all of the results where the value is not set to “null” into the query. This can be done by clicking the column, selecting the Value and Relation, and clicking Generate SQL Code, then simply erasing the Condition Value and type “is not null.” For example, in the screen shot above, erase “Draft” and type “is not null”. Some fields included in the report may be null like a subjects Middle Initial. If a column has entries that are null, and a chart is built based upon this column name, then the chart will not generate. So be sure to set this restriction on columns if you intend to build a chart based upon those columns.

Upon saving, this window will close, and you will be return to the iRIS Report Builder Screen. Select the column that you have just put the condition on if you want that column in the report. We have selected “Study Status”,  “Study Title”, “Date Created”, and “System Study ID”.  

Continue selecting any additional columns. Follow each step to be sure you have included all columns you need in your report. If you need to rename a column, right click the words of that specific column and enter your label.

To select/deselect all the options in a specific group, click on Exclude/Include.

By clicking the green arrows next to Push Down on the far right of each group, you can rearrange each group.

Note:  If the “Attach constraint on user SQL conditions” property is on, then a user can set conditions on certain columns without selecting these columns and still have these conditions affect the information pulled into the report. For example, we might want to generate a report that pulls in subject information, more specifically the subject’s names.  Now, let’s say we only want subjects on Active studies to be pulled up in our report. Since our report is only about subjects, it would take up space to have to include a column about the study status that the subject is on. Thus, we can set a condition on the study status column to be equal to the status “Active” without having to select this column to appear in the report. The condition will still be associated with the subjects being pulled into the report. Hence, when the report is executed, only subjects that are on an Active study will populate the report.  

Continue selecting columns for your report as you step through the wizard.  

Anytime during the report building process you may skip to the end by clicking on the End button.

Step 4: Verify Your Query

After selecting the End button at the end of the selections within the Report Builder Wizard (described above) the user will be directed to the following screen.

Note for Advanced Users: The Report Builder Query Analyzer is similar to the System Query Analyzer under System Administration. The Query Analyzer allows the user to preview the SQL code and execute it before starting to build the actual Jasper Code. From here you can review the available options within the Menu (as described above). Please see the System Administration – System Query Analyzer manual for additional information regarding the Query Analyzer.

Additional settings for Basic & Advanced Users

If you are not familiar with Query data, simply review the available options within the Menu (as described below) and select the Build Report button. The system is still building the query needed to run the report for you, advanced users may want to adjust the query SQL code themselves in order to manipulate the report further.

Report Builder Analyzer Menu

The Report Builder Analyzer provides the user with a series of utilities that can be applied before the report is built. The utilities are accessible in the “Menu” area of the Report Builder Analyzer.

• Execute: Executes the query (for each report a query is being built on the back end as selections and elements are being added to the report). Selecting Execute will display the results of what has been built into the report so far (by default, the query will execute once you get to this page). Warning: Do NOT click on Execute multiple times in a row.
• Download: Exports query results to an Excel file.
• Order By: Orders the data, required for group and graph reports. To order a particular column by value, select the “Include” checkbox in corresponding row of the desired column name. This will enable the user to choose the type of ordering that will be applied to the selected column. In this case, columns can be ordered by Ascending or Descending values. For example, we will order our query by setting the Study Title to sort in ascending order by selecting the radio button for Ascending and then clicking the Apply button. When the Apply button is clicked, the report will re-execute itself and the new order will be displayed.  

• Group By: Groups results by value.

• Add Function: Applies unique functionality to the current query. This option allows users to add functionality to selected columns. For example, a user can get the total number of entries for a specific column by applying the COUNT function. If the data type for a selected column deals with numbers such as numeric or integer then more options can be applied such as finding the Max, Min, Average, Sum, and Standard Deviation.

• Condition: View current conditions and allows the user to set additional Conditions. To view conditions set during previous steps, click on the “Condition” utility under the Report Builder Analyzer Menu to display the window below

 

Click on Add at the bottom of the window and a list of the available columns to add conditions to will display as shown below.

Clicking on the next to the column listed will allow you to view/edit the SQL Code for that particular condition (see screen shot below).

• Save Query: Click the Save Query button to save this query in the System Query Analyzer menu item under System Administration. Be sure to name the Query appropriately as this is the name that the Query will display by in the Query Analyzer.
• Relations: (For Advanced users only) This option allows users to change the logic used to join tables that are used to gather your logical data. This button, in the SQL Editor Page, brings user to a routing relation list (shown below). Here the user can select the type of joining relation to be used. The report builder supports equi/inner, left, right, and full join between entities. Read “SQL Join Basics” for more information on joining tables.

The screen below does not display any results as no join relations were used in the example query.

Click on execute to make sure the query is working correctly (by default, the query will execute once you get to this page).

Warning:  Do NOT click on Execute multiple times.

A table with the results will display at the bottom of the page. Notice how each item under the Study Status column is “Draft”, this is because of the Condition placed on Study Status during the “iRIS Database Wizard” process (Step 3).  

Once you are satisfied with the Report Settings, click the Build Report button.

Step 5: Report Layout

After selecting the Build Report button, , you will be directed to the next page which will allow you to setup the layout of your report. Items such as data grouping and the size of the columns can be set within this area.

• Report Title - Here the title of the report can be edited, if needed.
• Report Template – This will allow you to associate the template that you would like the report to display in when users run the report. Portrait, Landscape and Spreadsheet (for basic reports) as well as additional graph settings (for advanced users only).

*Note: If the available report templates do not meet your needs, you can design your own. See “Designing SQL Template Wizard” for more information about this.

**Note: If you do not want to use any of the templates that you have created so far, but you only need to edit an existing template, click the Edit button. Follow the instructions in “Designing SQL Template Wizard” for more information regarding editing existing templates.

• Group Report Data (Optional) - If you want to group your data, select a column or columns in this section.  For example, if you ordered the report by Study ID then Study Status , then in the report template menu page you should pick group Study ID then Study Status so that the group Study ID is the first and the biggest group. This is required for reports that contain graphs also. (It is optional for Excel and Standard PDF.)

Note: If you modify/add/remove a column from your report, click on Order By, then click on Apply again.

 

For example, in our report we have included the “System Study ID”, “Study Title”, “Study Status”, “Date Created” columns (disregard any conditions we may have put on the “Study Status” column earlier in this document). This report will be grouped by Study Status. In order to do this, we would have had to set the Order By in Step 5 to Study Status first, and then set another Order on the Study Title. Always order the data by the column you will group by first, and then put any other order you need.

 

When you click on Study Status, the text and background will change signifying that it has be selected for grouping. To undo the selection, click on the “x” appearing to the left of the label (see screen shot below).

To view the results of grouping the report data by Study status, click on the Preview button on the top right of the screen, then click the Run Report button. Now, when our report is generated, all of the studies will be categorized by study status. These status categories will display in alphabetical order as we set it to.  Additionally, the studies in each status category will display the studies alphabetically by study title (see below).  

• Arrange Your Columns (Optional) – From here the columns can be re-arranged as needed, simply grab and drop the boxes in the order that you would prefer.
• Resizing Columns (Options) – From here the columns can be resized as needed, simply reset the number percentages listed for each. Keep in mind, they must total 100%. iRIS™ chooses a percentage based on the standard length of the columns pulled in to the report, compared to the other columns in the report, so generally, the length predefined is acceptable for the report template, however, you may change the size as needed.

Step 6: Adding Filters (Optional)

When you publish the report (step 10), you are making this report available for users in the system. You can create filters that the user can set to control what data is pulled into the report. Here you have the option to choose filters that will appear for the users. When clicking the More Options button in the Report Builder main page, the following screen appears. (Important: Be sure to save your report prior to clicking More Options.)

Choose Manage Filtering and the following screen appears:

To add filters to your report simply click on the Add button and the following screen will appear.

To select a filter that the user can use before running the report, click the check box next to the desired filter (only those filters associated with your report will be shown). Once you are satisfied with the selected filters, click on the Save Addition button (see screen shot above).

A Filter Dictionary is available at the end of this manual, if needed.

If you do not see a filter that fits your needs, you can always add your own by clicking on the Configuration button.

The following screen will appear.

The folders (if shown) next to a filter indicate that there are reports that are already published using that specific filter. Clicking on each yellow folder will open details of the reports using that filter. Clicking on an opened folder will close it.

To edit a filter, click the button. Caution: Editing the filters with yellow folders changes the reports that include those filters. To add a brand new filter, click the Add button. Fill in all of the fields and choose a display type.

Click Save.

In order to save time, you can create a filter for a specific column in your report by clicking Add using my report column. When you click this button, choose the column of your report you want to filter and then the screen above will appear partially populated. You must enter a prompting value and choose a display before saving.

1. The drop-down list creates a list of items that the user can choose from.
2. The check boxes allow the user to choose multiple selections.
3. The text field allows the user to type in a value.
4. The radio buttons allow the user to choose only one item.

Once finished, click Save.

To use this filter return to the main filter page by clicking the Back button.

In the main filter page, click Add and check the box next to the newly created filter and click Save Addition.

To delete a filter from the current report, check the box next to the unwanted filter and click Delete.

To remove the newly created filter completely from iRIS Report Builder, navigate back by clicking on the Configuration button.

Check the box next to the newly created filter and click Delete.

Caution: It is not recommended to delete filters with Pre-defined values of “Yes”, as this will cause issues with reports that came (pre-canned) with iRIS™.

Building Filters Report Interfaces (For Advanced Users)

iRIS™ comes with a pre canned list of filters that can be used to filter data in a report. The report builder allows users to build their own filters and their own drop-down lists, check boxes, radio buttons, and text fields. For example, if you are gathering information on certain questions in the form, you can create a drop down list to filter your data based on the values in your form. This feature allows departments to customize the report interface to their preference based on their own form and report requirements.

Step 7: Adding Group Calculations (Optional)

Enabling this feature allows you to view group calculations on selected columns. In order to do this you must ensure that your query is ordered by the selected column(s), in our example we have selected Study status (see screen shot below).

Once you are finished grouping your columns and you have clicked Save, click More Options.

Click on the check box under Group Calculations. By marking this feature as checked your report will calculate a count for each group in the report. To turn off this feature simply uncheck the box.

Once you have enabled group calculations click Save then click Preview. The results of having group calculations in your report can be seen in the screen shot below.

Step 8: Preview Report

To see what your report will look like as a PDF click on the Preview button.

Your default browser will open the report and you can download or print the PDF file.

Once you are finished editing the report and you have clicked Save, click More Options and choose Publish Report. This step will install the report in your system. After publishing the report, the report will be part of your iRIS™ Application.

IMPORTANT Note: If when you click More Options, it reads Un-Publish Report, click on Un-Publish Report and then re-publish it. To do this, click on More Options once again and then click Publish Report.

Make sure you reach the screen below to ensure that you have started the publishing process.

This step allows you to specify where in the system your report will be available. There are reports that the user can access in the Review Board Assistant, in Study Assistant, in System Administration, and in many other places. Report Type dropdown list will allow you to select which area of iRIS™ to display the report in. Different review boards can be set, thus the report you’ve created will be available in the Review Board Assistant. If you put the report in the Study Assistant, you can choose to give access to it for all departments (in which case you would select Study Assistant-All Departments), or you could make this report accessible to Department Reports (in which case you would select Study Assistant – Departments). When finished editing this screen, click Save Report Definition.  

Building a Report with Graphs (For Advanced Users)

For illustration purposes, the 2D bar graph report will be used for the example. Click the 2D Bar icon under Report Template and then choose 2 groups under Group Report Data. For this example, 1) Study status and 2) Study title have been selected.

You can rearrange the order of your group by clicking the red “x” next to each group. By clicking the More Options button in the upper right corner, you can add a filter under Manage Filtering.

Note: You must save your reports in order to manage filters.

Click the Add button and select your filters. You can also configure your selection and delete selections. When finished configuring and adding filters, click the back button in iRIS™ and click Save. For more information on filters see the Generating a Report - Step 7 section of this manual. You can now Preview the report. Depending on the configurations of the filters chosen, you may have the ability to choose certain items that appear in your graph.

For this example, only studies that are Draft, Pending – Submitted for Initial Review, and Expired are checked. Since Departments were grouped first the report data pertaining to Study Statuses will be grouped by departments selected. In this example the departments selected are: Academic Information Systems, CPFM-Utilities, and School of Medicine.

After clicking Run Report, the graph will appear in the last page.

Notice the categories we defined are different departments in the system, and the heights of the bars signify the number of studies with that particular status. Also, notice that the number of studies for each status is calculated within every department.

If your report includes a pie chart or a graph other than 2D, you will follow the steps above. The only difference will be selecting the appropriate icon under Report Template.

For example when selecting 3D Pie graph using the same report data (see screenshot below).

Report Source Code JRXML (For Advanced Users)

To access the JRXML file, click on the More Options button (see screen shot below).

You can then choose to Download Report Source Code which will appear in a pop-up page like this:

After selecting Download Report Source Code the JRXML source will become viewable on another page.

Alternatively, the JRXML file can be downloaded from the iRIS Report Builder home page by navigating to My Reports and clicking on the Download icon.

Report Builder Summary (Steps)

Step 1: Pick your columns by checking the checkboxes, then click on END.

Step 2: Click on Order By and order your data (optional), then click on Build Report.

Step 3: (optional) Click your report groups, report title, and arrange your columns the way you like.

Step 4: Save and then Preview.

Step 5: If you would like to add a filter or a count. Click on more options and then add filter and/or check the calculate count check box.

Step 6: Publish the report by clicking on More Options, then un-publish, then again click on More Options, then publish.

Scheduling Reports

To schedule a report to run automatically, click on the icon next to the report you want to scehdule. The system will take you to the screen show below.

To add a schedule to this report, click on the Add a scheduled report button. This will open the page shown below.

Scheduling STEP 1: Execution Pattern

From this screen you can select the Execution Pattern for your report. The date pattern is set up by choosing a Start Date, an End Date (optional), and selecting a Pattern for recurrence from the drop-down box. If you are uncertain how long a period you want the report to run, you do not have to select an end date. By leaving this open, your report will continue its scheduled pattern indefinitely. You can choose to have your report run on one of the following Recurrence Patterns: Daily, Weekly, Monthly, or Yearly.

Daily – When choosing the Daily Recurrence Pattern, you have the option to run your report every day, every 2 days, 3 days, and so on: up to 30 days (as shown below).

 

Weekly – The Weekly Recurrence Pattern lets you schedule reports in weekly increments, from every 1 to 5 weeks. You can also indicate the day(s) that you want the report to run.

Monthly – The Monthly Recurrence Pattern allows you to schedule reports on a monthly basis, from every 1 to 11 months. This pattern also incorporates two different schedule patterns – by the week or by the day. First, you can choose a specific week of the month (1 to 4), in conjunction with a specific day or days of that week (Sunday through Saturday). The second pattern allows you to pick which day of the month the report is to run (the 1st through the 28th).

Yearly – The Yearly Recurrence Pattern lets you schedule your report every 1 to 2 years. The specific month and day the report is to run can be selected as well.

Once you have established your pattern, click Save and Continue in the upper right corner of screen.

Scheduling STEP 2: Report Filters

Step 2 is where you manage report properties. These properties will vary depending on the report you are scheduling.

Make your selections and then click Save. The following screen will appear, displaying your report type and the properties you selected.

From this screen you can now do the following:

Edit Filters – Clicking this button will take you back to the previous screen where you can change your report property selections.

Run Test – Clicking this button will run a test of the report with the properties that you selected.

Scheduling STEP 3: Users

The user setting up the schedule will automatically receive the report based on the execution pattern. However, you can select additional iRIS™ users that the report should be sent to. You can also delete a user from this list at any time.

Add User(s) – When you click the Add User(s) button, a popup search window will appear. Enter either the first or last name of the user you would like to add to the report schedule. If you are unsure of the spelling, you can enter a portion of the first or last name. If you know the user’s primary department you can also include that in your search criteria. Once you have entered your search criteria, click Find. Click the icon in the Select User column to add the user to the scheduled report.

After adding a selected user, their information becomes available under the Users tab in Step 3 (see screenshot below).

A checkbox will also become available in the first column for newly added users.

Delete User(s) – If you need to remove a user from this list, simply check the record to be deleted and click the Delete User(s) button at the top right-side of the screen.

When a user is added to a scheduled report, that user now has the ability to change the schedule through the System Report Administration, if they have appropriate access.

History

Within the History tab is a table that contains records for each time the report is run, including test runs, as shown in the screenshot below.

This table contains the following columns:

        Audit # – Report Audit ID.

Report Title – Name of report.

Download – Clicking this icon will download the report in the format selected when scheduled.

Schedule Name – Gives a brief description of the schedule (how often it is set to run) if the report was executed by the system and not manually.

User – The name of the user that the report was sent to.

Last Run – The date the report last ran.

Email – The email address of the user listed.

Note: In order for the History Tab to be visible, set the system property system.use_report_auditing to “Yes”. Find this property by navigating to System Administration > System Configuration > Reports Administration. This property (when enabled) gives privileged users the ability to track the history of a report. Each time a report gets executed, the system will back up the output document and store it in the database.

Notes:

1. Make sure that the user has access to your report in the place that you put the report in the system.  Then click Report Builder from the My Workspaces navigation pane at the top of the window and your report will be shown under My Reports.
2. If the property in System Configuration under the Reports Administration group called system.grant_user_access is set to “Yes”, then in Reports Administration you will have to grant users access to reports manually or by role. This is useful if you only want certain users to be able to use this report. If this property is set to “No”, then everybody will have access to your report.  
3. If you have a report that generates subject information, you may not want the user to see all subjects in all departments. You can control access for this in a number of ways. You can put a condition on certain columns that you are including, or you can control the filters that the user has access to. In our example, the best way to control the departments a user sees would be to include this column in the report. When publishing the report, a filter can be selected called SYSTEM_DEPARTMENT and the filter type is Default Department only. This will limit the data to include only subjects that are associated with the user’s department running the report.

Designing SQL Template Wizard

The “SQL Template Wizards”, or simply the “Template Wizards”, are tools created by the administrator to help other users generate SQL code easily. The predefined wizard already has an advanced setup of 14 or more steps that cover nearly areas of the database. Using this wizard can be complicated and time consuming for other system users. Other template wizards can be created to minimize the steps available and eliminate unnecessary steps. In other words, users are able to create and design wizards for other users to make the report building process easier and faster.

SQL Template Wizard is a set of columns and tables that are customized to meet certain conditions. Not only does it minimize the number of steps for the user to generate the final query, but also it allows the administrator to determine what rows should show up in the final query (i.e., adding a condition to pull out all studies that belong to Department A). More details will be covered in the coming sections.

Create View

The users who have access to the Template page will be able to create wizards. Start off by clicking on the Create View icon in the Report SQL Template section of the Report Builder home screen. Initially the Database Wizard appears. Below is an example of adding columns to your report SQL Template:

In the above example, columns: “System Study ID”, “Study title”, “Date created”, and “Study status” are selected. As a result the selected columns are listed in the area labeled “My Columns” which ultimately will be included in your report, see example below.

Note: Clicking on a specific column name in the “My Columns” area will link the user to the location of that column in the iRIS Database Wizard.

Setting Conditions to your SQL Template

You can add condition(s) to the SQL template (left-click column name) in order to generate a specific view of the data that the user is trying to report on, for example you can add a condition to limit the studies to only “Draft” status, or you can limit the data generated from the query to a specific department or institution. A screenshot of the popup window is shown below.  

Parameter Place Holders

You can also add a place holder in your template so that it will be replaced at execution time with the logged user information, such as the user’s ID, studies, or departments. For example:

$P{USER_DEPARTMENT}

Including this parameter in a query will restrict the user to the department he or she has access to. If that group is joined with the study, then the user will only see the studies within his or her department(s).

$P{LOGGED_USER_ID}

Including this parameter in a query will replace the LOGGED_USER_ID with the current user who is running the query. You can use this place holder to restrict the user to see data that is limited to his/her access, or see information that is related to the user.

$P{USER_STUDY}

Including this parameter in a query will only display studies that the user has access to, or has any role on the study. 

After you have selected all of the study information for your template, click on and the following screen will appear.

Here you will determine how your Wizard Template will be viewed and used. The icon selection you choose will appear on the button in the menu bar on your home screen.

Editing Existing Report SQL Templates

Once a Report SQL Template has been made it can be edited from the iRIS Report Builder main menu.

When you click on the edit icon on the top left of the Report SQL Template section on your home page, you will be brought to a screen with a list of existing SQL Templates.

You may add or delete templates via the Add and Delete buttons in the upper right-hand corner of the screen.

Clicking on the Add button will take you back to the iRIS Database Wizard where you are able to go through all the steps of making a Report SQL Template.

Clicking on the Delete button removes a selected template from the list.

If you click the icon in the Edit column for an existing SQL Template, you will be brought to the SQL Wizard Information screen where you initially created the view for your Wizard Template.

Access Control

The Access button allows you to give users access to the Template Wizard button on the Home screen.

There are two main access types:

• Access By User’s System Role
• Access based on “User Name”

Granting Access by user’s System Role enables an administrator to allow users of a specific role(s) in the system to have access to a particular Report SQL Template. An administrator is also able to be more direct in granting access to a Report SQL Template by having the ability to search for users by name.

 

 

System Administration

This section will guide you through the higher level settings for the System Report Builder, such as the available properties within System Configuration that allow you to turn on/off Report Builder features. Additionally, this area will review creating templates that enable users to create reports from a specific database.

Report Administration Properties

This area covers the system properties related to the report builder. You can find these settings by navigating to System Administration > System Configuration > Reports Administration.

Please see the System Administration – System Configuration Manual for additional information regarding the System Properties found in this area.

Grant User Access

Setting this option to “Yes” (default is “No”) will allow the Administrator to control access to specific reports based on System Roles. Report Administration is located in: System Administration > System Report Administration.

When this property is set to “Yes” the User Access option will become available from the iRIS Report Builder home screen, as seen in the image below.

Hide Excel Study Plane

This property gives the user the option of exporting data to an Excel format when clicking the Print Friendly button in an Application, Study Plan construction, or in a Submission.

Report Debug Messages

Setting this option to “No” (default is “Yes”) will hide the status of the last report operation when runtime exceptions are displayed.

Enabling CSV Report Format

Setting this option to “Yes” (default is “No”) will give users the option of exporting reports into *.csv format.

Report Auditing

Setting this option to “No” (default is “Yes”) disables the display of audit information related to a specific report (how often a report is run and the data that was contained in the previously run report).

When this property is set to “Yes” the History tab will become available in the Report Scheduler, as seen in the image below.

Enabling Report Scheduler

Setting this option to “No” (default is “Yes”) will disable the ability for users to schedule reports to be run and emailed to them at specific times.

When this property is set to “Yes” the report scheduling feature will become available within the iRIS Report Builder home screen, as seen in the image below.

Report Builder Availability

It is possible to hide the iRIS Report Builder from even System Administration Users. This feature when set to “No” will remove the report builder tool from the System Administration dropdown on the home screen.

Limit Returned Date Size

It is possible that some queries will pull a huge amount of information that the system may not be able to handle or allocate in memory; this may cause the system to crash. This feature allows the administrators to put an upper limit on the number of rows returned from the database server in the Query Analyzer.

Max Rows Returned

This feature will be effective if the above property is set to “Yes”. This property sets the maximum number of rows returned from the query.

Report Builder Dashboard

This property, when set to “Yes”, will provide the users with a Report Builder Dashboard on their home screen.

For Advanced Users

Additional SQL Configurations

This area covers additional SQL configurations.

Allow Auto Clear Cached Data

Each time the user uses the Report Builder tool, different types of files will be generated and cached in the root directory. These files could be a problem in the future if the server has limited memory. The system will track the number of reports and if the number reaches 100 times it will automatically clear everything. The reports that have been published already will not be affected since the published reports are copied to a permanent directory. However, all unpublished reports will be cleared from the temporary folders.

Custom Column Names

The user has full control of what the final query column names can be displayed as. By default query column names are taken from the database columns, but the user can explicitly choose other display names. The user can add the “AS” keyword to a query to give a column a new displayable name.

For example:

STUDY.STUDY_ALIAS AS 'Study Number'

Attach Constraint on user SQL conditions

This option determines whether or not the generated query must take into consideration the user SQL clauses. For example, if a user has set a condition on a study and has selected columns from study submission, this property will determine whether or not these submissions should meet the study criteria.  

For example:

The screen shot above illustrates a constraint that will limit query results to draft study statuses only. More on SQL conditions will be discussed in later sections.

See Setting Conditions to your SQL Template

Distinct SQL Selection

The user can add the “DISTINCT” keyword to the Query if this property is turned on (see example below). Users must know that some data types require casting, such as Study Title. This property is related to the casting property, as some databases do not allow for huge data types. For example, adding the “DISTINCT” keyword to the query eliminates duplicate rows from the results.

SQL query using the “DISTINCT” keyword:

SELECT DISTINCT TOP 2000 STUDY.STUDY_ALIAS AS 'Study Number' ,STUDY.STUDY_STATUS AS 'Study status' FROM STUDY

Depicted below is an example of the kind of results a user can expect after executing a query using “DISTINCT”.

Use Casting Types

Using the SQL “CAST” keyword will help in resizing the columns coming from the database. For example the Study Title could be installed in the database as 2,147,483,647 characters (text data type or CLOB for Oracle), and this data type and similar ones are considered too large. If these large data types are cast to smaller sizes, it will improve the report display, as well as the efficiency of the memory and execution time.

Casting Data type:

The casting data type is the representation of information explicitly defined by the user. For example, a user can cast text to a variable character field (varchar) by including ‘varchar’ after the ‘AS’ keyword in a SQL SELECT query.

Casting Data type Length:

This property represents the maximum length of the casted column.

For example the SQL query below declares STUDY_ALIAS to be represented as a ‘varchar’ and for the results of selecting STUDY_ALIAS to be limited to 5 characters in length.

SELECT CAST(STUDY_ALIAS AS varchar(5)) AS 'Study Number' FROM STUDY

SQL Join Basics

Join Relation between Tables

Currently this tool has four joining relations (Equi/Inner, Left, Right and Full joins).

Equi (Inner) join:

SQL query using Equi (Inner) join:

SELECT A.SYSTEM_STUDY_ID, A.MASTER_SUBMISSION_ID, A.SUBMISSION_ID, B.RB_SUBMISSION_ID, A.MEETING_ID FROM A, B WHERE A.SUBMISSION_ID = B.SUBMISSION_ID

Resulting Table:

 

 

 

 

 

 

 

 

 

Left Join:

SQL query using left join:

SELECT A.SYSTEM_STUDY_ID, A.MASTER_SUBMISSION_ID, B.SUBMISSION_ID, B.RB_SUBMISSION_ID, A.MEETING_ID FROM A LEFT JOIN B ON A.SUBMISSION_ID = B.SUBMISSION_ID

Resulting Table:

 

 

 

 

 

 

 

Right Join:

SQL query using right join:

SELECT A.SYSTEM_STUDY_ID, A.MASTER_SUBMISSION_ID, A.SUBMISSION_ID, B.RB_SUBMISSION_ID, A.MEETING_ID FROM A RIGHT JOIN B ON A.SUBMISSION_ID = B.SUBMISSION_ID

Resulting Table:

 

Full Join:

SQL query using full join:

SELECT A.SYSTEM_STUDY_ID, A.MASTER_SUBMISSION_ID, A.SUBMISSION_ID, B.RB_SUBMISSION_ID, A.MEETING_ID FROM A FULL JOIN B ON A.SUBMISSION_ID = B.SUBMISSION_ID

Resulting Table:

 

SQL Import Utility

Creating Reports from User’s SQL

We recommend that unless you are familiar with SQL, or you have access to queries and files with proper SQL language, you avoid using this aspect of the tool until you become more familiar with how the Report Builder works. There are several ways to get the SQL code that you need to build a report.

1. You can enter SQL code in the text box labeled “SQL Source Code” shown below.
2. Import SQL code from a Jasper report.
3. Open the Query Analyzer and get an existing query that you are interested in.

Execute SQL Code

The Execute SQL Code button will execute queries entered in the “SQL Code” textbox by sending a request to the database to verify the correctness of the SQL Code. Then it will start paring the results to make sure that the entered code will be compatible with the Jasper JRXML code. For example, by entering the SQL Code:

SELECT * FROM STUDY

Then click on Execute SQL Code. If the SQL/Jasper Code is successfully executed the Operation Status will appear as shown in the screenshot below.

Otherwise, if the SQL/Jasper Code is unsuccessfully executed the operation status will appear in red text with a detail message on the sort of error that was encountered.

Additionally, a table of the parsed columns of the query will be listed below:

The Field Name is the column name that will be used to create the Dynamic Field in the Jasper Report. The Field Table column is used when you need to associate a column to a specific table. For example:

You can have the SYSTEM_STUDY_ID column in the following two tables

1. STUDY
2. STUDY_SUBMISSION

To tell the Report Builder that you want to show the SYSTEM_STUDY_ID in the STUDY table, not the STUDY_SUBMISSION table, enter the table name STUDY in the Field Table column. The report will know that these columns must pull data from the STUDY table and not from the STUDY_SUBMISSION table. It is important to specify the table name when column names are shared by multiple tables in the query. It also removes ambiguity in the SQL Code.

The Field Type column represents the data type of the column in the Jasper code and in the database.

The Field Validation column will show the user if the column is valid to be used to build a report. An example of an invalid column would be a column that does not have a title. For example, by entering  this INVALID SQL Code:

SELECT COUNT(*) FROM STUDY

Produces an invalid field validation since no Field Name was specified, see below:

By specifying the Field Name the SQL Code becomes valid, see example below:

SELECT COUNT(*)  AS  NUMBER_OF_STUDIES FROM STUDY

After executing the query, you will be able to pick the columns that you are interested in.

Remove Field

The Remove Field button in the menu will remove the selected columns from the query result set.

For example, by selecting the first row from the query result set and clicking on the Remove Field button, the selected row no longer appears in the result set.

To undo the removal of a field, click on the Execute SQL Code button again to re-establish the original result set.

Add Query Analyzer

You can add the SQL code to the Query Analyzer; the saved query statement will be available from the Query Analyzer.

Import Report

A pop-up window will ask you to locate the Jasper report file on your local hard drive. The file must be “.jasper”. After selecting the file, the system will re-compile the report to get the JRXML code and will populate the SQL code in the “SQL Source Code” textbox for you to view/edit.

Open SQL Query

This button gives you the option to open a SQL query from the Query Analyzer, which has a list of your previously used queries. This is helpful if you do not want to go through Step 1 to Step 6 in “Generating a Report” if you have already defined the query needed to build your report.

Build Report

When you are satisfied with your SQL Code, click the Build Report button. You will then be directed to the details page where you can choose your report template, group and resize columns, and publish the report.

 

Appendix A

System Constants – For Step 3

Under User Study Access and Study Budget Person column groups, the column “Access Type” may refer to the values listed below:

System Constant Name	Numeric Value	Label
PRINCIPAL_INVESTIGATOR	1	Principal Investigator
STUDY_CONTACT	2	Study Contact
COINVESTIGATOR	3	Co-Investigator
ADDITIONAL_COORDINATOR	4	Study Coordinator
ADMINISTRATIVE_ASSISTANT	5	Administrative Assistant
REVIEWER	6	Reviewer
STUDY_AUTHOR	7	Study Author
FACULTY_ADVISOR	9	Faculty Advisor
DEPT_ADMINISTRATOR	10	Department Administrator
SMO_PROJECT_MANAGER	12	Project Manager
SMO_REGULATORY_SPECIALIST	13	Regulatory Specialist
SMO_DATA_COORDINATOR	14	Data Coordinator
NURSE	15	Nurse
PARTICIPATING_CLINICIAN	16	Participating Clinician
CRA	17	Clinical Research Associate
BIOSTATISTICIAN	18	Biostatistician
SUB_INVESTIGATOR	19	Sub-Investigator
DATA_MANAGER	20	Data Manager
VETERINARIAN	21	Veterinarian
TECHNICIAN	22	Technician
SYSTEM_DEFINED_KSP1	23	Study KSP1
SYSTEM_DEFINED_KSP2	24	Study KSP2
SYSTEM_DEFINED_KSP3	25	Study KSP3
SYSTEM_DEFINED_KSP4	26	Study KSP4
SYSTEM_DEFINED_KSP5	27	Study KSP5
SYSTEM_DEFINED_KSP6	28	Study KSP6
SYSTEM_DEFINED_KSP7	29	Study KSP7
SYSTEM_DEFINED_KSP8	30	Study KSP8
SYSTEM_DEFINED_KSP9	31	Study KSP9
SYSTEM_DEFINED_KSP10	32	Study KSP10
SMO_CRA	33	Clinical Research Associate (SMO)
SMO_CTC	34	Clinical Trial Coordinator

 

Under the Study Submissions column group, the column “Submission Type” may refer to the values listed below:

System Constant Name	Numeric Value
TT_INITIAL_REVIEW	12
TT_CONTINUING_REVIEW	13
TT_AMMENDMENT	5
TT_ADVERSE_EVENT	3
TT_IND_SAFETY	4
TT_REVIEW_RESPONSE	14
TT_DEVIATION	19
TT_STUDY_CLOSURE	20
TT_RETURN_PREREVIEW_CORRECTION	-1
TT_UMBRELLA_CONTINUING_REVIEW	22
TT_INACTIVATION	24
TT_ADMINISTRATIVE_ACTION	25
TT_MISCELLANEOUS	26
TT_FEASIBILITY	28

 

Under the Review Board Submission category, values for determining the review board process may refer to:

System Constant Name	Numeric Value
PREREVIEW_NOT_ASSIGNED	0
PREREVIEW_BOARD_REVIEW	1
PREREVIEW_EXPEDITED	2
PREREVIEW_EXEMPT	3
PREREVIEW_FORWARD_TO_BOARD	4
PREREVIEW_FORWARD_TO_COMMITTEE	5
PREREVIEW_DENIED	6
PREREVIEW_ADMINISTRATIVE	7
PREREVIEW_EMERGENCY_USE	8
PREREVIEW_DESIGNATED_REVIEWER	9
PREREVIEW_FACILITATED_REVIEW	10
PREREVIEW_AOR_SIGNOFF	11
PREREVIEW_AOR_EXEMPT	12

 

Under the Study Funding Agencies category, values for determining the funding source group may refer to:

System Constant Name	Numeric Value
EXTERNAL_FUNDING_SOURCE	1
INTERNAL_FUNDING_SOURCE	2

 

 

Values for determining the agenda type state may refer to:

System Constant Name	Numeric Value
AGENDA_CONTENT	1
REVISION_CONTENT	2
MINUTES_CONTENT	3

 

Values for determining the meeting state may refer to:

System Constant Name	Numeric Value
MEETING_DRAFT	1
MEETING_FINALIZED	2
MEETING_REVISION	3

 

Values for determining notification types may refer to:

System Constant Name	Numeric Value
NOTIFY_CONTINING_REVIEW	1
NOTIFY_AGENDA_REVIEWER_ASSIGNMENT	2
NOTIFY_REVIEWER_UNASSIGNMENT	3
NOTIFY_PREREVIEW_AGENDA_ASSIGNMENT	4
NOTIFY_PREREVIEW_EXPEDITED_ASSIGNMENT	5
NOTIFY_PREREVIEW_EXEMPT_ASSIGNMENT	6
NOTIFY_SUBMISSION_PENDING_EVENT	7
NOTIFY_EXPEDITE_REVIEWER_ASSIGNMENT	8
NOTIFY_EXEMPT_REVIEWER_ASSIGNMENT	9
NOTIFY_MEETING_ATTENDENCE_REVIEW	11
NOTIFY_ATTENDANCE_CHANGE	12
NOTIFY_STUDY_SIGNOFF	13
NOTIFY_STUDY_SIGNOFF_REMINDER	14
NOTIFY_STUDY_SIGNOFF_EXCEEDED	15
NOTIFY_REVIEW_RESPONSE_REQUIRED	16
NOTIFY_REVIEW_RESPONSE	17
NOTIFY_REVIEW_CORRECTION	18
NOTIFY_STUDY_PI_SIGNOFF	19
NOTIFY_ADMIN_REVIEWER_ASSIGNMENT	20
NOTIFY_NEW_ACCOUNT_REQUEST	21
NOTIFY_STUDY_INVOICE_SIGNATURE	26
NOTIFY_STUDY_SIGNOFF_DENIAL	27
NOTIFY_RB_LETTER_SIGNOFF	28
NOTIFY_ACCOUNT_CREATED	29
NOTIFY_ACCOUNT_CREATED_PENDING_ACCEPTANCE	30
NOTIFY_PENDING_ACCEPTANCE	31
NOTIFY_STUDY_SIGNOFF_REMOVED	32
NOTIFY_RB_LETTER_OUTCOME	33
NOTIFY_RB_LETTER_CORRESPOND	34
NOTIFY_FD_DISCLOSURE	35
NOTIFY_PREREVIEW_EMERGENCY_USE	36
NOTIFY_EMERGENCY_USE_REVIEWER_ASSIGNMENT	37
NOTIFY_OUTSTANDING_STIPULATION	38
NOTIFY_RENEWEL_EXPIRATION	39
NOTIFY_DESIGNATED_REVIEWER_ASSIGNMENT	40
NOTIFY_INTERNAL_DOC	41
NOTIFY_STUDY_INVOICE	42
NOTIFY_PATIENT_TASK_COMPLETE	43
NOTIFY_MILESTONE_OCCURRED	44
NOTIFY_RB_INTERNAL_SUBMISSION_REVIEW_ASSIGN	45
NOTIFY_DESIGNATED_MEMBER_ASSIGNMENT	46
NOTIFY_DESIGNATED_REVIEW_AGENDA_ASSIGNMENT	47
NOTIFY_PASSWORD_REQUEST	48
NOTIFY_LOCKED_ACCOUNT	49
NOTIFY_TRAINING_EXPIRATION	50
NOTIFY_UPDATE_CV_REMINDER	51
NOTIFY_EXPIRE_ACCOUNT_ADMIN	52
NOTIFY_EXPIRE_ACCOUNT_USER	53
NOTIFY_AUTO_REPORT	54
NOTIFY_TRAINING_ABOUT_EXPIRE	55
NOTIFY_RECRUIT_FOLLOWUP	56
NOTIFY_RECRUIT_LETTER	57
NOTIFY_REVIEW_ASSIGNMENT_BY_STUDY_ROLE	58
NOTIFY_REVIEW_ASSIGNMENT_BY_STUDY_ROLE_DENIAL	59
NOTIFY_REVIEW_ASSIGNMENT_BY_STUDY_ROLE_REMINDER	60
NOTIFY_REVIEW_ASSIGNMENT_BY_STUDY_ROLE_EXCEEDED	61
NOTIFY_REVIEW_ASSIGNMENT_BY_USER	62
NOTIFY_REVIEW_ASSIGNMENT_BY_USER_DENIAL	63
NOTIFY_REVIEW_ASSIGNMENT_BY_USER_REMINDER	64
NOTIFY_REVIEW_ASSIGNMENT_BY_USER_EXCEEDED	65
NOTIFY_EQUIPMENT_INVENTORY	66
NOTIFY_EQUIPMENT_DISPOSITION	67
NOTIFY_PREREVIEW_FACILITATED_ASSIGNMENT	68
NOTIFY_STUDY_AUTO_EXPIRE	69
NOTIFY_FORM_SECTION_ASSIGNMENT	70
NOTIFY_RB_FINAL_MEETING_AGENDA	77
NOTIFY_RB_REVISED_MEETING_AGENDA	78
NOTIFY_RB_FINAL_MEETING_MINUTE	79
NOTIFY_RB_DRAFT_MEETING_MINUTE	80
NOTIFY_RB_OUTCOME_SIGNOFF_COMPLETE	81
NOTIFY_RB_REVIEWERS_COMPLETE	82
NOTIFY_RB_INTERNAL_ROUTING_COMPLETE	83
NOTIFY_ADDITIONAL_SUBMISSION_REVIEWER	84
NOTIFY_TIME_ACCOUNTABILITY	85
NOTIFY_RB_MEETING_MINUTES_ROUTING	86
NOTIFY_FACILITATE_REVIEWER_ASSIGNMENT	87
NOTIFY_WAITING_SUBMISSION	88
NOTIFY_WAITING_SUBMISSION_REMINDER	89
NOTIFY_BUDGET_PUBLISHED	90
NOTIFY_OPEN_COST_CENTER	94
NOTIFY_CLOSE_COST_CENTER	95
NOTIFY_ANNUAL_COI	96
NOTIFY_ANNUAL_COI_LAPSE	97
NOTIFY_ANNUAL_NEW_HIRE	98
NOTIFY_ANNUAL_FAILURE_TO_SUBMIT	99
NOTIFY_FIRST_REVIEW_COMMITMENT	100
NOTIFY_FIRST_REVIEW_COMMITMENT_REMINDER	101
NOTIFY_FIRST_REVIEW_COMMITMENT_DEADLINE	102
NOTIFY_SECOND_REVIEW_COMMITMENT	103
NOTIFY_SECOND_REVIEW_COMMITMENT_REMINDER	104
NOTIFY_SECOND_REVIEW_COMMITMENT_DEADLINE	105
NOTIFY_COVERED_PERSON_COMMITMENT_CHANGE	106
NOTIFY_COVERED_PERSON_COMMITMENT_CHANGE_REMINDER	107
NOTIFY_COVERED_PERSON_COMMITMENT_CHANGE_DEADLINE	108
NOTIFY_PROTOCOL_KSP_STUDY_COI	109
NOTIFY_PROTOCOL_KSP_STUDY_COI_REMINDER	110
NOTIFY_PROTOCOL_KSP_STUDY_COI_DEADLINE	111
NOTIFY_SPONSOR_DUE	112
NOTIFY_GRANTS_OFFICE_DUE	113
NOTIFY_TIMESHEET_SUBMISSION_DUE	114
NOTIFY_TIMESHEET_PAST_SUBMISSION_DUE	115
NOTIFY_REVIEW_ASSIGNMENT_BY_SYSTEM_ROLE	116
NOTIFY_REVIEW_ASSIGNMENT_BY_SYSTEM_ROLE_DENIAL	117
NOTIFY_REVIEW_ASSIGNMENT_BY_SYSTEM_ROLE_REMINDER	118
NOTIFY_REVIEW_ASSIGNMENT_BY_SYSTEM_ROLE_EXCEEDED	119
NOTIFY_PREREVIEW_DENIED	120
NOTIFY_COI_REVIEW_RESPONSE	121
NOTIFY_COI_RESPONSE_REMINDER	122
NOTIFY_COI_REVIEW_RESPONSE_COMPLETED	123
NOTIFY_RB_MANAGEMENT_PLAN_OUTCOME	124
NOTIFY_RB_PLAN_SIGNOFF	125
NOTIFY_RB_PLAN_SIGNOFF_COMPLETE	126
NOTIFY_KSP_EDUCATION_VALIDATION_FAILED	127
NOTIFY_REVIEWER_PENDING_REMINDER	128
NOTIFY_UCSF_ACCOUNT_REQUEST	129
NOTIFY_UCSF_ACCOUNT_REQUEST_NO_DEPT	130
NOTIFY_FINANCE_ENROLLED	131
NOTIFY_STATUS_ENROLLMENT_CLOSED	132
NOTIFY_STATUS_PENDING_SPONSOR	133
NOTIFY_FINANCE_APPROVED_OBTAINED	134
NOTIFY_STUDY_PLAN_PUBLISHED	135
NOTIFY_FINANCE_NEW_INSTITUTION	136
NOTIFY_COST_CENTER_STATUS_OPEN	137
NOTIFY_BUNDLE_EAMIL_TEMPLATE	138
NOTIFY_TRAINING_ABOUT_EXPIRE_REMINDER	139
NOTIFY_PREREVIEW_ADMINISTRATIVE_ASSIGNMENT	140
NOTIFY_PROTOCOL_KPP_PROJECT_COI	143
NOTIFY_PROTOCOL_KPP_PROJECT_COI_REMINDER	144
NOTIFY_PROTOCOL_KPP_PROJECT_COI_DEADLINE	145
NOTIFY_SIGNOFF_ARC_ORIGINATING_PI	146
NOTIFY_SIGNOFF_ARC_ORIGINATING_PI_DENIAL	147
NOTIFY_SIGNOFF_ARC_ORIGINATING_PI_REMINDER	148
NOTIFY_SIGNOFF_ARC_ORIGINATING_PI_EXCEEDED	149
NOTIFY_SIGNOFF_ARC_TRANSFER_COORDINATOR	150
NOTIFY_SIGNOFF_ARC_TRANSFER_COORDINATOR_DENIAL	151
NOTIFY_SIGNOFF_ARC_TRANSFER_COORDINATOR_REMINDER	152
NOTIFY_SIGNOFF_ARC_TRANSFER_COORDINATOR_EXCEEDED	153
NOTIFY_SIGNOFF_ARC_VETERNARIAN	154
NOTIFY_SIGNOFF_ARC_VETERNARIAN_DENIAL	155
NOTIFY_SIGNOFF_ARC_VETERNARIAN_REMINDER	156
NOTIFY_SIGNOFF_ARC_VETERNARIAN_EXCEEDED	157
NOTIFY_STAR_PATIENT_ENCOUNTER	2000
NOTIFY_PROJECT_WAITING_SUBMISSION	10000
NOTIFY_PROJECT_SIGNOFF	10001
NOTIFY_PROJECT_SIGNOFF_REMOVED	10002
NOTIFY_PROJECT_WAITING_SUBMISSION_REMINDER	10003
NOTIFY_PROJECT_PI_SIGNOFF	10004
NOTIFY_PROJECT_SIGNOFF_DENIAL	10005
NOTIFY_PROJECT_REVIEWER_REMINDER	10006
NOTIFY_PROJECT_REVIEWER_EXCEEDED	10007
NOTIFY_PROJECT_ADDITIONAL_SUBMISSION_REVIEWER	10008
NOTIFY_PROJECT_AOR_ASSIGNMENT	10009
NOTIFY_PROJECT_AOR_UNASSIGNMENT	10010
NOTIFY_PROJECT_AOR_OUTCOME_APPROVED_PA_NOTICE	10011
NOTIFY_PROJECT_AOR_OUTCOME_REVISION_REQUESTED_PA_NOTICE	10012
NOTIFY_PROJECT_AOR_OUTCOME_DENIED_PA_NOTICE	10013
NOTIFY_PROJECT_STIPULATION_NOTICE	10014
NOTIFY_PROJECT_STIPULATION_NOTICE_COMPLETE	10015
NOTIFY_SUBMIT_TO_SPONSOR	10016
NOTIFY_FOA_CHANGE	10018
NOTIFY_PROJECT_INSTITUTIONAL_DUE_DATE	10019
NOTIFY_PROJECT_SPONSOR_DUE_DATE	10020
NOTIFY_PROJECT_BOARD_PROPOSAL_FORM_RECEIVED	10021
NOTIFY_PROJECT_BOARD_SUBMISSION_FORM_RECEIVED	10022
NOTIFY_PROJECT_BOARD_PEER_REVIEW_REQUEST	10023
NOTIFY_PROJECT_END_DUE_DATE	10024
NOTIFY_PROJECT_PERIOD_END_DUE_DATE	10025
NOTIFY_PROJECT_GRANT_CNTR_STATUS_CHANGE	10026

 

Workflow Event Types, System Workflow Entry Table:

System Constant Name	Numeric Value
EV_SUBJECT_PAST_DUE_TASKS	1
EV_SUBJECT_UPCOMMING_TASKS	2
EV_STUDY_CONTINUING_REVIEW_NOTICE	3
EV_STUDY_SUBMISSION	4
EV_RESPONSE_REQUIRED	5
EV_SIGNATURE_SIGNOFF	6
EV_REVIEWER_ASSIGNMENT	7
EV_PA_ASSIGNMENT	8
EV_PREREVIEW_DENIED	9
EV_RENEWEL_EXPIRATION_NOTICE	10
EV_KSP_IRB_TRAINING_VALIDATION_FAILED	11
EV_KSP_IRB_TRAINING_VALIDATION_COMPLETE	12
EV_WORKFLOW_PREREVIEW_ASSIGNMENT	13
EV_WORKFLOW_OUTCOME_LETTER_SIGNOFF	14
EV_WORKFLOW_SUBMISSION_REVIEW	15
EV_MEMBER_REVIEWER_ASSIGNMENT	16
EV_KSP_IACUC_TRAINING_VALIDATION_FAILED	17
EV_KSP_IACUC_TRAINING_VALIDATION_COMPLETE	18
EV_SIGNOFF_DENIED	19
EV_RECRUIT_FOLLOWUP	20
EV_SUBMISSION_ROUTING_SIGNOFF	21
EV_SUBMISSION_FORWARD	22
EV_STUDY_USER_BY_ROLE_REVIEW_ASSIGNMENT	23
EV_USER_REVIEW_ASSIGNMENT	24
EV_EQUIPMENT_INVENTORY	25
EV_EQUIPMENT_DISPOSITION	26
EV_FORM_SECTION_ASSIGNMENT	27
EV_KSP_VACCINATION_VALIDATION_FAILED	28
EV_WORKFLOW_OUTCOME_SIGNOFF_COMPLETE	29
EV_WORKFLOW_REVIEWERS_COMPLETE	30
EV_WORKFLOW_INTERNAL_ROUTING_COMPLETE	31
EV_ADDITIONAL_SIGNOFF	32
EV_WORKFLOW_MEETING_MINUTES_ROUTING	33
EV_ANNUAL_COI_RENEWAL_NOTICE	34
EV_ANNUAL_COI_LAPSE_NOTICE	35
EV_COI_NEW_HIRE	36
EV_ANNUAL_FAILURE_TO_SUBMIT	37
EV_COC_REVIEWER1	38
EV_COC_REVIEWER2	39
EV_COC_RESPOND_TO_REVIEW	40
EV_KSP_COI_INFO	41
EV_SYSTEM_USER_BY_ROLE_REVIEW_ASSIGNMENT	42
EV_COI_RESPOND_TO_REVIEW	43
EV_COI_REVIEWER_ASSIGNMENT	44
EV_WORKFLOW_MGMT_PLAN_SIGNOFF	45
EV_WORKFLOW_PLAN_SIGNOFF_COMPLETE	46
EV_STAR_PATIENT_VISIT	47
EV_SUBMISSION_RETRACTED	48
EV_COI_MATCH_PA_ASSIGNMENT	49
EV_COI_RESPOND_TO_REVIEW_COMPLETE	50
EV_KPP_COI_INFO	51
EV_ARC_ORIGINATING_PI_SIGNOFF	52
EV_ARC_VETERNARIAN_SIGNOFF	53
EV_ARC_TRANSFER_COORDINATOR_SIGNOFF	54

 

These constants are used in the Time Accountability notification to specify whether to send the notification to User (1), Manager (2), or both (3)

System Constant Name	Numeric Value
NOTIFY_TO_USER_ONLY	1
NOTIFY_TO_MANAGER_ONLY	2
NOTIFY_TO_BOTH_USER_AND_MANAGER	3

 

**ER (Entity Relationship) Diagrams

ER diagrams are also available for further assist in designing reports. Please contact your iMedRIS Project Manager or Customer Service Representative for these ER diagrams.

Filter Dictionary for Step 7

Here is a list that describes the possible filters that you might be able to use with your report. The filters available to you will depend on the columns you have chosen.

Filter Name	Parameter Name	Applicable On	Description
Date Selection	START_DATE, END_DATE	Date/time or Date	Allows the user to filter the data by a start date and an end date.
Select Department	SELECT_DEPARTMENT_ID	Varchar, Varchar2,  Integer	User will have the option Select a Department; this filter could be added to any published reports in any module.
Default Department	DEPARTMENT_ID, DEPARTMENT	Varchar, Varchar2,  Integer	Resulted data will be filtered by the User’s Default Department. It is advised that you publish your report in Dept/Site reports section.
Select Children Department	SELECT_CHILDREN_ DEPARTMENT_ID	Varchar, Varchar2,  Integer	Data will be filtered by the Child Department ID only.
Select Parent Department	SELECT_PARENT_ DEPARTMENT_ID	Integer	Data will be filtered by the Parent Department ID only.
Select Review Process	DETERMINE_REVIEW_ PROCESS	Integer	Filter the submission by the pre-review process (e.g., Full Board, Expedited...etc.)
Select Submission Type	SUBMISSION_TYPE	Integer	Filter submissions by their type. (e.g., Initial Submission, Continuing Review.)
Select Institution	INSTITUTION_NUMBER	Integer	User will be prompted to select an Institution
CRC's Names	CRC_ID	Integer	A drop down list of the CRC’s that users can select to filter the data. All are selected if this filter is ignored.
Default Review Board	SYSTEM_RB_ID	Integer	Filter the data by the review board that you are in. Example if you are in the report section of the IRB 1 Review Board then data will be filtered by IRB 1 ID.
Select Review Board	SELECTED_SYSTEM_RB_ID	Integer	Filter the data by a drop down list of the Review Boards in the system.
Select Review Board Committee	COMMITTEE_LIST_ID	Integer	Filter the data by committee from a drop down list.
Study Selection	SYSTEM_STUDY_ID, STUDY_ALIAS,STUDY_TITLE	Varchar, Varchar2, Clob, Nclob, Text, Ntext, Integer	Select a study by searching the database using multiple parameters. (IRB #, Title, PI...etc.)
Study Selection without All	SYSTEM_STUDY_ID, STUDY_ ALIAS,STUDY_TITLE	Varchar, Varchar2, Clob, Nclob, Text, Ntext, Integer	Same as "Study Selection" but the report runs for only ONE STUDY at a time. All studies cannot be selected.
Study Status Selection	STUDY_STATUS	Varchar, Varchar2	Filter the studies by study status.
Study Status Selection with All	STUDY_STATUS _ WITH_ALL	Varchar, Varchar2	Filter the studies by one study status at a time or all.
On Study Status	ON_STUDY_STATUS	Varchar, Varchar2	Filters subjects by the “on study status” (all is included also) they have been assigned.
Select User	SELECT_USER_ID	Integer	A “Find” filter will be available for users to search the database to pick a user.
Study Personnel Role	ACCESS_TYPE	Integer	Select from drop down list of the roles of the study personnel. (e.g., PI, Contact...etc.)
Round Number	ROUND_NUMBER	Integer	Filter the submissions by the number of times they have been back and forth between study management to the review board (called “rounds”).
USDA Class	USDA_CLASS	Varchar, Varchar2	Filter by drop down list of the USDA Classes.
Submission Form Name	SUBMISSION_FORM_NAME	Varchar, Varchar2	Filter by the submission form name. (Submission form name is equivalent to submission type.)
Select Race	RACE	Varchar, Varchar2	Filter by Subject Race.
Select Enrolling Physician	ENROLLING_PHYSICIAN	Integer	A drop down list of the Enrolling Physician that you can use to filter the data. All physicians will be included if this filter is ignored.
Select Department without All	SELECT_DEPARTMENT_ID_ WITHOUT_ALL	Integer	Allows a user to Select a Department without the ALL option.
Select System Role	SELECT_SYSTEM_ROLE	Integer	Select the user system role. (e.g., Administrator.)
Logged User ID	LOGGED_USER_ID	Integer	Filter the data by the Logged User ID.
Select CRO	CRO	Integer	Filter by CRO NAME.
Default Committee	RB_COMMITTEE_ID	Integer	Filter the data by default committee.
Select Committee Role	SELECT_COMMITTEE _ROLE	Integer	Allows a user to select a Committee Role from drop down list. (e.g., Coordinator, Board Member, Chairperson.)
Select Treatment Arm	ARM_ID	Integer	Filter procedures and subject task by Treatment Arm.
Select Trade Drug Name	TRADE_DRUG_NAME_ID	Integer	This filter consists of a drop down list of the trade name of the drug.
Select Investigational Drug Name	INVESTIGATIONAL_ DRUG_NAME_ID	Integer	This filter consists of a drop down list of the investigational name of the drug.
Select Generic Drug Name	GENERIC_DRUG_ NAME_ID	Integer	This filter consists of a drop down list of the generic name of the drug.
Select Device	DEVICE_ID	Integer	Select a Device from the drop down list of the devices in the system
Select Internal Funding Source	INTERNAL_FUNDING_ SOURCE_ID	Integer	Filter study and study financial data by internal funding source.
Select External Funding Source	EXTERNAL_FUNDING_ SOURCE_ID	Integer	Filter study and study financial data by external funding source.
Select Subtask State	SUBTASK_STATE	Varchar, Varchar2	Filter by procedure task state (completed, cancelled, etc.).
Select Subtask Classification	SUBTASK_CLASSIFICATION	Varchar, Varchar2	Filter by procedure task classification (clinical, administrative, etc.).
Select Subtask	SUBTASK	Varchar, Varchar2	Select procedure or subtask name.
Select Sponsor	SPONSOR	Varchar, Varchar2	Select Sponsoring Agency.
Select Risk Assigned	RISK	Varchar, Varchar2	Select Risk Assigned type.
Select Reason	REASON	Varchar, Varchar2	Select the Screening Reason.
Study Account	ACC_NUM	Varchar, Varchar2	Input field for study account number.
Research Type	RESEARCH_TYPE	Varchar, Varchar2	Drop down list of study research types.
RB Number	IRB_NUM	Varchar, Varchar2	Input field for RB # for (GRANTS, IRB, IACUC, IBC, CONTRACTS).
Multiple Select Race (Max 4K Char)	FILTER_BY_ PATIENT_RACE	Varchar, Varchar2	Select multiple subject races
Multiple Select Institution (Max 4K Char)	FILTER_BY_ INSTITUTION	Varchar, Varchar2	Limited to 4000 characters, multiple select for institutions.
Multiple Select Off study Reason (Max 4K Char)	FILTER_BY_OFF_ STUDY_REASON	Varchar, Varchar2	Limited to 4000 characters, multiple select for off study reason.
Multiple Select Study Status (Max 4K Char)	FILTER_BY_ STUDY_STATUS	Varchar, Varchar2	Limited to 4000 characters, multiple select for study status.
Multiple Select On Study Status (Max 4K Char)	FILTER_BY_ON_ STUDY_STATUS	Varchar, Varchar2	Limited to 4000 characters, multiple select for subject on study status.