Building Reports in SQL Server 2005—Applying a Filter

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

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 requery; 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 Properties. On the Filter tab, enter the previous expression so that it looks like Figure 4-21.

Figure 4-21. Filter dialog box to exclude nonvisits