Applying filters

About this topic:

Audience: System administrators, SSRS Report administrators
Conditions/restrictions: None

Use filters to determine what data displays on a report. Filters reduce the amount of data retrieved from the data source (database) each time you run your report. A report without filters, or with very broad filters, will significantly slow down the performance of your database.

You can use:

A filter that retrieves data by using a fixed value. Each time you run the report, the filter has the same value, and the same data will be retrieved.
Note: In this help system, this type of filter is referred to as static.
A filter that uses a parameter. Each time you run the report, you can change the filter value, and different data will be retrieved.
Note: In this help system, this type of filter is referred to as dynamic.

Applying a static filter

When you use a static filter, the same data will be retrieved in the report each time you run it.

To apply a static filter, follow these steps:

Open your report in Report Builder.
In the Report Data pane, expand the Datasets folder.
Right-click vSSRSRB_(template-type), and select Query.
The Query Designer window opens.
To the right of Applied Filters, click Add Filter.
You will see default values under the Field name, Operator, and Value headings.
Note: The Field name defaults to the first field in the dataset list.
Click the field name in the Applied filters section.

A list of all available fields you can use in the report opens.
Select the field name you want to use as a filter by clicking it.
Your selection replaces the default field name.
Note: If you want to filter a report based on a date, you must use a date field ending in Date Only. If you choose a date field that does not end in Date Only, your report may not retrieve every record from the selected date.
For example, if you want to filter a report based on order bill date, select Bill Date Only, not Bill Date. If you use the option without Date Only, your results will not include the last date you choose. This happens because the time defaults to midnight. Selecting the Bill Date Only option ensures that the report retrieves every record from that date.

If the list of available fields does not include a Date Only field for the desired date, you must add that field to the report dataset.
Click the default operator.
A list of operators you can use opens.
Select the operator you want to use in your filter by clicking it.
Click the default value, and do one of the following:
- In the box, enter the value you want in your filter and click the plus sign to the right of it.
- Select the value you want in your filter by clicking it.
Click OK.
Click Run to preview your report.
Note: To return to the previous window, click Design.
Save your changes to the report.
Note: Be sure you are saving the changes to the copy of the template.

Applying a filter with a parameter

When you apply a dynamic filter, you add a parameter that you can change each time you run the report.

To apply a dynamic filter, follow these steps:

Open your report in Report Builder.
In the Report Data pane, expand the Datasets folder.
Right-click vSSRSRB_(template-type), and select Query.
The Query Designer window opens.
To the right of Applied Filters, click Add Filter.
You will see default values under the Field name, Operator, and Value headings.
Note: The Field name defaults to the first field in the dataset list.
In the Applied filters section, click the field name.

A list of all available fields you can use in the report opens.
Select the field name you want to use as a filter by clicking it.
Your selection replaces the default field name.
Note: If you want to filter a report based on a date, you must use a date field ending in Date Only. If you choose a date field that does not end in Date Only, your report may not retrieve every record from the selected date.
For example, if you want to filter a report based on order bill date, select Bill Date Only, not Bill Date. If you use the option without Date Only, your results will not include the last date you choose. This happens because the time defaults to midnight. Selecting the Bill Date Only option ensures that the report retrieves every record from that date.

If the list of available fields does not include a Date Only field for the desired date, you must add that field to the report dataset.
Click the default operator.
A list of operators you can use opens.
Select the operator you want to use in your filter by clicking it.
Leave the default value as (none).
Under Parameter, click the corresponding check box.
Click OK.
In the Report Data pane, you will see the new parameter in the Parameters folder.
Click Run to preview your report.
You can see your new parameter in the gray area above the report header like this:

Note: To return to the previous window, click Design.
Save your changes to the report.
Note: Be sure you are saving the changes to the copy of the template.

Deleting a filter that has a parameter

To delete a filter that has a parameter, you must delete both:

The filter in the Query Designer window
The parameter in the Parameters folder in the Report Data pane

To delete a parameter, follow these steps:

In Query Designer, highlight the field name of the parameter to delete.
Click and then OK.
In the Report Data pane, expand the Parameters folder.
Right-click the parameter to delete, and select Delete.
The delete confirmation window opens.
Click OK.
Click Run to preview your report.
Save your changes to the report.
Note: Be sure you are saving the changes to the copy of the template.