Selecting a Comparison Operator for a Filter Condition (Report Builder)
Comparison operators, such as equals, greater than or less than, are used to evaluate the filter condition for each instance of the entity to determine whether the data should be included in the report. Comparison operators are used with text, numeric, or date data. Equals and Not equals are used with Booleans and EntityKeys. By default, equals is displayed as the comparison operator but you can select from any of the other available options. The available options change depending on the data type of the field or entity added to the filter condition.
Using the Not Option
The Not option is used to indicate that only those items that do not meet the specified filter condition should be included in the report. For example, suppose you want to see the sales data for all countries except the United States. Instead of selecting all the available countries and listing them in your filter condition, simply use the Not option in the filter condition to state that Country field items should not equal the United States. The Not option can be used with any other available comparison operator.
Filtering Empty or Blank Values
Some fields or entities in your data source might reference tables in the underlying database that contain blank (" ") or empty (Null) values. For example, suppose your company sells bicycles over the Internet. One of the fields in the online sales order is an e-mail address field; however, customers are not required to complete this field when making a purchase. If the customer submits an e-mail address, that information is added to a field called emailaddress in a database table. If the customer does not provide an e-mail address, the emailaddress field might contain an empty or blank string value instead. Blank only applies to text fields, but empty applies to all data types. When creating and running your reports, the empty or blank information is included in the data returned unless you specifically exclude the data using a filter condition.
When filtering empty values, use the Is Empty option. When filtering for blank strings, use the Is Blank option. If you want to filter on both empty and blank values, use the Is Blank or Empty option.
Using Relative Dates
Report Builder supports two types of relative dates in filters: relative date values and relative date ranges. Relative date ranges are specified using special comparison operators and return data for a range of dates.
Date values are specified directly in the text box when indicating your criteria. For more information about relative date values, see Specifying Criteria for a Filter Condition (Report Builder).
Relative Date Ranges
Relative date range operators can be used with date/time fields only. The following ranges are supported.
| Name | Description | Example |
|---|---|---|
To Date…Month |
Returns data from the first day of the month to the current day. |
Suppose today is 5/16/06. Data returned falls from 5/1/06 to 5/16/06. |
To Date…Quarter |
Returns data from the first day of the quarter to the current date. |
Suppose today is 11/25/10 and this is the fourth quarter. Data returned falls from 10/1/10 to 11/25/10. |
To Date…Year |
Returns data from the first day of the year to the current date. |
Suppose today is 11/25/10. Data returned falls from 1/1/10 to 11/25/10. |
Next (n)…Days |
Returns data from today plus the number of days specified. |
Suppose today is 12/18/09 and 12 days are specified. Data returned falls between 12/18/09 and 12/30/09. |
Next (n)…Days (including today) |
Returns data for the next specified number of days, including today's date. |
Suppose today is 12/18/09 and 12 days are specified. Data returned falls from 12/18/90 to 12/30/09, including today's date. |
Next (n)…Months |
Returns data from today plus the number of months specified. |
Suppose today is 12/18/09 and 4 months is specified. Data returned falls between 12/18/09 and 4/18/10. |
Next (n)…Months (including this month) |
Returns data for the next specified number of months, including today's month. |
Suppose today is 12/18/09 and 4 months is specified. Data returned falls from 12/18/09 to 4/18/10, including today's month. |
Next (n)…Years |
Returns data from today plus the number of years specified. |
Suppose today is 12/18/10 and 2 years is specified. Data returned falls between 12/18/10 and 12/18/12. |
Next (n)…Years (including this year) |
Returns data for the next specified number of years, including today's year. |
Suppose today is 12/18/10 and 2 years is specified. Data returned falls from 12/18/10 to 12/18/12. |
Last (n)…Months |
Returns data for the last specified number of months, including the current month. |
Suppose today is 6/15/07 and 2 months is specified. Data returned falls between 4/1/07 and 5/31/07. |
Last (n)…Months (including this month) |
Returns data for the last specified number of months, including the current month. |
Suppose today is 6/15/07 and 2 months is specified. Data returned falls from 5/1/07 to 6/16/07. |
Last (n)…Days |
Returns data for the last specified number of days, not including today's date. |
Suppose today is 6/15/07 and 5 days is specified. Data returned falls between 6/10/07 and 6/14/07. |
Last (n)…Days (including today) |
Returns data for the last specified number of days, including today's date. |
Suppose today is 6/15/07 and 5 days is specified. Data returned falls from 6/10/07 to 6/15/07. |
Last (n)…Quarters |
Returns data for the last specified number of quarters, not including the current quarter. |
Suppose today is 6/15/07 and 1 quarter is specified. Data returned falls between 1/1/07 and 3/31/07. |
Last (n)…Quarters (including this quarter) |
Returns data for the last specified number of quarters, including the current quarter. |
Suppose today is 6/15/07 and 1 quarter is specified. Data returned falls from 1/1/07 to 6/16/07. |
Last (n)…Year |
Returns data for all the days of the previous year. |
Suppose today is 6/15/07. Data returned falls between 1/1/06 and 12/31/06. |
Last (n)…Years (including this year) |
Returns data for the last specified number of years, including today's year. |
Suppose today is 6/15/07 and 2 years is specified. Data returned falls from 6/15/05 to 6/15/07. |
This…Month |
Returns data from all the days of the current calendar month based on today's date. |
Suppose today is 8/14/08. Data returned falls from 8/1/08 to 8/31/08. |
This…Quarter |
Returns data from all the days of the current calendar quarter based on today's date. |
Suppose today is 8/14/08. Data returned falls from 7/1/08 to 9/30/08. |
This…Year |
Returns data from all days of the current calendar year based on today's date. |
Suppose today is 8/14/08. Data returned falls from 1/1/08 to 12/31/08. |
Same Last Year…Month |
Returns data for all the days of the same month last year. |
Suppose today is 8/31/09. Data returned is from 8/1/08 to 8/31/08. |
Same Last Year…Quarter |
Returns data for all the days of the same quarter last year. |
Suppose today is 8/31/09. Data returned is from 7/1/08 to 9/30/08. |
See Also
Tasks
How to: Add, Move, or Delete a Filter Condition (Report Builder)
How to: Group Filter Conditions (Report Builder)
Concepts
Working with Filters (Report Builder)
Grouping Filter Conditions (Report Builder)
Other Resources
Filter Data Dialog Box (Report Builder)