Creating custom reports

To log in to the Report Management module, you need the 'Access Reports Module' permission. You must also have been assigned access to All Studies and All Sites.

You can add custom reports to the home page by specifying an SQL query to be run on the MACRO v4 database. You can choose which fields are displayed in the report and you can edit the report layout.

To write custom reports you must be familiar with SQL and with the MACRO database structure.

In the current version of MACRO v4, custom reports cannot be edited after they have been created. To make changes to a custom report, e.g. to change the SQL, it is necessary to delete the previous report and create a new one.

From the Report Management module, select Tasks > Design a Report. The Report Wizard is displayed.
Enter the SQL for the query, and click Apply. This runs the query, and you will be asked to provide temporary values for the @userid and @rolecode parameters. Use rde and FullUser.
The recordset retrieved is shown in the lower half of the window. Click Next.
Select the columns that you want to appear in the report. To select all columns, click the >> button.
Click Next. The next screen allows you to group the results. Select a column and click > to create a grouping under that column. For example, the results of this report could be grouped by study and site.
Click Next. The Summary Options screen allows you to show summary values. For example, you can choose to show the Min and Max number of eForms.
In the next two screens select a report layout and style.
Specify a title for your report and click Finish.
You will then see the Report Designer, where you can make further changes to your report layout. Please see the DevExpress Report Designer documentation for further information on report design.
To keep the default design, just select File -> Save as… from the Report Designer menu to save your report. Save it in the Reports folder within the MACRO 4.0 application folder.
You will then see a message outlining the next steps required. The system assumes that your report was saved to a Custom_Reports folder and so asks you to copy it to the Reports folder. However if you saved it to the Reports folder, as suggested in the previous step, then this is already done. Two extra files will have been created for your report with extensions .REPX and .DLL. These must be copied to the Reports\BIN folder.
Select File -> Exit to close the Report Designer window.

Designing a report

The following example generates a report showing how many eForm instances there are for each subject in the database. Each subject is identified by Study, Subject ID, Site and Label.

SELECT CLINICALTRIALNAME as Study, efi.PERSONID as Subject, efi.TRIALSITE as Site, LOCALIDENTIFIER1 as Label,

COUNT(*) as EForms from CRFPAGEINSTANCE efi, CLINICALTRIAL c, TRIALSUBJECT ts, USERROLELOOKUP

WHERE efi.CLINICALTRIALID = c.CLINICALTRIALID

AND efi.CLINICALTRIALID = ts.CLINICALTRIALID

AND efi.PERSONID = ts.PERSONID

AND efi.TRIALSITE = ts.TRIALSITE

AND USERROLELOOKUP.USERNAME = @userid

AND USERROLELOOKUP.ROLECODE = @rolecode

AND USERROLELOOKUP.STUDYID = efi.CLINICALTRIALID

AND USERROLELOOKUP.SITECODE = efi.TRIALSITE

GROUP BY CLINICALTRIALNAME, efi.PERSONID, efi.TRIALSITE, LOCALIDENTIFIER1

ORDER BY CLINICALTRIALNAME, efi.PERSONID, efi.TRIALSITE, LOCALIDENTIFIER1

Note the inclusion of fields from the USERROLELOOKUP table. The parameters @userid and @rolecode (current user's username and role) are automatically included when calling your SQL from the home page. This ensures that users will only see the data they have permission to access. Omitting the extra USERROLELOOKUP comparisons will give all users access to all the information in the database.

Oracle databases

If you use an Oracle database, you must also include the database name when referencing a table in a query. For example, the first part of the above query should read:

SELECT CLINICALTRIALNAME as Study, efi.PERSONID as Subject, efi.TRIALSITE as Site, LOCALIDENTIFIER1 as Label,

COUNT(*) as EForms from DatabaseName.CRFPAGEINSTANCE efi, DatabaseName.CLINICALTRIAL c, DatabaseName.TRIALSUBJECT ts, DatabaseName.USERROLELOOKUP

WHERE efi.CLINICALTRIALID = c.CLINICALTRIALID

Creating custom reports

Designing a report

Oracle databases

Linking to the report from the home page

Related Topics