Working with a Filter Condition (Report Builder)
A filter condition is a statement that specifies the criteria Report Builder should use to determine whether the data is displayed in the report. To create a filter in Report Builder, you need to add at least one filter condition but you are not limited in the number of filter conditions that you can add. By using multiple filter conditions, you can create very complex filters. You can add, delete, and group filter conditions using different operators. You can even nest filter condition groups within other condition groups to achieve the results that you want. When the report runs, the filter is evaluated to determine if each instance of the data meets the criteria you specified in the filter. If the data meets the criteria, the data is added to the report. If the data does not meet the criteria, the data is not included in the report.
Adding Filter Conditions
When the Filter Data dialog box first opens, the entities and fields available in the report are displayed in the Explorer pane on the left. You can use any available entity or field to create your filter; the entity or field does not have to be displayed in the body of the report.
A filter condition is composed of three parts: the field or entity name, the comparison operator, and the criteria. To add a filter condition to your report, drag an entity or field name to the filter area and specify a comparison operator and criteria. For more information about comparison operators, see Selecting a Comparison Operator for a Filter Condition (Report Builder). For more information about specifying criteria, see Specifying Criteria for a Filter Condition (Report Builder).
After you add the filter condition to the filter area, you can move the condition around the filter area by selecting the entity or field name and dragging it. A filter condition can be moved between groups or it can be moved within the current group. When moved, filter conditions are added to the bottom of the group. For more information about grouping filter conditions, see Grouping Filter Conditions (Report Builder).
.gif "Filter Data dialog box")
Logical Operators
As you add additional filter conditions to the filter area, a logical operator is displayed between each filter condition. Report Builder supports the And and Or logical operators. By default, the logical operator is And, but you can easily change it by clicking And and selecting Or.
Filtering on Formulas
You can use Report Builder's function capabilities to filter the entire filter condition as a formula. You can filter on formulas when you want to create a filter clause that requires the manipulation of the formula for the field or on other functionality supported in the Define Formula dialog box, such as aggregation levels or removing duplicates, and this functionality is not in the Filter Data dialog box. For example, suppose you want to include only products in your filter condition that have a Sell Start Date of earlier than 2003. You could edit the Sell Start Date field formula using an available Date function.
To edit the field formulas in your filter, right-click the field name in the filter condition and select Edit as Formula. For more information about Report Builder functions, see Working with Formulas (Report Builder).
Prompting for Filter Values at Run Time
Prompt allows individuals reading your report to control the content of the report without having to edit the report file. Suppose you create a sales report that displays detailed sales information by territory. Instead of selecting which territory to display in the report, you can select Prompt and then, when the user opens the report on the report server, they can select which territory to view themselves.
To use the Prompt option, right-click the field or entity name in the filter condition and select Prompt. If you specify a specific value in the criteria text box, the report will run first using that value. Then, the person reading your report can select a new value. If you leave the criteria text box blank, the person reading the report must select a value before the report runs.
Note
When report readers view the parameter list, the values might not be sorted.
Change History
| Release | History |
|---|---|
17 July 2006 |
|
See Also
Tasks
How to: Add, Move, or Delete a Filter Condition (Report Builder)
How to: Group Filter Conditions (Report Builder)
Concepts
Grouping Filter Conditions (Report Builder)
Working with Filters (Report Builder)
Other Resources
Filter Data Dialog Box (Report Builder)