Overview

Adding filter clauses To avoid the need to extract out all data from Paradigm in favour of targeting just the records that are desired, Report Builder will allow you to add filter clauses. This is both to minimise the time taken to generate a report and also remove the need for manually editing (removing rows) from the output before it is ready to support business decision making.

Basic Filter Clause Operators

---

Equals (=)

For this operator the data within the column name has to be an exact match.

Example 1

“Postal Code” = 3153 (only records with the exact match (3153) will be returned in the data set)

Example 2

“Course Accredited” = YES (only records with the exact match (YES) will be returned in the data set)

Not Equal !=

For this operator the data within the column name can not be an exact match.

Example 1

“Postal Code” Not Equal ! = 3153 (records with every postcode OTHER than 3153 will be returned)

Greater Than (>)

Normally used for numeric values like date, credit points. Return values greater than what has been set.

Example 1

“Start Date” Greater Than (>) 2020-01-01 (values returned will start after 2020-01-01)

Less Than (<)

Normally used for numeric values like date, credit points. Return values less than what has been set.

Example 1

“Start Date” Less Than (<) 2020-01-01 (values returned before 2020-01-01)

Greater Than Equal To (>=)

Normally used for numeric values like date, credit points. Return values greater than and equal to what has been set.

Example 1

“Start Date” Greater Than Equal To (>=) 2020-01-01 (values returned will start from 2020-01-01)

Less Than Equal To (<=)

Normally used for numeric values like date, credit points. Return values less than and equal to what has been set.

Example 1

“Start Date” Less Than Equal To (<=) 2020-01-01 (values returned on or before 2020-01-01)

In

IN is normally used when selecting from predefined options from the drop down box

Example 1

“Course Level” In Graduate (all records with a course level “Graduate” will be returned)

Not In

NOT IN is normally used when selecting from predefined options from the drop down box, and it it will choose which of the predefined options should be specifically excluded from the results.

Example 1

“Course Level” Not In Graduate (all records with a course level “Graduate” will not be returned)

Nested Filters

Brackets

Putting your clause inside brackets (parentheses) will tell the system to assess the conditions or clauses inside the brackets first before evaluating the clauses outside of it.

Example ()

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.

Base Report Parameters

Configuring Report Parameter Screen

• Appear on form

• Form label

• Mandatory

Workflow

Status
colour Green title 1

In the Bracket Open column, select a bracket if you are adding to add nested clauses.

Status
colour Green title 2

Select the field name you want to add a filter clause to under the Column Name.

Status
colour Green title 3

Select an operator under the Operator column required for the field you chose under the Column Name.

Status
colour Green title 4

Enter the value you want for the column name to be true against the comparison operator selected.

Status
colour Green title 5

Select a condition or logical operator (AND or OR) if you want to add another line of clause or condition.

Sample Clause:

Info
INFO: In the sample clause above, all data within the specified start and end date (2018-09-26 and 2021-09-26), and with a Completed unit enrolment status Id will be displayed.

---

---

_{Introduction to Report Builder | Step 1 - Select a Base Report | Step 2 - Add and Remove Fields | Step 3 - Rename and Reorder Fields | Step 4 - Add Filter Clauses | Step 5 - Save Report}