Generating a custom report

You can create just the report you want with CourseMill's custom reports. Generate reports based on predefined database views, custom database queries, or external scripts.

Custom reports can be downloaded or saved for quick retrieval or for automatic scheduling in the Report Locker.

To generate a custom 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.

To 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.

The report selection fields are enabled if you include in the select fields the corresponding column keys or 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.

Click Save.

To setup FTP Properties for the Report so that when the report is executed via the Batch Report Scheduled Task the report will be sent via FTP to the server specified.

The following properties must be configured for it to work:

FTP URL: The address of the FTP server. (e.g. 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 then you need to 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 will be used]
FTP User: The username required to access the FTP server; the user credentials must have write access.
FTP Password: The password required to access the FTp server.

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

To generate a report based on an external database script, the script must return XML records based on the following passed parameters and in the following XML format:

Parameters	Description
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	Suborgs
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>

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

To generate a report based on an external script:

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 to the script in the Script URL.
Enable the appropriate report selection options in the Allow Report Selection list.
Click Save.

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