Pro SQL Server 2012 Reporting Services

CHAPTER 6  BUILDING REPORTS

Figure 6-32. Report generated with multiple selection criteria

The completed report for multivalued parameters in the Pro_SSRS project is called

EmployeeServiceCost_MVP.rdl.

Applying a Filter

You might recall from Chapter 2 that you enhanced the performance of your stored procedure,

Emp_Svc_Cost, by removing the criteria that looked only at visits. You will now apply a filter to the

report to take the place of the original query criteria so that only visits will be displayed.

You can use filters to exclude values from a report after the results have been returned by the query.

Filters, in that sense, will prevent a re-query; however, the full dataset will be returned to the report. In

the example in Chapter 2, you knew that a limited number of excess rows would be returned. You should

use filters when a query parameter is not supported by the data provider or with report snapshots. You

should also use filters in reports that address a specific request or solution, and that are based on the

same stored procedure as other reports, because you can use filters without modifying an existing stored

procedure. Here is a simple filter expression applied to the Table data region of your report that will

exclude any rows that are not visits:

=Fields!ServiceTypeID.Value = "V"

To add the filter expression, on the Layout tab, right-click the upper-left section of the table, and

select Tablix Properties. On the Filters tab, enter the previous expression so that it looks like Figure 6-33.

Add the filter to the EmployeeServiceCost_MVP.rdl report included in the Pro_SSRS project from the

book source code.