Use the guidelines in Table 21-3 when choosing an operator and specifying the required values. The operator list from which you can choose is populated based on the function that you are performing (for example, creating a filter or creating a dashboard prompt) and the type of column that you selected.
Table 21-3 Guidelines for Choosing an Operator When Creating a Column Filter
| Operator | Usage Guidelines |
|---|---|
|
is equal to / is in |
Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results include only records where the data in the column matches the value in the filter. |
|
is not equal to / is not in |
Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results include only records where the data in the column does not match the value in the filter. |
|
is less than |
Valid for a column that contains numbers or dates. Specify a single value. Results include only records where the data in the column is less than the value in the filter. |
|
is greater than |
Valid for a column that contains numbers or dates. Specify a single value. Results include only records where the data in the column is greater than the value in the filter. |
|
is less than or equal to |
Valid for a column that contains numbers or dates. Specify a single value or multiple values. Results include only records where the data in the column is less than or the same as the value in the filter. |
|
is greater than or equal to |
Valid for a column that contains numbers or dates. Specify a single value or multiple values. Results include only records where the data in the column is greater than or the same as the value in the filter. |
|
is between |
Valid for a column that contains numbers or dates. Specify two values. Result include only records where the data in the column is between the two values in the filter. |
|
is null |
Valid for a column that contains text, numbers, or dates. Do not specify a value. The operator tests only for the absence of data in the column. Results include only records where there is no data in the column. Sometimes it might be useful to know whether any data is present, and using the is null operator is a way to test for that condition. For example, suppose your business has a worldwide address book and you want to extract the United States addresses only. You could do this by checking for the presence or absence of data in the "State" field. This field should be unpopulated (null) for non-United States addresses and populated (not null) for United States addresses. You can obtain a list of United States addresses without the need to check the column for a specific value. |
|
is not null |
Valid for a column that contains text, numbers, or dates. Do not specify a value. The operator tests only for the presence of data in the column. Results include only records where there is data in the column. |
|
is ranked first |
Valid for a column that contains text or dates. Specify a single value. Results include only the first n records, where n is a whole number specified as the value in the filter. This operator is for ranked results. For example, you could use this operator to obtain a list that contains the first 10 brand names in alphabetical order. |
|
ranked last |
Valid for a column that contains text or dates. Specify a single value. Results include only the last n records, where n is a whole number specified as the value in the filter. This operator is for ranked results. For example, you could use this operator to obtain a list of the dates of the last 10 sales transactions. |
|
is in top |
Valid for a column that contains numbers. Specify a single value. Results include only the first n records, where n is a whole number specified as the value in the filter. This operator is for ranked results. For example, you could use this operator to obtain a list of the top 10 sales in dollars. |
|
is in bottom |
Valid for a column that contains numbers. Specify a single value. Results include only the last n records, where n is a whole number specified as the value in the filter. This operator is for ranked results. For example, you could use this operator to obtain a list of the customers reporting the fewest problems. |
|
contains all |
Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results include only records where the data in the column contains all of the values in the filter. |
|
does not contain |
Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results include only records where the data in the column does not contain any of the values in the filter. |
|
contains any |
Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results include only records where the data in the column contains at least one of the values in the filter. |
|
begins with |
Valid for a column that contains text, numbers, or dates. Specify a single value. Results include only records where the data in the column begins with the value in the filter. |
|
ends with |
Valid for a column that contains text, numbers, or dates. Specify a single value. Results include only records where the data in the column ends with the value in the filter. |
|
is LIKE (pattern match) |
Valid for a column that contains text. Specify a single value or multiple values. Requires the use of a percent sign character (%) as a wildcard character. You may specify up to two percent sign characters in the value. Results include only records where the data in the column matches the pattern value in the filter. |
|
is not LIKE (pattern match) |
Valid for a column that contains text. Specify a single value or multiple values. Requires the use of a percent sign character (%) as a wildcard character. You may specify up to two percent sign characters in the value. Results include only records where the data in the column does not match the pattern value in the filter. |