A filter is made up of one or more conditions. A single condition compares a field to a value. For example, "Order Amount is greater than $100.00" is a condition that only allows those records with an order amount of more than $100.00 to appear in the report.

A condition is made up of either three or four parts, depending on whether it's the first condition in a filter or not: the field to filter on (for example, Order Amount), the operator, the value or values, and the connection.

Operator

How the field is compared to the value is called the operator. There are several operators available; they are shown in the table below. Some operators use more than one value; for example, is between requires two values (as in "Order Date is between 01/10/96 and 12/31/96"). Other operators do not use any value; for example, "Zip code is blank" is either true or not without needing a value.

OperatorDescription
equals

The data stored in the field must exactly match the value you specify.

Example: Last Name equals Johns

The report shows only records with "Johns" as the last name. Records with "Johnston," "Johnson," and "Johnstone" are not included.

does not equal

The data stored in the field may be anything but the value you specify.

Example: City does not equal Calgary

begins with

The data in the field starts with the value specified.

Example: Last Name begins with Johns

The report shows records with "Johns," "Johnston," "Johnson," and "Johnstone" as the last name.

This operator only appears for string fields.

does not begin with

The data in the field starts with anything but the value specified.

Example: Last Name does not begins with Johns

The report shows records other than those with "Johns," "Johnston," "Johnson," and "Johnstone" as the last name.

This operator only appears for string fields.

ends with

The data in the field ends with the value specified.

Example: Last Name ends with son

The report shows records with "Kingston" and "Johnson" but not "Johnston" as the last name.

This operator only appears for string fields.

does not end with

The data in the field ends with anything but the value specified.

Example: Last Name does not end with son

The report shows records other than those with "Kingston" and "Johnson" as the last name.

This operator only appears for string fields.

contains

The field has the specified value anywhere in the data it contains.

Example: Outline contains Federal Gov

All records with "Federal Gov" somewhere in the Outline field are included.

This operator only appears for string fields.

does not contain

The field does not have the specified value anywhere in the data it contains.

Example: Outline does not contain Federal Gov

This operator only appears for string fields.

is blank

Includes all records where the specific field is empty or contains an unknown (null) value.

Example: ZipCode is blank

is not blank

Includes records where the specific field contains data.

Example: ZipCode is not blank

greater than

Includes records where the specific field contains a value in excess of the specified value.

Example: Amount is greater than 100

Example: City is greater than M

greater than or equal

Includes records where the specific field contains a value in excess of or equal to the specified value.

Example: Amount is greater than or equal to 100

Example: City is greater than or equal to M

less than

Includes records where the specific field contains a value under the specified value.

Example: Amount is less than 100

Example: City is less than M

less than or equal

Includes records where the specific field contains a value under or equal to the specified value.

Example: Amount less than or equal to 100

Example: CITY is less than or equal to M

is between

Includes records where the specific field contains values in a specified range.

Example: Amount is between 150 and 250

Example: Date Started is between 05/01/94 and 12/31/94

is not between

Includes records where the specific field does not contain values in a specified range.

Example: Amount is not between 150 and 250

Example: Date Started is not between 05/01/94 and 12/31/94

is one of

Includes records where the specific field contains one of up to ten specified values. The equivalent expression would be created using ten separate "equals" conditions.

Example: City is one of Tokyo, Calgary, New York

is not one of

Includes records where the specific field does not contain one of up to ten specified values. The equivalent expression would be created using ten separate "does not equal" conditions.

Example: City is not one of Tokyo, Calgary, New York

is Yes

Includes records where the specific field contains Yes or true.

Example: Active is Yes

This operator only appears for Boolean (true or false or yes or no) fields.

is No

Includes records where the specific field contains No or false.

Example: Active is No

This operator only appears for Boolean (true or false or yes or no) fields.

Connection

Conditions are connected together with a connection. The connection can be either AND or OR. The AND connection means the two conditions it connects must both be true for a record to be included in the report, while the OR connection means that either one or both can be true for a record to be included.

You can think of AND as "more restrictive." For example, if the first condition is "City equals San Francisco," that gives a certain set of records. Adding a second condition such as "Order Amount is greater than 100.00" using the AND connection means that for any given record, the city must be San Francisco and the order amount must be greater than $100.00. Since that excludes San Francisco records with an order amount of less than $100.00 (which just specifying the first condition includes), these two conditions together are more restrictive (there are fewer records in the report) than either of these conditions alone.

You can think of OR as "less restrictive." For example, if the first condition is "City equals San Francisco" and you add a second condition such as "City equals Los Angeles" using the OR connection, this means that for any given record, the city can either be San Francisco or Los Angeles. These two conditions together are less restrictive than either of these conditions alone, since the report includes records matching both conditions.

Notice the use of AND and OR with filters can be the opposite of how these words are used in English. For example, someone might say "print out all of our customers from San Francisco and Los Angeles." You might think you use the AND connection in this case, but in fact you use the OR connection since you want records where the city is either San Francisco or Los Angeles. The way to think of it is this: for each record, check each condition. It is not possible, for example, for the city for any one record to be both San Francisco and Los Angeles at the same time. It could be either one, however, so OR is the correct connection to use.

Watch out if you need to use both AND and OR connections in a filter. Conditions connected with AND are evaluated before those connected with OR. For example, if you want all customers from San Francisco or Los Angeles where the sales amount is more than $50, you might think you could use "City equals San Francisco or City equals Los Angeles and Sales is greater than 50." However, this likely won't give you the results you want because the AND is evaluated first. So, this gives all customers from Los Angeles where the sales amount is more than $50 (the second and third conditions ANDed) or those from San Francisco regardless of the sales amount (the first condition).

If you need to use both AND and OR connections in a filter, you need to use filter groups. All the conditions in a filter group use the same connection. Filter groups are connected together using another connection. So, to get all customers from San Francisco or Los Angeles where the sales amount is more than $50, use one filter group of "City equals San Francisco or City equals Los Angeles" and another of "Sales is greater than 50." The two groups are connected using AND. Internally, the filter condition is handled with parentheses: "(City equals San Francisco or City equals Los Angeles) and Sales is greater than 50." However, filter groups make it easier to understand how the conditions are combined than worrying about where parentheses should go and how many there are.