Accessing Financial Information from onCourse

When running any kind of query against onCourse to extract financial information, keep in mind that enrolment information is attached to the student and all financial information (such as invoices / payments / credit notes) are attached to the debtor within the system. Therefore there is no direct data relationship between the student and the debtor.

There are multiple methods you can use to extract / examine financial information within onCourse;

Automated reporting from scripts

An automated weekly or monthly Trial Balance report from onCourse is available by enabling the script 'send weekly finance summary report'.

By default, this script is set up to run every Monday morning. It prints to PDF and emails the Trial Balance report for the previous 7 days to the default system administrator email address. You can make the following changes in the script to meet your requirements:

Change the email address receiving the report - The default system administrator email address is the one you have set in the Messaging section of the Preferences window. If this is not the email address you want to use, change the reference in the script that says:

smtp {
    from preference.email.from
    to preference.email.admin
}F

to say:

smtp {
    from preference.email.from
    to "[email protected]"
}

Where "[email protected]" is the email address of the person who should receive the report, being sure to include the speech marks.

Change from weekly to monthly automated reports -
To make this change you need to make some changes to the code in the script, and also the CRON. The CRON is the scheduler that defines how often the script runs. By default the CRON is 3am every Monday morning:

0 0 3 ? * MON

Change it to 3am the 1st of every month:

0 0 3 1 1/1 ? *

And then inside the body of the script, you need to change which actions are commented out. Lines of code which are comments and not run by the software begin with //. Change the script from:

Groovy

    //If you want to change date period use one of the sections below:

    //set period in number of days (e.g. for the last 7 days)
    def endDate = Calendar.getInstance().getTime()
    endDate.set(hourOfDay: 0, minute: 0, second: 0)
    def startDate = endDate - 7


//    set period in calendar months (e.g. for the last month)

//    def endDate = Calendar.getInstance().getTime()
//    endDate.set(dayOfMonth: 1,hourOfDay: 0, minute: 0, second: 0)
//    println endDate
//    def startDate = DateUtils.addMonths(endDate, -1)

To:

Groovy

    //If you want to change date period use one of the sections below:

    //set period in number of days (e.g. for the last 7 days)
    //def endDate = Calendar.getInstance().getTime()
    //endDate.set(hourOfDay: 0, minute: 0, second: 0)
    //def startDate = endDate - 7


//    set period in calendar months (e.g. for the last month)

   def endDate = Calendar.getInstance().getTime()
   endDate.set(dayOfMonth: 1,hourOfDay: 0, minute: 0, second: 0)
    println endDate
    def startDate = DateUtils.addMonths(endDate, -1)

Locating data using related records

In all onCourse windows, you can track through the relationships between the data using the 'Find related' function in the cogwheel in the top right-hand corner. For example, if you wanted to find all the invoices related to the payments in taken on a given day, you can select all the payment in records for the day, click the 'Find related' icon and select invoices. This will open all the related invoices in a new window.

In the new Invoices window, you can run additional queries and or print reports. This window has opened in a special state that only ever shows the maximum set of results as being the list then generated from your original find related search, so all your searches, filters and reports will only relate to this subset of data until you close the Invoice list window.

Printed Financial Reports

onCourse comes with a number of different Financial Reports and each of these are looking at one particular aspect or area of the database such as Invoices. You may also wish to analyse the financial information that is being generated. You can do this by comparing information from one report to the next.

For example, a data comparison that will allow you to verify the accuracy of the financial information is the deposited funds account, balance = Payment in - Payment out for the same time period.

New Custom Reports can be developed upon request, just bear in mind the rules of how the data is structured within onCourse when considering what kind of information you want to appear on the Report.

Copying info within list view windows to Excel

Copy and paste list view records into excel. Run your desired query within a given window, highlight the records you want in the list window and copy. Open excel to paste records.

The benefit of this approach is that it allows you to copy information from a number of different windows on to the same excel worksheet in order to better compare and verify the data.

CSV Exports

onCourse allows you to export data directly from onCourse. As is the case with the printed reports, these exports are essentially flat tables of data, which means that you are only looking at one specific area such as invoices.

For more information regarding exporting financial data from onCourse, refer to importExport.

Exporting to MYOB

onCourse allows you to generate a text file export of your Financial Data in order to import this information directly into MYOB. Search for the 'MYOB export' in the dashboard search and click the search result to run the export. Only Admin users will be able to see and access this.