Share via

Filter Data Dialog Box [Model Designer]

  • Article

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

Model Designer F1 Help

Other Resources

Working with Model Designer
Working with Roles, Source Fields, and Expressions
Working with Filters (Report Builder)

Help and Information

Getting SQL Server 2005 Assistance