Overview

Now that you’ve learned some basic filter clause operators from this knowledge article: https://silverband.atlassian.net/wiki/spaces/DPG/pages/1457127488, you can add more complex filters by using the operators mentioned in this article. The following operators are mostly used to compare two fields.

Navigation

Comparison Operators

Like

Like is a wildcard value. This will return values that contain a pattern match (partial keyword matching) with the same case as set in the value.

Example 1

“Suburb” Like B (will return Broadview, Broadmeadows, Brighton)

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 ACCOUNTING 101, etc.)

back to top

Like Case Insensitive

If you want a LIKE Operator which would not mind the case on how the data are coded as long as it contains/matches the value set, then use this operator. This will return values that contain a pattern match (partial keyword matching) regardless of the letter case.

Example 1

“First Name” Like Br (will return Brianna, Brian, Bridget) as well as (brian, brianna, bridget)

Example 2

“UnitCode” Like ACC (will return all unit codes with “Acc” keyword, like Accounting101, etc.)

back to top

Not Like

Displays records if a condition is not TRUE or don’t doesn’t match the selected pattern match.

Example 1

“Surname” NOT LIKE Smith (will return other surnames like: Jones, Brown, Davies except for Smith)

Example 2

“Student Number” NOT LIKE A (will return all other student numbers except for the ones that starts with A)

back to top

Is

Checks whether a value IS NULL. The IS NULL returns TRUE if the expression IS NULL, otherwise, it returns FALSE.

Example 1

“Grade Description ” IS NULL (will return true if the field is NULL, else false)

Is Not

Checks whether a value IS NOT NULL. The IS NOT NULL returns TRUE if the expression is NOT NULL, otherwise, it returns FALSE.

Example 1

“Invoice Id” IS NOT NULL (will return true if the field is NOT NULL, else false)

Field Equals (=)

Compares the contents of one field if the same or equal to another field. 

Example 1

“Country” Field Equals (=) “Billing Country” will return data which has the same country and billing country information

 Sample Output

back to top

Field Not Equals !=

 Will return data or information with a field not equal to the other field.

Example 1

“Country” Field Not Equals (=) “Billing Country” will return data which has a country not equal or the same as the billing country

Sample Output

back to top

Field Greater Than (>)

 Will return data or information with a field greater than the other field.

Example 1

“Withdrawal Date” Field Greater Than (>) “Dest Census Date will return data which has a withdrawal date greater than the Dest Census Date.

Sample Output

back to top

Field Less Than (<)

 Will return data or information with a field less than the other field.

Example 1 

“Amount Paid” Field Less Than (<) “Amount Due” will return data which has an amount paid less than the amount due.

Image Added

Sample Output

Image Added

back to top

Field Greater Than Equal To (>=)

Will return data or information with a field greater or equal to the other field. The difference with between this and the Field Greater Than (>) operator is that this would include the field which is also equal to the other field.

Example 1

“Withdrawal Date” Field Greater Than Equal To (>=) “Dest Census Date will return data which has a withdrawal date greater than or equal to the Dest Census Date.

Sample Output

back to top

Field Less Than Equal

To (<=)

Will return data or information with a field less than or equal to the other field. The difference with between this and the Field Less Than (>) operator is that this would include the field which is also equal to the other field.

Example 1 

“Amount Paid” Field Less Than Equal To (<=) “Amount Due” will return data which has an amount paid less than or equal to the amount due.

Image Added

Sample Output

Image Added

back to top

_{Advanced Report Builder | Order By Clauses | Alternative Data Formats | Bulk Student Select | Complex Filter Clause Operators | Nested Operators Using Brackets | User-Defined Parameters | Summarise Data | Generic Report Query}