SRS Filters
Posted: Monday, November 27, 2006 10:59 PM
by
dave
Filed under: Reporting Services, newsletter
I have found a lot of students and clients are not getting the most out of filters in there reports. So I thought I would explain a bit about how useful they can be. There are two places you can put a filter in a report.
- Data Region (Table, Matrix, List, Graph, Grouping Level)
Applying a filter to a DataRegion means I can show different views of the same data. In other words I can create one Dataset and let it feed multiple regions in my report. Say for example I need a report for management that shows the breakdown of Salaried employees vs hourly. They want to see two pie charts one will show the number of salaried employees by department the other Hourly. Instead of creating two queries one that selects salaried employees and one that selects hourly. I can just select all the data into one Dataset and filter the separate Graphs.
When you set the filter don't forget you are applying an expression so the value must begin with an equal sign. This is a common source of frustration the first time people use filters.
I can reuse this data in different ways throughout the report. In a matrix to show the number of employees by region, in a table sorted by Hire Date. All from one dataset.
You can apply a filter to a Dataset, which at first seems odd. Doesn't the Dataset already have its parameters. Why would I filter the report again? When you create a Cached Instance or a Snapshot the report is stored with its data and the parameters cannot be changed. However filters can still be applied to the report. Filters will use the current report parameter values to filter the report instead of creating a new cached instance or snapshot. This can be useful to avoid creating multiple versions of the same report in the cache.
If you would like to receive an email when updates are made to this post, please register here