Reference: Filter Operators

Text Operators

Operator	Description
is blank	Field value is empty.
is not blank	Field value isn’t empty.
equal to	Field value is equal to comparison value. Case insensitive. Example: Cat is equal to cat.
not equal to	Field value isn't equal to comparison value. Case insensitive. Example: Dog is not equal to cat.
contains	Comparison value is a subset of field value. Case insensitive. Example: Cat contains AT.
contains (case sensitive)	Comparison value is a subset of field value. Example: Cat contains at.
does not contain	Comparison value isn't a subset of field value. Case insensitive. Example: Cat does not contain dog.
does not contain (case sensitive)	Comparison value isn't a subset of field value. Example: Cat does not contain AT.
starts with	Comparison value is a subset of field value, and the first character of the field value matches the first character of the comparison value. Case insensitive. Example: Cat starts with ca.
starts with (case sensitive)	Comparison value is a subset of field value, and the first character of the field value matches first character of comparison value. Example: Cat starts with Ca.
ends with	Comparison value is a subset of field value, and the last character of the field value matches the last character of comparison value. Case insensitive. Example: Cat ends with AT.
ends with (case sensitive)	Comparison value is a subset of field value, and the last character of the field value matches the last character of the comparison value. Example: Cat ends with at.
greater than	Field value comes before the comparison value in alphabetical order. This operator compares alphabetic sort order, not length. Case insensitive. Example: Cat is greater than dog.
greater than or equal to	Field value is the same as or comes before the comparison value in alphabetical order. This operator compares alphabetic sort order, not length. Case insensitive. Examples: Cat is greater than or equal to dog. Dog is greater than or equal to dog.
less than	Field value comes after comparison value in alphabetical order. This operator compares alphabetic sort order, not length. Case insensitive. Example: Dog is less than cat.
less than or equal to	Field value is the same as or comes after the comparison value in alphabetical order. This operator compares alphabetic sort order, not length. Case insensitive. Examples: Dog is less than or equal to cat. Cat is less than or equal to cat.

Currency and Numeric Operators

Operator	Description
equal to	Field value is equal to comparison value. Example: 123 is equal to 123.
not equal to	Field value isn't equal to comparison value. Example: 12 is not equal to 123.
greater than	Field value is greater than comparison value. Example: 321 is greater than 123.
greater than or equal to	Field value is greater than or equal to comparison value. Examples: 321 is greater than or equal to 123. 123 is greater than or equal to 123.
less than	Field value is less than comparison value. Example: 12 is less than 123.
less than or equal to	Field value is less than or equal to comparison value. Examples: 12 is less than or equal to 123. 123 is less than or equal to 123.

Date Operators

Operator	Description
is blank	Field value is empty.
is not blank	Field value isn't empty.
equal to	Date value is equal to comparison value.
not equal to	Date value isn't equal to comparison value.
greater than	Date value is later than comparison value. Example: 10/20/2016 is greater than 05/20/2016.
greater than or equal to	Date value is later than or equal to comparison value. Examples: 10/20/2016 is greater than or equal to 10/20/2016. 10/20/2016 is greater than or equal to 05/20/2016.
less than	Date value is before comparison value. Example: 05/20/2016 is less than 10/20/2016.
less than or equal to	Date value is before or equal to comparison value. Examples: 10/20/2016 is less than or equal to 10/20/2016. 05/20/2016 is less than or equal to 10/20/2016.

Boolean Operators

Operator	Description
is blank	Field value is empty. Represents False.
is not blank	Field value isn't empty. Represents True.
equal to	Field value is equal to the comparison value. For True, select the Comparison Value check box. For False, clear the Comparison Value check box.
not equal to	Field value isn't equal to the comparison value. For True, clear the Comparison Value check box. For False, select the Comparison Value check box.

Single Instance and Multi-Instance Operators

Field Type	Operator	Usage
Single Instance Multi-instance	exact match with the selection list	Field value matches exactly with comparison value. For single instance fields, this operator restricts a prompt so that it accepts a single value only. To enable a prompt for a single instance field to accept multiple values, use in the selection list .
Single Instance Multi-instance	NOT exact match with the selection	Field value doesn’t exactly match the comparison value.
Single Instance Multi-instance	subset of the selection list	Field value is a subset of the comparison value. Blank field values are a subset of the comparison values.
Single Instance Multi-instance	NOT subset of the selection list	Field value isn’t a subset of the comparison value. Blank field values are a subset of the comparison values.
Single Instance Multi-instance	superset of the selection list	Comparison value is a subset of the field value. Blank comparison values are a subset of the field values.
Single Instance Multi-instance	NOT superset of the selection list	Comparison value isn’t a subset of the field value. Blank comparison values are a subset of the field values.
Single Instance Multi-instance	count is equal to	The number of field values is equal to the comparison value. Example: To view workers with 2 dependents, set a filter for Dependents where count is equal to a Comparison Value of 2.
Single Instance Multi-instance	count is not equal to	The number of field values isn't equal to the comparison value. Example: To view workers with dependents, set a filter for Dependents where count is not equal to a Comparison Value of zero.
Single Instance Multi-instance	count is greater than	The number of field values is greater than the comparison value. Example: To view workers with more than 1 dependent, set a filter for Dependents where count is greater than a Comparison Value of 1.
Single Instance Multi-instance	count is greater than or equal to	The number of field values is greater than or equal to the comparison value. Example: To view workers with 1 or more dependent, set a filter for Dependents where count is greater than or equal to a Comparison Value of 1.
Single Instance Multi-instance	count is less than	The number of field values is less than the comparison value. Example: To view workers with fewer than 2 dependents, set a filter for Dependents where count is less than a Comparison Value of 2.
Single Instance Multi-instance	count is less than or equal to	The number of field values is less than or equal to the comparison value. Example: To view workers with 2 or fewer dependents, set a filter for Dependents where count is less than or equal to a Comparison Value of 2.
Single Instance	in the selection list	Comparison value is 1 of the possible field values. Example: A is in the selection list A, B, C.
Single Instance	not in the selection list	Comparison value isn't 1 of the possible field values. Example: D is not in the selection list A, B, C.
Multi-Instance	any in the selection list	Field value is 1 of the possible comparison values.
Multi-Instance	none in the selection list	Field value isn't 1 of the possible comparison values.
Single Instance Multi-instance	is empty	Field value is blank.
Single Instance Multi-instance	is not empty	Field value isn't blank.

A field value is a subset of the comparison value if all instances of the field are instances of the comparison. Inversely, the comparison value is a superset of the field value if it contains all instances of the field. Example: The field value is 1, 2, 3
and the comparison value is:

1, 2, 3, 4, 5
. The comparison value contains all instances of the field value. Therefore, the field value is a subset and the comparison value is a superset.

4, 5, 6, 7
. The comparison value doesn't contain any instances of the field value. Therefore, the field value isn't a subset and the comparison value isn't a superset.

Single Instance Examples

This table contains examples of expected results for the logical operators that compare single instance field values against a selection list.

Single Instance Field Value	Selection List	in the selection list	not in the selection list	exact match with the selection list	subset of the selection list	superset of the selection list
Atlanta	Chicago	False	True	False	False	False
Chicago	Chicago	True	False	True	True	True
Atlanta	Chicago Sacramento	False	True	False	False	False
Chicago	Chicago Sacramento	True	False	False	True	False
<blank>	Chicago Sacramento	False	True	False	True	False
<blank>	Chicago	False	True	False	True	False
Chicago	<blank>	False	True	False	False	True
<blank>	<blank>	False	True	True	True	True

These operators provide specialized logic that might not be appropriate when comparing a single instance field value against a selection list:

exact match with the selection list

subset of the selection list

superset of the selection list

In most cases, you should use either the in the selection list
or not in the selection list
operator.

Multi-Instance Examples

This table contains examples of expected results for the logical operators that compare multi-instance field values against a selection list.

Multi-Instance Field Value	Selection List	any in the selection list	none in the selection list	exact match with the selection list	subset of the selection list	superset of the selection list
Atlanta	Chicago Sacramento	False	True	False	False	False
Chicago	Chicago Sacramento	True	False	False	True	False
Chicago Atlanta	Chicago Sacramento	True	False	False	False	False
Chicago Sacramento	Chicago Sacramento	True	False	True	True	True
Chicago Sacramento Dallas	Chicago Sacramento	True	False	False	False	True
<blank>	Chicago Sacramento	False	True	False	True	False
<blank>	Chicago	False	True	False	True	False
Chicago	<blank>	False	True	False	False	True
<blank>	<blank>	False	True	True	True	True

Optional Prompts

If you want to evaluate blank values as True, you must include an Or
statement as part of the filter. The Or
statement prompts the user for a superset of the selection list
. If the user doesn't select a prompt value when running the report, the superset of the selection list
filter condition evaluates as True. In this case, an empty prompt value is the equivalent of selecting all possible values.

Example: To prompt for a location and ignore the prompt if the user doesn't enter a location, enter these values in the

Filter on Instances

grid.

And/Or	Field	Operator	Comparison Type
And	Location	in the selection list	Prompt the user for the value
Or	Location	superset of the selection list	Prompt the user for the value