Objective

The Report builder is a tool to aid in accessing the information stored within the system and extract it in meaningful and useful reports. Each report is custom designed to meet your institutions data requirements and needs. Reports can be produced in various output formats to meet the institutions requirements. The report builder uses SQL (Structured Query language) however you don’t need to be able to write in SQL to create report. The Report builder has a user friendly interface to help you create your own reports.

Complexity: Medium to High

Page Contents

1 Objective
2 Assumptions
3 Key terms and concepts
4 Implications
5 Workflow
6 Further reading
7 Related Pages

Assumptions

The user has a reasonable knowledge of the data base, fields and structure.
The user has access to the reports section of the system.

Key terms and concepts

Below is a list of the Current Base reports within the system, a short description of what each report might be used for and a few of the key fields with in that base report.

The data in the system is stored in various tables and this tool will allow for reports to be built to extract different sets of information and build a report to extract the exact information required.

The report builder is based tables, each table contains different parts of the system and information stored in it. Knowing what section(s) or the data required is the first step in building the report.

Report Name	Brief overview	Sample Data

Report Name	Brief overview	Sample Data
Student Details	The data in this section of the system will provide information from the student section of the database. Details associated with the edit details page from the student screen.	Last Name First Name Personal Title Home Institution
Student Audit Log Details	Fields from the Student Audit log. This base report will provide detailed report from the student Audit log. Primarily on has been changed / updated within the system. This is helpful for Auditing and managing Data change within the system.	Changed Entity name Changed Field name Old Value Text New Value Text Date Changed Changed By info
Contact Details	This level will provide data of who has access to the system and at what level. It provides information from the contact screen and is mainly for staff users of the system.	Role type Highest Academic Qualification Publication Research Home Institution
Provider Details	The Provider details includes data about the institution(s) within the system. If your Institution is a sole provider the data is limited to that. If your a consortium this report can provide contact details, agent arrangements within your group.	Sales Commission Percentage Agent Focus Agent Sales Representative Contract renewal Date
Student Course Enrolment Details	The report is two tables joined together to provide data from both the student level and the enrolled course(s) table. This will allow for more detailed reports on the students course activity	Course Name Enrolment Status code Start Date Contact hours Contact mode Funding type Fee Type Progression Status
Student Course Enrolment Audit Log Details	Similar to the Student Audit log this report will provide specific information on that has been changed in the Student Course enrolment section of the system.	Changed Entity name Changed Field name Old Value Text New Value Text Date Changed Changed By info
Student Unit Enrolment details	This report joins 3 table together Student information, Enrolled Course(s) and Units of Study.	Schedule Unit ID Code Schedule Unit Name Scheduled Unit status code Scheduled Unit provider Census date Unit Mode of Delivery Amount Paid HEIMS Loan FEE Payment Date
Student Result details	This Report provides information of student results. This is useful for results moderation and bench-marking prior to results release.	Published grade Grade Description GPS Unit Results Comments Mode of Delivery Class number Teacher Name Scheduled Hours
Course Details	The details in this report focus on the Course Page.	Course ID Course Name Course Level Standard Unit Fees International Unit fees Course Specialisation Code Accreditation Date CRICOS Code Credit points required
Unit Details	This report focus on Parent Unit details (not the Scheduled unit details)	Unit Code Unit Name Unit Provider Code Unit Category Id Unit Status Code Prerequisites Co-requisites Credit Statement HEIMS Discipline Code School Invoice Fee Code
Scheduled unit Details	This report Focus on the Schedule Unit details	Start date End Date HEIMS Census Date Scheduled Location Mode of Delivery Teacher First name Teacher Last Name Maximum Participants Minimum Participants Current Participants Class number
Scheduled Unit Fee Details	This report Focus on the Schedule Fee details	Start date End Date HEIMS Census Date Amount charged Amount Paid Help Loading Funding type Fee type
Session Details	The Section provides details on Sessions / RAPLA	Sessions Current Participants Session Type Session Subject Session Day Start time RAPLA Event Id RAPLA Appointment Id Building Room
Credit Point Audit		Unit Count Credit Points EFTSL Fees Invoiced Fees Paid Fees Owing Paid Scholarship
Scheduled Assessment View Details	This report will provide data for Scheduled Assessments within Scheduled units	Assessment Id Assessment Name Assessment Description Assessment Maximum mark Assessment Pass Mark Assessment Weighting
Assessment Results Details	This report focuses on the assessments within a Scheduled unit. It is also useful for Final grade analysis and bench-marking. It will provide information on block marking and comparison between cohorts.	Assessment result awarded mark Assessment results awarded grade Assessment results awarded date Assessment results penalty type Assessment results penalty amount
Student Review details	This report provides details on student reviews, date, reviewer and actions. This is useful for mentor reports, or professional experience reports	Contact first Name Contact last name Review date Notes Review Action
Student request details	Data in this report focuses on request from the student and for the student e.g. new student card, Remark of assignment etc	Customer request type description Status id Priority Customer request date Response required date Customer request name Description Request created date Request create by user login Last modified date

Report Builder operators

When building a Report Builder report the operators enable you to filter the results by indicating what is or isn’t required.

Operator types	Description	Sample Data
Equals (=)	For this operator the data within the column name has to be an exact match.	“Credit points” = 100 Only records with the exact match will be returned in the data set
Not Equal !=	For this operator the data within the column name can not be an exact match.	“Credit points” not = 100 Records with every number OTHER then 100 will be returned
Greater Than (>)	Normally used for numeric values like date, credit points	“Credit Points” > 100 (values returned will start from 101) “Start Date” > 01/01/2018 (values returned will start from 02/01/2018)
Less Than (<)	Normally used for numeric values like date, credit points	“Credit Points” < 100 (Values returned will be up to and including 99) “Start Date” < 01/01/2018 (values returned will be up to and including 31/12/2017)
Greater Than Equal to (>=)	Normally used for numeric values like date, credit points	“Credit Points” > 100 (values returned will start from 100) “Start Date” > 01/01/2018 (values returned will start from 01/01/2018)
Less than Equal to (<=)	Normally used for numeric values like date, credit points	“Credit Points” < 100 (Values returned will be up to and including 100) “Start Date” < 01/01/2018 (values returned will be up to and including 01/01/2018)
Like Case Insensitive	This can be used in type-able fields where the data can be in various formats e.g. Surname, Suburb Case insensitive is the key to return all of these values	Smith smith SMITH Flinders Park flinders park FLINDERS PARK
Like	Like is a wildcard value. This will return values that contain a pattern match	“Suburb” Like B Broadview Broadmeadows Brighton
Not Like	Displays records if a condition is not True or don’t match the selected pattern match	“Surname” NOT LIKE Smith Jones Brown Davies
In	IN is normally used when selecting from predefined options from the drop down box	“Course Enrolment Status Id” In ENROLLED All records with a Course enrolment status of enrolled will be returned
Not In	NOT IN is normally used when selecting from predefined options from the drop down box	“Course Enrolment Status Id” NOT IN ENROLLED All records who are in any status other than ENROLLED will be returned
Is
Is Not
Field Equals (=)	Compares the contents of one field with another field. Note that the fields used here do not need to be added to the report
Field Not Equals !=
Field Greater Than (>)
Field Less Than (<)
Field Greater Than Equal To (>=)
Field Less Than Equal to (<=)

Conditions

Conditions allow for clauses to be joined together to form more complex filter clauses. e.g. “Start Date” > 01/01/2019 AND “Course Enrolment Status Id” In ENROLLED

This will produce a list of students who have a start date greater than 01/01/2019 AND have a course enrolments status of enrolled. To appear in this data set the record must satisfy BOTH conditions. Or if more conditions are added using the AND condition the record has to meet all of the required criteria to be returned in the produced data set.

The other condition is OR. e.g. “Start date” >01/01/2018 OR “Course Enrolment Status Id” in Enrolled

This will produce a list of students who have a start date of 01/01/2018 OR a Course Enrolment status of Enrolled OR Both. The record only has to meet ONE criteria in the filter clauses to return a value in the produced data set.

The example used for this report is the Centrelink report. This is a pre-saved report within the report builder

“Person Status’ IN Active AND

“Course Enrolment Status Id” IN Enrolled AND

“Overseas Student” NOT EQUAL Yes.

All three of these criteria have to be met for a record to appear within the data set produced for this report.

These can have multiple filters that can be mixed and matched to reduce the data set to produce a specific data set from the whole database.

Implications

Global reports are reports that are available for anyone who has access to the report builder.
Private reports are only available to either the person who built the report.
Reports can also be restricted to access levels or to particular Providers / Institutions
When saving a new report 5 fields are required:

Required Field to save report	Brief Description

Required Field to save report	Brief Description
Report Name	Report name -to help locate the report for future use.
Description	A brief description of what the report is for or the data that the report will provide
Type	The section/type of data that the report belongs to: Provider Student Financial Marketing Saved Search All
Scope	Who has access to this report: Global - All user can access this report Private - Only I can use this report
Status	Active Archived In Active In Progress

Output Formats for Reports

Formats	Brief Overview

Formats	Brief Overview
Print Format HTML	On Screen data export.
Bulk student Select	Used for bulk reports and actions within the system
CSV	Comma separated vales, a text file that has a specific format which allows data to be saved in a table structure
Report Summary
Custom CSV
XSL	The original file extension type for Microsoft spread sheets.
Uploaded HTML template
JSON	Renders quoted strings in unicode format
XML	Metalanguage which allows users to define their own customized markup languages, especially in order to display documents on the internet.

Workflow

Click on the Reports option in the side menu
Click on the Report Builder option in the side menu
Click on the Global reports or Private reports drop box
Select the report to be loaded
Click on the Load Report button
The report has now been loaded

Click on the Reports option in the side menu
Click on the Report Builder option in the side menu
Click on the Global reports drop box
Select the report to be loaded
Click on the Load Report button
Scroll down to the Report Columns section
The fields that are highlighted are already in the report. To add additional fields hold the ‘Control’ button and click on the fields to be added. The newly added field will be highlighted
To remove fields hold the ‘Control’ and click on the fields to be removed. The newly removed fields will be un-highlighted.
Once all of the required fields have been added or removed click on the Add/Remove Columns button.

Click on the Reports option in the side menu
Click on the Report Builder option in the side menu
Click on the Global reports or Private reports drop box
Select the report to be loaded
Click on the Load Report button
The report has now been loaded
Scroll down to the Report Columns section
Update the required Column names
Update the Order Number numbers of the fields as required to have report produced in the required field order

Click on the Reports option in the side menu
Click on the Report Builder option in the side menu
Click on the Global reports or Private reports drop box
Select the report to be loaded
Click on the Load Report button
The report has now been loaded
Scroll down to the Filter Clause section
Click on the Column Name drop box
Select the Column name to be filtered
Select the required Operator

The order by function works as intended if you export the report in a csv format.

When you produce report in a HTML or create a bulk list, the screen displays the order by to the first column by default.

Workaround suggestions if you want to see order by in a HTML version.

You can either manually ascending/descending the columns by click on the first-row header once you get to the screen of bulk student select
Change the order number of the column you want to see the order by function to 1

Related Pages