As you know, adding parameters to a SSRS report gives users the ability to filter and format data. In this post, we’ll show you an easy way to add parameters in SSRS, how to add drop-downs and how to allow users to select multiple parameters.
While there are a number of ways to add parameters in SSRS, one of the quickest is directly from Query Designer.
Use the form @variable_name. This will automatically create parameter objects in your report:
Your newly created parameter has no default value and no drop-downs for value selection. It’s simply a bare-bones textbox:
To make parameters more user-friendly, you can add drop-down menus (or drop-downs). Start by creating a dataset for the parameter that includes all possible parameter values.
(Note: If your SSRS supports shared datasets, and if this parameter is to appear on multiple reports, then you can create a single shared dataset for greater efficiency.)
Once you’ve set up the dataset, the next step is to assign available values:
The values you assign will be displayed in the drop-down. In this example, the default values are all the available values. (Note: Set up of default values is optional.) Of course, you can customize this using the “Specify Values” option or by using a “where” clause in the query.
When we put all these pieces together, we get a much more functional and friendly prompt:
Multiple value parameters
If you want, you can go one step further and allow users to select multiple values using the prompt. (Note: This can be problematic with some sets of data but for most types of values it’s straightforward.)
First, check “Allow multiple values” for the parameter:
Second, update the “where” clause to use the “IN” operator. (The parentheses around the @variable are important, so be sure to include them.)
The result is an even more useful prompt:
In some older versions of SSRS/SQL Server or for certain data, you may run into problems when implementing multiple value parameters. Unfortunately, the solutions to these types are problems are often complex and may require extra parameter processing via stored procedures. If you run into these difficulties, feel free to contact us for more information.