Creating custom reports

The Report Designer enables you to specify 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.

Custom reports must be placed in the MACRO\Reports folder in order to work correctly.

If you have access to this folder, we recommend that you set it as the default save location. Otherwise you will be prompted to manually copy the report files to this location after saving them in your chosen location.

To set the default location, add the following settings to the MACROUserSettings40.config file:

Ensure that these locations are writable.

From the Report Management module, select Tasks > Design a Report. The Report Wizard is displayed.
Enter the SQL for the query, and click Apply.
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 layout screen allows you to select layout and page orientation.
Click Next. The style screen allows you to select a predefined font style for the report.
Click Next. Enter 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 help on report design.
To keep the default design, select File -> Save from within the Report Designer menu to save your report.
If a default save location has been set, the report will be saved in the MACRO\Reports folder. The automatically assigned report name is displayed and although you cannot change this name you can copy it for future use. You will be prompted to create a homepage link to the report. If you wish to do so now, click Yes and the Homepage Item window is displayed with the details already entered. Change the Display Name to something meaningful and click Save. You must add the report link to a group before it will display on the homepage.

If a default save location has not been set, you will be prompted to copy the reportname.aspx file from its current location to the Reports folder within the application folder. Two additional files have been created (reportname.repx and reportname.dll) and must be copied to the Reports\BIN folder. You will have to set up the homepage link to this report manually.
Select File -> Exit to close the Report Designer window.

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.

Creating custom reports

Using the Report Designer