Basic Comparison Operators
This page will provide a description and examples for each Basic Comparison Operator that are made available in Report Builder
Overview
Generating a report using Report Builder can overwhelm you with lots of data if the results are not filtered out as you intend them to. Building reports in Report Builder allows you to use comparison operators to help you with sifting the right data or information that you need to meet your report requirements.
An example would be using a Course Enrolment Base Report limiting the results to a specific start date, only students who are enrolled on a specific Course, and only output records that are "Enrolled", etc. Generating a report with these filters will make a significant difference in the number of results found in your output file. The speed of generating it is also noticeably faster.
How to do it in Paradigm using the Report Builder menu?
After you have selected your Base Report, to further narrow down your output information - specific to what you only need to see on your report, you can add filter clauses using some of the basic comparison operators below. The following can be added under the Set Filter Clauses tab [FILTER CLAUSES section] in building your report, see Step 4 - Add Filter Clauses for more details on the workflow.
The Comparison Operators are used to determine if the two values (in the above screenshot - in Report Builder, the Column Name [second column]column and the Value column [fourth column])that you used are the same or not.
The Column Name section is where you can choose the field name that you are going to compare. The fields that you can see in the dropdown are the fields that are made available based on the Base or Saved report that you have loaded, see Step 1 - Select a Base Report.
The Operator column [first and third columns] is where you need to choose your intended operator - depending on how you are going to compare the field you have selected in the second column. The operators are categorised into three:
Inclusive - operators to use that will include results within a range of values
Exclusive - operators to use that will include results outside a range of values
Advanced - operators to compare two fields, see Complex Comparison Operators page
Refer to the description and examples below for each basic comparison operator under the Inclusive and Exclusive categories that you can use.
The Value column (fourth column) is where you enter the value that you would want to compare against the field that you have selected in the second column (Column Name section).
To understand better how the filter clause interacts with each other, check each basic comparison operator below and the examples of when and how you can use them in your report:
Inclusive Comparison Operators
Equals to the value (=)
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)
Greater than the value (>)
Normally used for numeric values like date and 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)
Greater than or equal to the value (>=)
Normally used for numeric values like date and 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 the value (<)
Normally used for numeric values like date and 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)
Less than or equal to the value (<=)
Normally used for numeric values like date and 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)
Contains the value (ilike)
Like is a wildcard value. This will return values that contain a pattern match (partial keyword matching) with the same character combination regardless of the case as set in the value.
Example 1
“First Name” Like Br (will return Brianna, Brian, Bridget) and also (brian, brianna, bridget)
Example 2
“UnitCode” Like ACC (will return all unit codes with “ACC” keyword, like ACCOUNTING 101, Acc101, etc.)
Contains the case sensitive value (like)
If you want a LIKE Operator which would mind the case on how the data are coded and that it contains/matches the exact case typed in the value set, then use this operator. This will return values that contain a pattern match (partial keyword matching) depending on the letter case.
Example 1
“First Name” Like Br (will return Brianna, Brian, Bridget) but not (brian, brianna, bridget)
Example 2
“UnitCode” Like ACC (will return all unit codes with “ACC” keyword, like ACCOUNTING101, etc. but not "Accounting101" - as long as the value is not in the same letter case)
Contains any of the following options (in)
IN is normally used when selecting predefined options from the drop-down box. You can also multi-select the values which you would want to be included in your clause.
NOTE: In Paradigm, fields that are designed to have the select values will appear automatically when you use this operator, e.g. Course Id.
Example 1
“Course Id” In "Bachelor of Accounting" (all records with a Course Id of “Bachelor of Accounting” will be returned)
Example 2
“Course Enrolment Status Id” In "Enrolled" , "Withdrawn", "Credited/RPL" (all records with an “Enrolled”, "Withdrawn", "Credited/RPL" Course Enrolment Status Id will be returned)
Is not empty (not null)
Checks the field or value if it is not blank - returns true if the field has not been left bank during record creation. Fields with zero value or that contain spaces are considered "NOT NULL"
Example 1
“Email” Is NOT NULL (returns a value if the field "Email" has been entered a value during record creation)
Example 2
“Invoice Id” IS NOT NULL (will return true if the field is NOT NULL, else false)
Exclusive Comparison Operators
Not equal to (!=)
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)
Does not contain (not ilike)
Like is a wildcard value. Using the NOT ILIKE operator will return values that do not contain a pattern match (partial keyword matching) with the same character combination regardless of the case as set in the value.
Example 1
“First Name” Not ILike Br (will not return Brianna, Brian, Bridget) and (brian, brianna, bridget) or anything that has the "Br" character combination
Example 2
“UnitCode” Not ILike ACC (will not return unit codes with “ACC” keyword, like ACCOUNTING 101, Accounting101, etc.)
Does not contain the case sensitive value (not like)
If you want a NOT LIKE Operator which would mind the case on how the data are coded and that it contains/matches the exact case typed in the value set, then use this operator. This will return values that contain a pattern match (partial keyword matching) depending on the letter case.
Example 1
“First Name” Not Like Br (will not return Brianna, Brian, Bridget) but will still return (brian, brianna, bridget)
Example 2
“UnitCode” Like ACC (will not return all unit codes with “ACC” keyword, like ACCOUNTING101, etc. but will still return "Accounting101" - as long as the value is not in the same letter case)
Does not contain any of the following options (not in)
NOT IN is normally used when selecting predefined options from the drop-down box, and it will choose which of the predefined options should be specifically excluded from the results. You can also multi-select the values which you would want to be included in your clause.
NOTE: In Paradigm, fields that are designed to have the select values will appear automatically when you use this operator, e.g. Course Id.
Example 1
“Course Id” Not In "Bachelor of Accounting" (all records with a Course Id “Bachelor of Accounting” will not be returned)
Example 2
“Enrolment Status Id” Not In "Applied", "Deferred", "Audit" (all records with enrolment status of "Applied", "Deferred", "Audit" will not be returned)
Is empty (null)
Checks the field or value if it is blank - returns true if the field has been left bank during record creation. Fields with zero value or that contain spaces are not considered "NULL"
Example 1
“Email” Is NULL (returns a value if the field "Email" has been left blank during record creation)
Example 2
“Grade Description ” IS NULL (will return true if the field is NULL, else false)
Last updated
