Overview: A quick review of the optional relationship between parameters and filters for a SQL Server Reporting Services report. SQL Server 2008 R2 is used in all screenshots.
Level: Basic familiarity with parameters & filters is assumed.
Parameters in Reporting Services
When talking with people, sometimes I notice they refer to parameters synonymously with filters for a report. This is often true, but parameters can be so much more! Filters are quite often based on a user choice, but they don’t have to be. The way I think of the optional relationship:
A parameter can be bound to a filter, but it doesn’t have to be.
A filter can be bound to a parameter, but it doesn’t have to be.
Other (non-filtering) uses for a parameter include things like:
Hide/show report sections
Provide conditional formatting
Control sorting and grouping
Choices for actions
etc...
Two Types of Parameters
Using SSRS lingo, there are two types of parameters:
Dataset Parameter. The purpose of a Dataset Parameter (aka Query Parameter) is to perform a filter within the source query.
image
Report Parameter. The Report Parameter properties contain all of the settings related to a parameter (available values, default values, etc). A Report Parameter might or might not be associated to a Dataset Parameter. Unless it's hidden, the purpose of a Report Parameter is to interact with the user:
image
The vast majority of the time, a Report Parameter is associated to a Dataset Parameter – i.e., its purpose is to filter the data being displayed based on the user’s selection. However, if a Report Parameter is not bound to a Dataset Parameter, the Report Parameter could be used for all kinds of cool & creative things related to layout, formatting, or actions.
Two Types of Filters
The two types of filters in SSRS are:
Dataset Filter. This is the most common: filtering within the source query. When you implement a filter within the dataset, less data is sent over the network from the source database server to the Report Server - usually a good thing.
Report Filter. This includes filtering after the source query has come back – on a data region (like the Tablix), or a data grouping. When you implement a filter within the report, when the report is re-executed again with different parameter choices, the Report Server uses cached data rather than returning to the database server. This is a viable option if you intentionally want to return all possible data from the database server in the initial query. Keep in mind this could be far less performant if the volume of data is high & the report won’t be re-executed numerous times.
Generally speaking, using a Dataset Filter is the most efficient method.
Just to confuse the matter a wee bit - in talking with a business analyst or another non-BI-developer regarding requirements, I use the term Report Filter – when in reality I’m actually implementing a Dataset Filter. With non-developers, I try to use the conceptual terminology rather than the SSRS lingo.
A Report Parameter can be associated to a Dataset Filter, or a Report Filter, as shown in the next 2 sections.
Associating a Report Parameter to a Dataset Filter
When you add a query variable to parameterize a dataset (using the @ symbol), Reporting Services does you a favor and creates a Report Parameter automatically. This is the behavior most SSRS developers are familiar with.
image
image
After SSRS makes the association between the Dataset Filter and Report Parameter for you, if you check the Dataset Properties you’ll see the parameter properties:
image
Don’t forget! It is up to you to go visit the Report Parameter properties to validate them, and finish setting them up (for example, default values). Also, if you delete a parameter from the dataset, SSRS isn’t going to and delete it from the Parameters for you – you’ll need to do that manually.
Associating a Report Parameter to a Report Filter
Contrary to the previous example, this is handled a bit more manually. There’s 2 steps:
1. Associate the Report Filter (such as a Group property shown below) to a Report Parameter. Following is an example using @Country:
image
2. Set up the Report Parameter properties manually. SSRS doesn’t set it up dynamically like it does with a Dataset Filter (which is fine, since this is much less commonly used).
image
Finding More Information
Technet - Parameters (Report Builder 3.0 and SSRS)