Generating a custom report

Custom reports let you create just the report you want. Generate reports based on predefined database views, custom database queries, or external scripts. Custom reports can be downloaded, or saved to the Report Locker for quick retrieval or automatic scheduling.

Generate a report based on a predefined database view

Open the Reports side-tab and select Custom.
Click Add Custom Report. The Custom Report window opens.
Select the organization if you are a global administrator.
Select Database View in the Type field.
Select the unassigned database view from the View Name list.
Specify a description of the report in the Description field.
Click Save.

The results are displayed in the window. Click a column heading to re-sort the table.

Generate a report based on a custom database query

Open the Reports side-tab and select Custom.
Click Add Custom Report. The Custom Report window opens.
Select the organization if you are a global administrator.
Select Database Query in the Type field.
Specify a description of the report in the Description field.
Enable the appropriate report selection options in the Allow Report Selection list.
Click Save.

Note: The report selection fields are enabled if you include the corresponding column keys in the select fields, or if they are enabled as part of the conditional WHERE clause of the query. The Status option is enabled if you include either StudentCourse.Status or Sco.Lession_Status. The Dates option is enabled if you include any of the following: Student.LastLogin, StudentCourse.LastAccess, StudentCourse.CompletionDate, CourseSession.StartDate, or CourseSession.EndDate.

Set up FTP properties for a report

Configure FTP properties for a report so that when the report is executed by the Batch Report scheduled task, it is sent by FTP to the server you specify. See Managing scheduled tasks. The following properties must be configured for it to work:

FTP URL – The address of the FTP server (for example, ftp://company.server.com).
FTP Port – The port number for your FTP server. CourseMill is configured to work with port 21. If a different port is required, contact Support so that a new rule can be added to the firewall to allow communication on the specified port. If left blank, port 21 is used.
FTP User – The user name required to access the FTP server. The user credentials must have write access.
FTP Password – The password required to access the FTP server.

Generate a report based on an external database script

To generate a report based on an external database script, the script must return XML records based on the parameters passed to it and in the XML format shown below.

Parameters passed to the script

orgID – Organization ID.
currID – ID of the curriculum (configured in CourseMill).
courseID – ID of the course (configured in CourseMill).
sessionID – ID of the session (configured in CourseMill).
scoID – ID of the SCO (configured in CourseMill).
status – lesson_status or status (configured in CourseMill).
startDate – Beginning day of the report.
endDate – Ending day of the report.
SubOrg0-n – Sub-orgs.
start – Starting record, if pagination is available.

Response in XML format

<reportlist>
   <totalRecs>1</totalRecs>
   <numRecs>1</numRecs>
   <hdr0>StudentID<hdr0/>
   <hdr1>StudentName<hdr1/>
   <hdr2>LastLogin<hdr2/>
   <report>
     <col0>TestStudent</col0>
     <col1>Lastname, FirstName</col1>
     <col2></col2>
   <report>
 </reportlist>

Note: CourseMill provides a sample callable script, SampleCustomReport.jsp, located in the root of your CourseMill installation. See the contents of this file for details.

Add the report

Open the Reports side-tab and select Custom.
Click Add Custom Report. The Custom Report window opens.
Select the organization if you are a global administrator.
Select Database Script in the Type field.
Specify a description of the report in the Description field.
Specify the address of the script in the Script URL field.
Enable the appropriate report selection options in the Allow Report Selection list.
Click Save.