Specifying Criteria for a Filter Condition (Report Builder)
The filter criterion is the value to which all items within the field or entity is compared to determine if the data should be excluded from the report. For example, suppose you only want to view the sales data for France. You would probably create a filter condition where Country equals "France". When you run the report, all items contained within Country are compared to the value "France". If the item does not equal "France", it is not included in the report. Depending on how your data source is set up, criteria can be specified using a variety of methods, including lists, text boxes, and calendars.
Selecting Criteria from a List
When using the in a list operator, all of the available items for the field or entity in the filter condition are displayed in a list. To select criteria from this pre-populated list, select the check box for each item you want to use in the filter condition. You can select as many items as you want. Pre-populated lists vary in length depending on the number of items within the entity or field and the defaults set in the data source.
Creating a Pre-Filtered List
In some cases, a pre-populated list is not possible because the number of items displayed would make the list unmanageable. For example, suppose you add the Sales Order entity to your filter condition. Depending on the contents of the database, there could be millions of sales orders from which to choose. Listing each sales order in the list would be impractical.
In situations like this, you are prompted to create your own pre-filtered list. Using this pre-filtered list, you can limit the number of items displayed within a pre-populated list. Then, just like any other list, you can select which items to add to the filter condition. For example, if the Sales Order entity contains too many items to pre-populate the list and you want to see only those sales orders that occurred on a specific date, you can pre-filter the list using the Filter List dialog box. Here, you specify a filter condition to locate the sales orders that you want and add them to the Available Data list box. You can, at this point, add all the available data to your Selected Data list box, or select particular items. When you click OK, all items in the Selected Data list box are displayed as a pre-populated list when you click the criterion down arrow in the Filter Data dialog box, allowing you to select which criteria to add to the filter condition.
When creating a pre-filtered list, keep in mind that you are not specifying your criteria; you are simply limiting the criteria options available from which to select your filter condition criteria.
Using Relative Dates
Report Builder supports two types of relative dates in filters: relative date values and relative date ranges. Relative date values are used as filter criteria when filtering data for a specific point in time. To specify a relative date value, you use the calendar control that appears in the criteria area when you add a date or time field to the filter area and the relative date options from the bottom of the calendar control. Alternatively, you can just type in the appropriate text, for example 'today'. The match is not case sensitive.
Relative date ranges are specified using comparison operators and return data for a range of dates. For more information about date ranges, see Selecting a Comparison Operator for a Filter Condition (Report Builder).
Relative Date Values
The following values are supported.
| Name | Description | Example |
|---|---|---|
Today |
Returns data for today's date at midnight (00:00:00 AM). |
Suppose today is 10/15/09. Data is returned for the entire day of 10/15/09. |
Now |
Returns data for today's date and time. |
Suppose it is 1:30 PM on 10/15/09. Data is returned for the date 10/15/09 from midnight to 1:30:00 PM. |
Yesterday |
Returns data for today's date minus one day. |
If today is 10/15/09. Data is returned for 10/14/09. |
Tomorrow |
Returns data for today's date plus one day. |
If today is 10/15/09. Data is returned for 10/16/09. |
First day of month |
Returns data for the first day of the current month based on today's date. |
Suppose today is 7/12/07. Data is returned for the date 7/1/07. |
First day of quarter |
Returns date for the first day of the current quarter based on today's date. |
Suppose today is 7/12/07, this is the third quarter of the year. Data is returned for the date 7/1/07. |
First day of year |
Returns data for the first day of the current year based on today's date. |
Suppose today is 7/12/07. Data is returned for the date 1/1/07. |
Last day of month |
Returns data for the last day of the current month based on today's date. |
Suppose today is 7/12/07. Data is returned for the date 7/31/07. |
Last day of quarter |
Returns data for the last day of the current quarter based on today's date. |
Suppose today is 7/12/07, this is the third quarter of the year. Data is returned for the date 9/30/07. |
Last day of year |
Returns data for the last day of the current year based on today's date. |
Suppose today is 7/12/07. Data is returned for the date 12/31/07. |
(n) days ago |
Returns data for a single date that occurs the specified number of days before today. |
Suppose today is 7/12/07 and five days are specified. Data is returned for the date 7/8/07. |
(n) months ago |
Returns data for a single date that occurs the specified number of months before the month of the current day. |
Suppose today is 7/12/07 and 2 months are specified. Data is returned for the date 5/12/07. |
(n) years ago |
Returns data for a single date that occurs the specified number of years before the year of the current day. |
Suppose today is 7/12/07 and 3 years is specified. Data is returned for the date 7/12/04. |
(n) days from now |
Returns data for a single date that occurs the specified number of days from today. |
Suppose today is 8/15/09 and 10 days is specified. Data is returned for the date 8/25/09. |
(n) months from now |
Returns data for a single date that occurs the specified number of months from today's month. |
Suppose today is 4/3/10 and 5 months is specified. Data is returned for the date 9/3/10. |
(n) years from now |
Returns data from today's year plus the number of years specified. |
Suppose today is 4/3/10 and 2 years is specified. Data is returned for the date 14/3/12. |
See Also
Tasks
How to: Add, Move, or Delete a Filter Condition (Report Builder)
How to: Group Filter Conditions (Report Builder)
Concepts
Working with a Filter Condition (Report Builder)
Other Resources
Filter Data Dialog Box (Report Builder)
Filter List Dialog Box (Report Builder)