Table of Contents
In onCourse, we have used a powerful, flexible reporting engine called JasperReports to create our reports. We and our customers can create report templates are used to generate print quality output PDF, CSV and XML that fits their needs.
There exist a number of tools that can be used to create custom reports which are outlined in the later sections of this document. Our tool of choice is called iReport, but it's still possible to create reports such as OpenOffice or MS Office but these solutions will not be able to generate reports that can be imported as a report within onCourse itself.
You'll find that a number of reports have already been included in onCourse. For the most part these will provide information about one thing, ie. from the Students window, you'll be able to print a list of students, or student details. Similarly from the Courses window, you can reports relating to courses.
However, sometimes onCourse users want to find out more information than is included in current reports. We also have a mechanism to create custom reports, and import theses reports that into onCourse, allowing new reports to be printed when required. Alternatively, you can contract ish to develop custom reports to meet your specifications.
The first thing to be aware of when attempting to print a report is that you must be in the right onCourse list view to print a report. For example, Class roll is a report available from the class list. You can not print this report from inside the class record, or from the general window. You must open the class window first.
From each list window, you can select File > Print to see the list of available reports.
All reports can be either printed to a PDF file which you save to your computer, or printed directly to a printer on your network. For exports to excel, XML or other formats, see the chapter Importing and Exporting.
Before you print a report, you will usually need to run a query on the data first. For example, if you wish to print the class rolls for all the classes starting this week, you will need to do an advanced query for classes starting from today, to the last day of the week. For more information on queries, see the chapter Getting Started.
In the print dialogue, you will be asked if you want to print all, selected or highlighted records. A record count will be given for each option. Selecting 'all' will print the report for all records in the database. Choosing 'selected' will print the results from the query you have run. Choosing highlighted will print the report for the one or more records you have selected in the list.
You also have the option to 'Use pre-printed paper' when printing. This will suppress the automatic onCourse header from the report which can be useful if you are printing a report like a Certificate, where you print onto letter head or other pre-printed paper.
Please note some reports are listed more than once as they may apply to multiple areas of the colleges operation
Budget Details Line by line details of class per page budgets
Budgets Summary reports of class budgets, grouped by subject
Budget Summary by Subject Provides a summary analysis at course subject level of actuals against budget
Class Invoice Record Report that summarises by Class a list of all students, payments made, fees owing and name of payer for the given enrolment
Banking ReportThis report shows payments in and is designed to take along to the bank when you deposit your cash and cheques.
Class Tutor List Report Scheduled tutor hours for each class, useful for pay claim verification
Debtors and Creditors Aged debtors and creditors
Invoice Report Invoices supplied to debtors
PaymentIn Line report of all payments in
Transaction Detail Detailed report displaying transactions made to and from each general ledger account over a specified period of time.
Transaction Summarya more general overview of transactions made over a specified period of time. This report is not based on cash positions, but invoices created and is used by most book keepers to provide their accountant with a record of assets, liabilities and income.
Reconcile For reconciliation with banking deposits
Statement Summary of all debits and credits for a contact
Class by Subject An overview of all the classes, grouped by subject, including enrolment numbers and enrolment targets
Class Funding This AVETMISS based report is useful for keeping track of the hours of funded delivery you have provided against each qualification
Sales ReportThis report provides a comparative analysis of sales figures from one College site to the next.
Sales By Course Location ReportList of the number and monetary value of enrolments categorised by Course and College Site.
Classes Report A summary list of classes, similar to the classes list window, showing dates and enrolment numbers
Enrolment Annual Comparison Report Report that provides the number of daily enrolments, enrolment count for date the report is run this year and compares it against the number of enrolments for same time last year.
Class Tutor List Report A useful report for payroll purposes, this shows, grouped by tutor the hours of scheduled delivery they have for each class
Courses A printed list of the courses window
All Class Details Detailed report of all information for a given class, including enrolment numbers, session details and course details and directions.
Student Special Needs Provides a report of any students that require assistance with completing or accessing a class or classes
Class By Site Summary of all classes against a given site or sites. Report includes summary enrolment information and Male Female breakdown.
Rooms Report List of all rooms and their related sites and seating capacity
Room Details Report A report that allows users to print the details of all sessions that are scheduled for a particular room, for a particular date range
Site Details The details of a particular site as it has been set up, including directions, room listings and capacity.
Site List A list of all the sites and their addresses
Class Tutor List List of all Tutors by Class, includes site information and start end times for sessions.
Tutor Details The contact details and scheduled sessions for a tutor
Tutors Report A list of tutor names and addresses
Class Sign Report that can be printed out and used as a sign / label on door of Training Room.
Mailing List Report Contact details of all people on a mailing list
Wait List ReportList of all currrent wait list entries received.
Class Details The information for each session of the class is listed on this report
Course Details This report provides all the detail of a course, including it's description and scheduled classes
Enrolment Confirmation Details about course content, time dates and locations for sessions of the class a student has enrolled in
Class Information Class information for the enrolling student
Invoice Report Invoice showing GST and non-GST fee components, plus balance outstanding.
Statement A list of all transactions made by a contact
Certificate Attendance Certificate to certify that a student has attended a non VET short course
Class Roll Session by session grid created for marking student attendance
Class Roll - Single Session A standard document that lists all the students in a class, tutors return this roll each session or week. Users need to print multiple copies of this roll if they require one for each session of a class.
Class Timetableallows tSingle sessionhe user to select multiple classes and print a date-range specified timetable of sessions to show the students when and where to turn up to class.
Class Contact List Contact details for students in a particular class
Student Contact List Contact details for all students across a range of class groups
Student Details A useful educational report showing an overview of all units a student has undertaken at the training organisation and their outcomes
Class Roll Contact Number A report that provides a list of all students, their contact phone number, can be printed out and used for roll call.
Assessment Outcome Report Provides a printable template for the Tutor to enter assessment outcomes for each unit of competency for students in a given class.
Qualifications A list of the NTIS qualifications available within onCourse
Certificate Statement of Attainment Report that generates a Statement of Attainment for partially completed qualifications.
Certificate Qualification Report that generates a Qualification Certificate for students that have completed all requirements for a given Qualification.
Traineeship Training Plan Provides an outline of the required units of competency within an overall Traineeship Training Plan for a given student.
Class Funding For keeping track of delivery hours for different funding groups
Class HoursA summary of the classroom, nominal and reportable hours for each course.
Class hours: A summary of the classroom, nominal and reportable hours for each class
onCourse includes a range of printed reports as part of the standard software. These examples list the reports available and their purpose. Additional reports can be quoted and built on request if you do not have the in-house IT skills to build or modify your own reports.
Use: To obtain a detailed print out of the budgeted and actual income and expense lines, one page per class.
Report Location: From the menu, select and choose . You can print all available records, listed records (based on a query you have run) or the highlighted records.
Budget Details
Use: To obtain a detailed or summary print out of the budgeted and actual
Report Location: From the Menu, Select and Choose . You can print all available records, listed records (based on a query you have run) or the highlighted records. You can suppress the class lines, to provide a summary for each subject only, or show each class.
In the example below, the classes are listed with the actual figures displayed. As detail has not been entered for the budget fields, the budget amounts are all listed as $0.
Budgets By Subject
Use: Enables Colleges to monitor budget against actuals at the summary level, includes details by subject on the number of enrolments, income generated, expenses and the resulting variances.
Report Location: From the window, select the specific classes you wish to run the budget analysis for. Select and then choose from the drop down menu of reports.
Budget Summary by Subject
Use: Invaluable as a method of cross referencing incoming payments for a given Class. the report lists the names of all enrolling students, Invoice No, fee charged, fee paid and any outstanding balances. As well as the name and contact number of the payee
Report Location: From the window, select the specific classes you wish to print the details for. Select and then choose from the drop down menu of reports.
Class Invoice Record
Use: To obtain a report of all cheque, cash and credit card payments processed since the last bank, to assign the money to the appropriate general ledger income accounts, and to generate a printed report, where necessary to provide to your bank with the cash and cheque deposits.
Report Location: From the Menu, select . In the window that opens, you can see a summary of the cheques, cash and credit card payments collected since the last processed bank. You can check the option, , for a hard copy, as displayed below.
Banking Report
Use: To obtain an overview of classes scheduled for each tutor. This report also provides the total hours the tutor is scheduled to teach for each class, which can be used for payroll purposes.
Report Location: From the window, find the classes you wish to include in the report, such as a particular date range. Select , and Choose Report
Class Tutor List Report
Use: To obtain an aged debtors and creditors report for all unbalanced invoices
Report Location: From the window, select and Choose .
A Debtors And Creditors Report
Use: To supply hard copy Tax Invoices to students and other debtors.
Report Location: From the window, find the invoices you wish to include in the report, such as a particular date range. Select , and Choose Report
Report Notes: When a student is enrolled, and provides an email address, a tax invoice
will be emailed to the payer, along with an enrolment confirmation, sent to the
student enrolled. Often this will be the same person.
Other invoices, not relating to enrolments, can be manually created and printed via the invoices window.
A Tax Invoice Report
Use: To obtain an overview of payments processed, declined or rejected, processed through either your office or over the website.
Report Location: From the window, find the payments you wish to include in the report, such as a particular date range. Select , and Choose Report
Report Notes: All payments made, or attempted, as part of the enrolment process will created a Payment In record. You can also manually create payments in from this window, against any outstanding invoice.
A 'Payment In' Report
Use: To provide a detailed report displaying transactions made to and from each general ledger account over a specified period of time.
Report Location: To access this report you must first be in the "Accounts" section of onCourse, you can access this through a number of ways. From the splash screen or from the top menu Financial > Accounts. Once in "Accounts" click on "Account", there's a subtle difference. Once in "Account" (under "Accounts") you may select (from the top menu) File > Print and print to PDF or paper. You can also set the date far back so that you may include more records that have occurred over time.
A Transaction Detail Report
Use: To provide a more general overview of transactions made over a specified period of time. This report will provide you with for example; Student Enrolments overall rather than each individual student
Report Location: To access this report you must first be in the "Accounts" section of onCourse, you can access this through a number of ways. From the splash screen or from the top menu Financial > Accounts. Once in "Accounts" click on "Account", there's a subtle difference. Once in "Account" (under "Accounts") you may select (from the top menu) File > Print and print to PDF or paper, but making sure you first change the drop down menu in the top right hand corner of the print window to "Print Transaction Summary". Be sure to adjust the date to suit your requirements. Remembering you can highlight multiple records when you open "account".
A Transaction Summary Report
Use: To reconcile payments in and out with banking deposits.
Report Location: From the menu, Select . Check the payments you wish to reconcile, and select . A summary of the reconciled and unreconciled payments are displayed in this window
Reconciliation Report
Use: To print a list of all transactions (both debits and credits) for a particular contact
Report Location: From the menu, Select and Choose . You can select to print the entire transaction history, or define a date range.
A Statement Report
Use:
Report is generated at the conclusion of any non VET short courses to verify that the student attended all of the required number of classes.
Report Location:
From the window, select the specific students you wish to print the certificates for. Select and then choose from the drop down menu of reports.
Certificate Attendance
Use: To obtain an overview of all classes status within a given time period, such as a term. Classes are grouped by their Category/Sub Category tag. Useful for holistic planning processes and work flow activities such as confirming course go ahead or managing cancellations.
Report Location: From the window, find the classes you wish to include in the report, such as a particular date range. Select , and Choose report .
Report Notes: M/F column: If a student's gender is not set within their details, they will not counted for either M or F; thus M + F does not necesarily equal to the total number of enrolments. Target column: This is the number of enrolments to reach the target for this class, ie: -3 means 3 more enrolments are required to meet the target, 3 would means that there are 3 enrolments more than the target.
A Class by Subject Report
Use: To keep track of delivery of funded program Student Contact Hours over a particular time period. This report shows a summary of each qualification and the hours delivered against each of the national funding types.
Report Location:From the window, find the classes you wish to include in the report, such as the current years English classes. Select , and choose report
Report Notes: This report may be of particular interest to colleges who deliver programs
with a range of funding sources and who are required to report the delivery of these hours.
By regularly running this report, the year to date delivery can be checked and be used to
assist with program planning. More information about how student contact hours are
calculated is available here
Class Funding Report
Use: A summary of the classroom, nominal and reportable hours for each class, for use in the administration of VET / AVETMISS courses.
Report Location: From the window, select the classes you wish to track hours for. Select and then choose from the drop down menu of reports.
Class Hours Report
Use: This report provides a comparative analysis of sales figures (shown as number of enrolments and monetary value) from one College site to the next.
Report Location: From the window, select the specific enrolments and classes you wish to run the analysis for. Select and then choose from the drop down menu of reports.
Sales Report
Use: Provides a summary list of the number of enrolments and monetary value of sales for each site, with further detail regarding number of enrolments and monetary value listed by Course.
Report Location: From the window, select the specific enrolments and classes you wish to run the analysis for. Select and then choose from the drop down menu of reports.
Sales by Course Location Report
Use: To obtain an overview of all classes status within a given time period, such as a term. Useful for holistic planning processes and work flow activities such as confirming course go ahead or managing cancellations. Classes are reported in the list order you have sorted the window data e.g. class name alphabetical order, start date order or code order.
Report Location: From the window, find the courses you wish to include in the report, such as a particular date range. Select , and choose report
Report Notes: The information available in this report is also available grouped by
subjects in the Classes by Subject Report.
Classes Report
Use: This report allows Colleges to compare and contrast current levels of enrolment against the same period in the previous year. The report also provides the variance between the two sets of numbers.
Report Location: From the window, selet the specific enrolments or classes you wish to run the analysis for. Select and then choose from the drop down menu of reports.
Enrolment Annual Comparison Report
Use: To create a print out of the list of courses
Report Location: From the window, find the courses you wish to include in the report, such as all courses tagged as ‘business'. Select , and choose report .
Courses Report
Use: Detailed report of all information for a given class, including delivery mode, minimum and maximum places, enrolment numbers, fees and discounts, session details, course description, tutor information and directions for getting to the campus.
Report Location: From the window, select the specific classes you wish to print the details for. Select and then choose from the drop down menu.
All Class Details
Use: Used as a reference for any special needs such as wheelchair access, dietary requirements and provides an indicator as to whether the student and or tutor has been contacted to confirm or follow up this information.
Report Location: From the window, select the specific classes you wish to print the details for. Select and then choose from the drop down menu of reports.
Student Special Needs
Use: This report provides a list of all current and future classes assigned to a given site. Information includes, course and class name and code, start and end date, fee charged, enrolments (against target) and statistics on Male Female breakdown.
Report Location: From the window, select the specific classes you wish to print the details for. Select and then choose from the drop down menu.
Class By Site Report
Use: List of all rooms and their related sites and seating capacity.
Report Location: From the window, select the specific rooms you wish to to print the details for. Select and then choose from the drop down menu of reports.
Rooms Report
Use: The purpose of the report is to allow users to print the details of all sessions that are scheduled for a particular room, for a particular date range.
Report Location: From the "room" window, highlight the room in question and go to File > Print choose "Room Details" from the drop down menu in the top right hand side. In the example below, there are some classes with and without tutors, to give you an idea of how it will come out for you.
Room Details Report
Use: List of all Tutors by Class, including site information and start end times for sessions.
Report Location: From the window, select the specific details you wish to print the details for. Select and then choose from the drop down menu of reports.
Class Tutor List Report
Use: List of all sites and their addresses
Report Location: From the window, select the specific records you wish to print the details for. Select and then choose from the drop down menu of reports.
Site List Report
Use: Detail of all each site, venue information, rooms and each room capacity for the site
Report Location: From the window, select the specific records you wish to print the details for. Select and then choose from the drop down menu of reports.
Site Details Report
Use: The contact details and scheduled sessions for a Tutor
Report Location: From the window select the Tutor(s) you wish to view the details for. Select , and choose the Report entitled
Tutor Details Report
Use: A list of tutor names and addresses
Report Location: From the window select the Tutors you wish to view the details for. Select , and choose the Report entitled
Tutors
Use: Report that can be printed off and used as a sign or label on door of Training Room
Report Location: From the window select the specified classes you wish to print details for. Select and then choose from the drop down menu.
Class Sign
Use: Contact details (first name, last name, email) of all people on a given mailing list.
Report Location: From the or window, select the contacts you wish to run the report against. Select , and choose the Report.
Mailing List Report
Use: List of all current wait list entries, including name of course, name of student, client, and site where course is held.
Report Location: From the window, select the specific entries you wish to print the details for. Select , and then choose from the drop down menu of reports.
Wait List Report
Use:To obtain full list of one or more classes scheduled sessions. This is a list view of the timetabled sessions. Useful information to provide to venue coordinators, tutors and students.
Report Location: From the window, find the classes you wish to include in the report, such as all current classes at a particular venue. Select , and Choose Report
Class Details Report
Use: To create a print out of the full course information including scheduled classes. Can be used to create print copies of all the web information to mail to students without web access.
Report Location: From the window, find the course(s) you wish to include in the report, such as all courses tagged as ‘sport and fitness'. Select , and Choose Report .
Course Details Report 1
Use: To provide students with details of their class once they have enrolled.
Report Location: From the window, select the enrolments you wish to print enrolment confirmations for, Choose and Select the Report .
Report Notes: During the enrolment process, if the student provides an email address, an email confirmation of enrolment can be selected. It is not necessary to provide hard copy enrolment confirmations for these students.
Enrolment Confirmation Report
Use: Class information that can be provided for the enrolling student including course details, information about the Tutor, directions on where to go and the individual session details.
Report Location: From the window, select the specific classes you wish to print the details for. Select and then choose from the drop down menu of reports.
Class Information Report
Use: To create a list of students enrolled and columns for each of the scheduled sessions, used to mark attendance. The report will automatically re-size to fit the number of students and session conditions. Hard copy rolls are often distributed to tutors before classes commence, or PDF copies emailed to tutors or coordinators.
Report Location: From the window, find the classes you wish to include in the report, such as all current class taught by a particular tutor. Select , and Choose Report
Report Notes: Given that tutors may pass rolls around the room for students to initial
their attendance, to comply with privacy legislation, there are no student contact details
on the class roll. If the tutor requires the student's contact details, a student
contact list can be printed.
Class Roll Report
Use: To be used for classes where the tutors need to return a student-signed copy of the class roll back to the college administration office after each week or session. Users need to print multiple copies of this roll if they require one for each session of a class.
Report location: Go to "class" and highlight a class then go to File > Print and select "Class Roll - Single Session"
Class Roll - Single Session Report
Use: The purpose of this report is to allow the user to select multiple classes (which may contain the same students e.g. those doing a Diploma may undertake 4 classes simultaneously) and print a date-range specified timetable of sessions to show the students when and where to turn up to class. Useful for both students attending classes or could also be printed for all classes for a tutor who teaches multiple classes.
Report location: To print this report go to "class" > highlight the class(es) in question and go to File > Print and select "Class Timetable".
Class Timetable Report
Use: To create a list of students enrolled and the phone numbers and email addresses for those students. Hard copy lists can be distributed to tutors before classes commence if you wish them to have this information. Tutors can also access this information via their web portal if you organisation has enabled it.
Report Location: From the "Class" window, find the classes you wish to include in the report, such as all current class taught by a particular tutor. Select File, Print and Choose Report"'Class Contact List"
Report Notes: Given that tutors may pass rolls around the room for students to initial
their attendance, to comply with privacy legislation, there are no student contact details
on the class roll. If the tutor requires the student's contact details, a student
contact list can be printed.
Class Contact List Report
Use: To export the contact details provided for students enrolled in a class.
Report Location: From the window, find the classes you wish to include in the report, such as all current class taught by a particular tutor. Select , and Choose Report .
Report Notes: This report may be used to accompany the Class Roll
Student Contact List Report
Use: A useful educational report showing an overview of all units a student has undertaken at the training organisation and their outcomes.
Report Location: From the window, select the classes and students you wish to include in the report. Select and choose from the drop down menu of reports.
Student Details Report
Use: Report that provides a list of all students, their contact phone number, can be printed out and used for a roll call. Can also be used to contact students who are not in attendance at their enrolled session or class.
Report Location: From the window, select the specific classes you wish to print the details for. Select and and then choose from the drop down menu of reports.
Student Details Report
Use: For use by the College Tutor to mark the assessment outcomes for each student studying a VET class. As well as enabling the Tutor to sign and date the entries prior to sending back to College Administrators for entering onto onCourse.
Report Location: From the window, select the specific classes you wish to print the required assessment outcomes for. Select , and choose the Report from the drop down menu.
Assessment Outcome Report
Use: A list of the all the data downloaded from NTIS which you can use to start building courses.
Report Location: From the window, select the qualifications you wish to run the report against. Select , and choose the Report.
Qualifications Report
Use: onCourse includes AQF recommended templates for full Qualification Certificates, Statements of Attainment and transcripts. Certificates can only be generated from units that are recorded as part of onCourse enrolments. If you wish to include Credit Transfer units, you will need to add these outcomes to the students record before you create the certificate. For detailed information regarding VET Certificates, refer to the RTO Guide Chapter of the User Manual.
Report Location: From the window, select the student(s) you wish to run certificates for. Select , and choose the from the drop down menu of reports.
Report Notes: The resulting report (Statement of Attainment or Qualification) is dependent on how you have set up the Certificate record.
Statement of Attainment Page One
Statement of Attainment Page Two
Use: onCourse includes AQF recommended templates for full Qualification Certificates, Statements of Attainment and transcripts. Certificates can only be generated from units that are recorded as part of onCourse enrolments. If you wish to include Credit Transfer units, you will need to add these outcomes to the students record before you create the certificate. For detailed information regarding VET Certificates, refer to the RTO Guide Chapter of the User Manual.
Report Location: From the window, select the student(s) you wish to run certificates for. Select , and choose the from the drop down menu of reports.
Report Notes: The resulting report (Statement of Attainment or Qualification) is dependent on how you have set up the Certificate record.
Qualification Page One
Qualification Page Two
Use: A list of all Training Packages available within onCourse.
Report Location: This report is accessible via the menu,
Training Packages Report
Use: This report provides summary information on the required units of competency that need to be completed for a given student. As well as provision of signatures and sign off on the Training Plan by the student's employer, the trainee themselves and the Registered Training Provider (RTO)
Report Location: From the window, select the specific students you wish to run the training plan for. Select and then choose from the drop down menu of reports.
Traineeship Training Plan Page One
Traineeship Training Plan Page Two
Custom reports can be developed for onCourse by any user, or by ish on request, and then imported into onCourse via your client. Most custom reports will be quoted for development as this work is not part of the onCourse user support plan.
If you have determined that there are no reports that currently exist inside of onCourse that meet your requirements, your next step is to mock up a report that you can provide to ish to show how and what you would like to see in a new report.
If there is a similar report, describe how you want it changed e.g. what information needs to be added, where it needs to be added ect. Write this on a copy of the report and scan and send to ish
For a new report, mock up a sample (hand drawn or typed up) to provide to ish. Describe how the report works, where the information comes from, what data is included or excluded. We will then discuss with you how we can best achieve this report, and any conditions that may apply to generating the report data in the format you request. A final mock-up sample will be provided by ish for your approval.
For most custom reports a quote will be provided. The quote needs to be approved before development can commence. Reports which are product suggestions only (not requiring a quote) will be scheduled for development at a time determined by ish.
We assign to a developer to build and test the new report. This process can take between one week and a month, depending on the complexity of the report.
Some reports require changes to be made to the underlying onCourse product code. Where this is required, we will advise you in advance as this means the report will not be available to you until you have upgraded to the product version where the code changes were made.
Once we have conducted our internal testing, the report file is sent to your onCourse support person to import into your copy of onCourse and so you can conduct your own testing and approval. Further modifications can be requested as needed.
onCourse uses Jasper, an open source tool to create reports. You can add your
own custom reports to onCourse at any time by creating them with iReport. You can learn to
Export to XML in Chapter 21, Importing and exporting from onCourse. You can learn to connect
onCourse to OpenOffice in Chapter 20, Reporting to perform functions like mail
merge.
Jasper report is a specially formatted text file. For ease of use the file can be edited with a visual editor called iReport. The report file format is called XML, this is a snippet from an example report file:
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="ClassTutorList" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="782" leftMargin="30" rightMargin="30" topMargin="20" bottomMargin="20">
<property name="name" value="Class tutor list"/>
<property name="entity" value="CourseClassTutor"/>
<property name="isSubreport" value="false"/>
<property name="isVisible" value="true"/>
<property name="versionNumber" value="8"/>
<property name="keyCode" value="ish.onCourse.classTutorListReport"/>
<property name="ish.oncourse.title" value="Class Tutor"/>
<property name="ish.oncourse.filePrefix" value="classTutor"/>
<property name="ish.oncourse.pdfFileName" value="ClassTutor.pdf"/>
<property name="sortOn" value="tutor.contact.lastName"/>
<property name="ireport.scriptlethandling" value="0"/>
<property name="ireport.encoding" value="UTF-8"/>
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<import value="net.sf.jasperreports.engine.*"/>
<import value="java.util.*"/>
<import value="net.sf.jasperreports.engine.data.*"/>
<style name="Report_Name" isDefault="true" fontName="Arial" fontSize="18" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<parameter name="ReportName" class="java.lang.String" isForPrompting="false"/>
<field name="datasource.title" class="net.sf.jasperreports.engine.JRDataSource"/>
<field name="subreport.ish.onCourse.titleSubReport" class="net.sf.jasperreports.engine.JasperReport"/>
<field name="tutor.contact.lastName" class="java.lang.String"/>
<field name="tutor.id" class="java.lang.Long"/>
<field name="tutor.contact.firstName" class="java.lang.String"/>
<group name="tutorGroup">
<groupExpression><![CDATA[$F{tutor.id}]\]></groupExpression>
<groupHeader>
<band height="30" splitType="Stretch">
<textField isBlankWhenNull="true">
<reportElement key="textField-13" style="Column_Field" x="1" y="9" width="776" height="14"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.0" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement>
<font isBold="true" pdfFontName="Helvetica-Bold"/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[(($F{tutor.contact.lastName}==null)?"":($F{tutor.contact.lastName} +", "))+
(($F{tutor.contact.firstName}==null)?"":($F{tutor.contact.firstName}))]\]></textFieldExpression>
</textField>
...
Tip
Simple changes to the XML can be made directly in the onCourse report window. For more complex changes you will want to use iReport.
Each report has to be identified in the system, therefore it is given an unique parameter called 'keyCode'. To avoid confusion all reports created by ish group start with 'ish.' prefix. We strongly recommend that for any reports you customise, you use a different prefix, like a name of the college. the keyCode can be edited in iReport using by choosing from the menu Edit->Custom properties
Other parameters which are compulsory are:
versionNumber - an integer number, has to be increased every time a report is upgraded
entity - identifies which is the starting point for the report, ie. report with value 'Certificate' will be available in print menu for list of certificates.
isVisible - can only take value of 'true' or 'false', indicates whether the report is visible in the print dialog
isSubreport - some reports are just injected inside others, this allows to specify this fact
ish.oncourse.title, ish.oncourse.filePrefix and ish.onCourse.pdfFileName are currently not used, but should be set to prevent future problems.
This section cannot be summarised in few words, there are many online resources which
will help you learn using iReport. A good starting point is http://jasperforge.org/plugins/project/project_home.php?projectname=ireport
There is one important thing to mention. onCourse utilises javaBean data source. This has its downsides, the report cannot be tested without importing it to onCourse. The database schema cannot be imported or read from iReport. But this is essential to assure security of the database and the data and prevent unauthorised access.
For effective report writing a developer needs onCourse database schema. This document is available free of charge for all onCourse customers with support plan.
The basic workflow for a report developer is:
Create the report file
Edit the report using iReport
Compile it using iReport
Import the report to onCourse
Test it
Repeat steps 2-5 until the report works as intended.
Future releases of onCourse will allow starting onCourse in a report developer mode, which will allow simple text editing of the report file without any need to exit onCourse. This feature is going to require installation of current JDK on the computer used for report compilation.
We discourage starting with iReport from creating brand new reports. It is much easier to start with tweaking the existing reports. (Remember though, that if you edit an existing report or template you need to give it a new keyCode property, otherwise the next time onCourse server is restarted it is going to be lost in the system).
When using iReport, you don't access the data directly. Instead you use the data model provided by the onCourse engine: this wraps up the database in a way that means you don't need to write SQL and you don't need to worry about which database you are using the backend. Derby, MySQL and MS-SQL will all work in exactly the same way. Accessing the data model is easy to do. First, download a tool called "Cayenne Modeler": just grab the most recent stable build from that download page for your platform. You don't need to worry about the Cayenne libraries themselves, just run the modeler application. Next, you need a copy of the model itself for your version of onCourse. You can get this most easily by downloading the Linux onCourse Server installation (regardless of platform you are running, just grab that version). Then unzip and untar the onCourseServer.tar.gz file. Inside will be another file with a .jar extension. That is just another zip, so go ahead and unzip it (you may find it easier to rename .jar into .zip to fool your zip tool that it really is a zip file). Finally, amongst thousands of files which will be unzipped, use the Cayenne Modeler you installed earlier to open a file "cayenne/AngelMap.map.xml".
What you now have open is the data model of onCourse itself. This ObjEntities represented by the icon which looks like "C" are the objects, attributes and relationships which the reports can access. As you look through some of the bundled reports that will become clearer.
Navigate to File > Preferences > Reports. From here you may click the + sign on the top right hand side and select "folder" or "single report". If you have more than one report to import, you can select "folder" is to import all the reports in a folder reports while the "single report" is to import just the one report.
You can then be to select your report (.jrxml) from the location where it is saved on your computer. Select “open” button after highlighting the file (or folder) you want to import.
You will then either get a message advising you that the import has been successful, or unsuccessful. If the import was unsuccessful, the most common reason is that the version number was not updated or the keycode was not made unique. onCourse will only recognise the report as a new report if it has a unique keycode, and will only update an exisiting report if the version number is higher than the version already in the product. You can check the values for these entries in the reports window inside onCourse.
Selecting a custom report file for import into onCourse
There are many reasons why you might want to access data in onCourse through a tool such as SQuirrel SQL. You might need to generate a custom report or export the result of a complex query to Excel and it's important to have full access to your data for that reason.
Before you begin, you will need to have SQuirrel SQL installed on your machine which can be obtained from the squirrelsql.org website. SQuirreL SQL is an open-source Java SQL Client program for any JDBC compliant database.
Please note that support for SQL tools such as SQuirrel is not included as part of the standard onCourse support packages. This is self-service option available for people with strong IT skills and experience in working with relational databases to access the data within their onCourse database for their own purposes.
If you are not familiar with these tools, we can provide these services for you as a custom quote.
If you are using the embedded Derby database, you will need to make sure you have the derbyclient.jar plugin for SQuirrel to connect it to onCourse. You can download the file here. Download the binary for the most recent version number. Once you have downloaded, add the jar file to the install folder of SQuirril. When you next run SQuirrel you can add the driver, which is called Derby Client. There may be other similarly named derby drivers that aren't available, but this will not affect your ability to access onCourse.
When creating the driver you need to ensure you have added to the derbyclient.jar as an extra class path, and defined the class name as: org.apache.derby.jdbc.ClientDriver.
You also must specify the URL/URI. You need to enter jdbc:derby//192.168.1.1/onCourse.iocdata/onCourse with the IP address of your onCourse server in place of the example IP given. If you are on a windows machine, you will need to add the file path name in addition, between the server IP and onCourse.iocdata path. For example, jdbc:derby://localhost/c:/Program Files/onCourse/onCourse.iocdata/onCourse
Save the driver and if you have set it correctly, the driver will have a blue tick.
Once you have installed the Derby plugin and have SQuirrel up and running, you will need to create an alias to connect SQuirrel to onCourse. Name your connection a name of your choice, and select the Derby Client driver. Ensure the URL is correct and enter the username and password.
The username is 'report' and the password is '948ty'
Setting the alias and connection details in SQuirrel
If you are new to SQL a good place to start is the w3schools SQL tutorial. Once you have some familiarity with the structure of the onCourse database and the basics of SQL commands, you can then create queries requesting the information of your choice from the database.
The most important part of constructing a query is understanding how the tables in onCourse join to each other so you can query across tables. You can access this information by following the instructions above about using the data model. You can also browse the tables, columns and data in onCourse through SQuirrel by navigating to the objects > onCourse > table. For each table you can browse the columns (field names) and the content (data from the database you have connected to).
Most tables are joined via the ID field to the previous table. For example, in the contact table, you will see the ID column. In the student table you will see the column ID for the student record ID, and also the column CONTACTID which joins the student record to the contact record. If you want to return results about the names of students enrolled in classes you need to join the contact record (where the name of the person is) to the student record (who has enrolled in something) to the enrolment record (the join between the student and the class) and the class record (where the student enrolled) to the course record (where the name of the class is).
For example, to return a class list for students with active enrolments in Woodwork WDWK-0010 the query would be:
select c.firstname, c.lastname from enrolment as e join student as s on e.studentid = s.id join contact as c on s.contactid = c.id join courseclass as cc on e.courseclass_id = cc.id join course on cc.courseid = course.id where e.status = 3 and course.name like 'WDWK' and cc.code like '0010'
Adding additional fields to the select list would increase the detail returned, like the student's address, or their class start date. Adding additional options to the where statement would change what parameters you were searching for.
You can right click on the list returned to data to export it into excel or another format for your own requirements.
Exporting results of an SQL query to excel