Parameters play a key role in the functionality of SQL Server Reporting Services (SSRS) reports. They can assist in controlling datasets, as datasets parameters, or they can help with the internal affairs of a report, such as in an expression that determines whether an object should be shown or be hidden. They can also link different reports together, when used as drill-through parameters. This article gives an overview of parameters and how they can be used to create efficient reports.
Create a Parameter
Let’s dive in and create our first parameter. In the Report Data pane, right-click on Parameters and choose Add Parameter…
A dialog pops up that allows you to specify the properties for our new parameter.
The following properties can be set:
- * Name: the name of the parameter. This is important when you use the parameter in queries or in report expressions.
- * Prompt: a text message that will be displayed when the report asks you to specify values for the parameter.
- * Data type
- * Allow null or blank checkboxes: if these are not selected, the parameter must always be assigned a value.
- * Allow multiple values checkbox: specifies whether a parameter can take one or multiple values. This can have an impact on how queries need to be written.
- * Select parameter visibility: if a parameter is visible, report users can assign values to the parameter at runtime. If a parameter is hidden, it can only be changed through the report URL, a subscription or on the report server. If a parameter is internal, it can only be set in the report definition.
When we run the report at this moment, the following prompt is shown:
The parameters pane prompts us to specify a value for the parameter. Once this is done, the report can be run with the new parameter value by clicking on the View Report button. If a value isn’t specified and the parameter cannot be null or blank, the following error is shown:
Note that the parameters pane can differ in layout, depending on whether you are working in Visual Studio, Report Builder, or SharePoint. There is no easy way of changing this layout, which is one of the biggest disadvantages of Reporting Services.
Let’s show the value of the parameter in a textbox, so we can verify its input. This can be done with the following expression:
When a value is assigned to the FirstParameter parameter and the report is run, we achieve this result:
Configuring Values for a Parameter
The user doesn’t always have to type in the parameter prompt, of course. In the parameter properties dialog, you can specify a list of available values for the parameter.
There are two options:
- * Specify values: the values are hard-coded into the report design, as in the screenshot above.
- * Get values from a query: you can specify a dataset that will provide the available values. From that dataset, you need to map the label and the value fields.
A parameter actually has two values: a label and a value. The label is what is shown to the report users, while the value is used as sort of a key by the report itself. It is possible for a parameter to have different values and the same label.
When the report is run, the user can now choose from a dropdown list, instead of being forced to type in a value.
Finally, you can also provide a default value for a parameter. When a parameter has been assigned a default value, the report will not prompt the user to specify a value. In other words, the report can be rendered with no user interaction, but the user can still change the parameter value after the report has been rendered (which renders the report again with the new parameter value).
The default value is specified at the parameter properties dialog in the Default Values pane.
Again you have the option to either specify values directly, as in the screenshot, or to use a query to fetch the values. In contrast with the available values, you can specify only the value, not the label.
When the report is run, it renders immediately without interaction:
However, the textbox we created earlier displays “1”, which is the value of the parameter, instead of the text. In order to display the label of the parameter, change the expression to the following:
The parameter has additional values that can be used in an expression:
Count and IsMultiValue are useful when dealing with multi-valued parameters.
Changing the Report Layout with a Parameter
Let’s illustrate the concept of parameters with an example. We have a simple report with a graph and a details table.
Suppose we want to give the user the flexibility of hiding the details table. This can be accomplished with the use of a parameter. Let’s create a parameter called ShowDetails.
The available values are configured with the following values:
On the tablix, we can set the visibility with this expression:
The text value from the parameter is converted to a Boolean. A value of true means the tablix will be hidden.
If we want the report to run immediately, we can assign a default value to the parameter.
The report will render without any user interaction. The user can still change the parameter, though, and run the report again.
Adding a Dataset Parameter
Aside from changing the layout or organization of a report, parameters are very useful when dealing with datasets. They can be used, for example, to filter the data that is not needed in the report. A dataset parameter can be used in two different ways:
- * Inside the query itself. Some data sources, such as SQL Server itself, support query parameters. In those cases, you can filter directly at the source, which can save resources tremendously.
- * In a filter on the dataset. You can define a filter inside the dataset using an expression. The downside is that all of the data is read into the report and is subsequently filtered.
Let’s illustrate the first option with an example. Suppose we have added a parameter Year to the report and that it can take the values 2007 or 2008. In the dataset, we can add a query parameter. Let’s call it @YearFilter and map it to our Year parameter.
Remark that the dropdown box automatically selects from the report parameters, but you can also add an expression by clicking on the fx symbol. This expression can be more complicated than just a single parameter value.
Inside the T-SQL query, we can add the parameter YearFilter by referencing it directly.
When the report is rendered, the user will be asked to provide a value for the “Year” parameter. The report then fetches the data, which is filtered at the source.
With SQL Server Profiler, we can monitor the query that is sent to the relational database engine. We can see that SSRS uses dynamic SQL to put the value of the YearFilter parameter into the query.
You can save a lot of work when you just use a parameter in a query; SSRS will automatically create a matching report parameter with the same name. Let’s add a parameter called @Category to the query.
When you close the dataset properties with OK, SSRS adds a parameter called Category to the report. All you have to do is configure available and default values for this new parameter.
When using the same parameter in different datasets, it is advisable to keep the naming consistent in order to avoid confusing and possible mistakes.
Another option to add a query parameter is in the relational query designer. However, this option is only available in Report Builder, not in Visual Studio. For more information, please refer to Relational Query Designer User Interface (Report Builder).
Filtering a Dataset
The second use of parameters in datasets is in the filter pane.
You can add multiple filters and use various operators. The value field is usually populated by a parameter, but this can, of course, be any other expression. The downside of this approach is that the filtering takes place after the data has been loaded, in contrast with query parameters, where the data is filtered at the source. This comes with a performance penalty, but sometimes you don’t have a choice; for example, when using a non-relational source such as an XML file or a SharePoint list.
For more information, see the MSDN article, Add a Filter to a Dataset (Report Builder and SSRS).
Parameters in MDX queries
Adding parameters to SQL queries is relatively straightforward. It’s a bit more complicated for MDX queries (for example on top of SSAS Multidimensional), but the MDX query designer provides us with great assistance.
In the designer, you can drag any dimension member to the filter pane, where you can assign a value for that filter. If you check the parameter checkbox, SSRS will convert the filter to a parameter and add it to the report for you. If a value was specified for the filter, SSRS will use it as the default value. The article Define Parameters in the MDX Query Designer for Analysis Services (Report Builder and SSRS) gives an overview of how to add parameters in the MDX query designer.
The query designer puts the parameter into a sub-query along with the StrToSet function.
By default, the newly created parameter will be able to support multiple values. The ease of setting up multi-valued parameters is one of the strengths of MDX. You might want to change the prompt into something more user-friendly, though.
SSRS creates an additional hidden dataset to fetch the available values for DateCalendarYear.
The default value is hard-coded into the parameter properties.
To view or modify the hidden dataset, right-click the Datasets node in the Report Data pane and choose Show Hidden Datasets.
This dataset contains a calculated field called ParameterCaptionIntended, which adds extra indentation to the caption of the parameter.
This results in a tree-like dropdown to select values for the parameter.
Note that there is a (Select All) and an All Periods choice in the parameter dropdown. The (Select All) option is merely a construct of the dropdown box allowing you to easily select or deselect all choices. This can be verified by using Profiler:
All of the options are passed down to the SSAS cube. The All Periods option is the renamed All member of the dimension, which resembles the aggregation of the values of all the members in the hierarchy. When selecting this member, we can see in Profiler that only this value is sent to the cube.
If you find it confusing to have two sort of “All” options in the parameter prompt, you can disable the all member in the hidden dataset by replacing the AllMembers function with the Children function. This will return all of the members of the dimension but exclude the all member.
The resulting dropdown:
The article Solving All Versus (Select All) SSRS End User Confusion gives a good overview of this solution.
For more information about parameters in general, please refer to the MSDN article Report Parameters (Report Builder and SSRS).
The second part of this article will go into more advanced topics, such as cascading parameters, dealing with multi-valued parameters, and using parameters in a drill-through report.