Filter Data Dialog Box [Model Designer]
Use the Filter Data dialog box to limit the data returned in the report. To open the Filter Data dialog box, click the Filter button on the toolbar or select Filter on the Report menu.
Options
- Entities
Navigate the entities from which you can drag fields or entities to the filter area.
- Fields
Locate the fields that you want to add to the filter condition. Drag or double-click the fields to add them to the filter area.
- Search
Click to locate specific fields within an entity or within the data source.
- When adding a new condition, apply to all data in my report.
Clear this check box if you want to constrain the data returned to a specific list of instances. If this check box is cleared, you will not be able to change the base entity when creating the filter. By default, this check box is selected.
New Group Options
Click the New Group button and select one of the following options to create a group of filter conditions.
- All of
When you select All of, the results of the logical filter group must meet all specified criteria. The word 'and' is automatically inserted between clauses.
- Any of
When you select Any of, the results of the logical filter group must meet at least one of the specified criteria. The word 'or' is automatically inserted between clauses.
- None of
When you select None of, the results of the logical filter group must not meet any of the specified criteria. The word 'or' is automatically inserted between clauses and a logical 'not' is inserted for the entire group.
- Not all of
When you select Not all of, the results of the logical filter group must not meet all of the specified criteria. The word 'and' is automatically inserted between clauses and a logical 'not' is inserted for the entire group.
Filter Condition Options
When a field is dragged on to the filter surface, a filter condition is automatically created based on that field. You can adjust the condition so that it returns the results that you want. The filter condition is comprised of three parts: the field or entity name, the operator, and the value. To change the operator, click the operator and then select the new operator you want to use. To specify a value, do one of the following: type a value in the text box, select the check box for each of the listed values that you want to use in your filter or, in cases where a large amount of data can be selected, pre-filter the field or entity values from which you want to continue to build your filter.
Field and Entity Options
When you click the field or entity name that you added to the filter area, the following options are displayed.
- All
Returns all instances of the entity that meet the specified criteria. This option only appears when an entity is added to the filter area.
- Any
Returns any instances of the entity that meet the specified criteria. This option only appears when an entity is added to the filter area.
- No
Returns all instances of the entity that do not meet the specified criteria. This option only appears when an entity is added to the filter area.
- Not all
Returns any instance of the entity that does not meet the specified criteria. This option only appears when an entity is added to the filter area.
- Edit As Formula
Click the field name to display this option. Select to open the Define Formula dialog box and edit the field expression.
- Prompt
Click the field name to display this option. Select to prompt report readers to provide a value for the field before the report is run. For example, suppose you want report readers to view the report based on territory. Select the prompt option for the Territory field and when readers try to run the report from the report server they will see a prompt at the top of the report that requires them to enter the name of the territory. If you want a default value, enter one in the criteria area of the filter. . If no value is entered, the user must select one before running the report.
- Remove Condition
Click the field name to display this option. Select to remove the filter condition from the filter surface.
Comparison Operator Options
You can change comparison operators by clicking the operator and selecting from the displayed list. By default, the comparison operator is equals. Report Builder supports two types of predefined date types: values and ranges. Predefined date values return a single date or time value. Predefined range values return a range of date or time values.
- Not
Indicates that the operator should return the data that does not meet the specified criteria. Not can be applied to all operators except Less than, Greater than, Less than or equal, and Greater than or equal.
- Equals
Returns data that equals the specified value.
- In a List
Returns a drop-down list of values from which you can select multiple values.
- Greater Than
Returns data that is greater in value than the specified value.
- Greater Than or Equals
Returns data that is greater than or equal in value to the specified value.
- Less Than
Returns data that is less in value than the specified value.
- Less Than or Equals
Returns data that is less than or equal in value to the specified value.
- From…To
Returns data that falls between the two values specified.
- Contains
Returns data that contains the value specified. This condition is only applied to text values.
- In a Filter
Use this option to specify a filter on an entity before applying the entire filter to the report. Click no filter selected, and then click Create a new filter to launch a new instance of the Filter Data dialog box. This option is displayed only when a filter condition based on an entity is added to the filter area.
- Begins with
Returns data that begins with the specified value.
- Ends with
Returns data that ends with the specified value.
- Is empty
Returns data that contains Null values.
- Is blank
Returns data where a value is not specified.
- Is blank or empty
Returns data that contains Null or no values.
- Is a
Returns data for an instance of the specified entity.
- After
Returns data after the specified date.
- On or After
Returns data on or after the specified date.
- Before
Returns data before the specified date.
- On or Before
Returns data on or before the specified date.
- Today
Returns data for today's date at midnight (00:00:00 AM).
- Now
Returns data for today's date and time, for example, 11/30/2008, 10:45:22 AM.
- Yesterday
Returns data for today's date minus one day.
- Tomorrow
Returns data for today's date plus one day.
- Ago...Days
Returns data for a single date that occurs the specified number of days before today.
- Ago…Months
Returns data for a single date that occurs the specified number of months before the month of the current day.
- Ago…Quarters
Returns data for a single date that occurs the specified number of quarters before the quarter of the current day.
- Ago…Years
Returns data for the specified number of years before the year of the current day.
- First day of month
Returns data for the first day of the current month based on today's date.
- First day of quarter
Returns data for the first day of the current quarter based on today's date.
- First day of year
Returns data for the first day of the current year based on today's date.
- From now…Days
Returns data from today plus the number of days specified.
- From now…Months
Returns data from today's month plus the number of months specified.
- From now…Quarters
Returns data from today's quarter plus the number of quarters specified.
- From now…Years
Returns data from today's year plus the number of years specified.
- Last day of month
Returns data for the last day of the current month based on today's date.
- Last day of quarter
Returns data for the last day of the current quarter based on today's date.
- Last day of year
Returns data for the last day of the current year based on today's date.
- Last (n)…Days
Returns data for the last number of days specified, not including today's date.
- Last (n)…Days (including today)
Returns data for the next number of days specified, including today's date.
- Last (n)…Months
Returns data for the past number of months specified, not including the current month.
- Last (n)…Months (including this month)
Returns data for the past number of months specified, including the current month.
- Last (n)…Quarters
Returns data for the past number of quarters specified, not including the current quarter.
- Last (n)…Quarters (including this quarter)
Returns data for the past number of quarters specified, including the current quarter.
- Last (n)…Years
Returns data for the past number of years specified, not including the current year.
- Last (n)…Years (including this year)
Returns data for the past number of years specified, including the current year.
- Next (n)…Month
Returns data for all the days of the next calendar month based on today's date.
- Next (n)…Quarter
Returns data for all the days of the next calendar quarter based on today's date.
- Next (n)…Year
Returns data for all the days of the next calendar year based on today's date.
- Next (n)…Days (including today)
Returns data for the next number of days specified, including today's date.
- Next (n)…Days
Returns data for the next number of days specified, not including today's date.
- Next (n)…Months (including this month)
Returns data for the next number of months specified, including the current month.
- Next (n)…Months
Returns data for the next number of months specified, not including the current month.
- Next (n)…Quarters (including this quarter)
Returns data for the next number of quarters specified, including the current quarter.
- Next (n)…Quarters
Returns data for the next number of quarters specified, not including the current quarter.
- Next (n)…Years (including this year)
Returns data for the next number of years specified, including the current year.
- Next (n)…Years
Returns data for the next number of years specified, not including the current year.
- Previous…Month
Returns data for the all the days of the previous calendar month.
- Previous…Quarter
Returns data for all the days of the previous calendar quarter.
- Previous…Year
Returns data for all the days of the previous year.
- Previous…Days (including today)
Returns data for the last specified number of days, including today's date.
- Previous…Days
Returns data for the last specified number of days, not including today's date.
- Previous…Months (including this month)
Returns data for the last specified number of months, including the current month.
- Previous…Months
Returns data for the last specified number of months, not including the current month.
- Previous…Quarters (including this quarter)
Returns data for the last specified number of quarters, including the current quarter.
- Previous…Quarters
Returns data for the last specified number of quarters, not including the current quarter.
- This…Month
Returns data from all the days of the current calendar month based on today's date.
- This…Quarter
Returns data from all the days of the current calendar quarter based on today's date.
- This…Year
Returns data from all days of the current calendar year based on today's date.
- To Date…Month
Returns data from the first day of the month to the current day.
- To Date…Quarter
Returns data from the first day of the quarter to the current date.
- To Date…Year
Returns data from the first day of the year to the current date.
- Same Last Year…Month
Returns data for all the days of the same month last year.
- Same Last Year…Quarter
Returns data for all the days of the same quarter last year.
See Also
Reference
Define Formula Dialog Box [Model Designer]
Concepts
Other Resources
Working with Model Designer
Working with Roles, Source Fields, and Expressions
Working with Filters (Report Builder)