Export (0) Print
Expand All

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.

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.

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.

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 has 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.

Community Additions

ADD
Show:
© 2014 Microsoft