Problem
In your SQL Sever Reporting Services report, if you don’t display the parameter values which are used to filter the report data then it will be very difficult to the end users to find the parameter values against which the report was ran. This is especially the case when a report is exported to another format i.e. Excel or PDF. So it is always a good idea to display the parameter values in the report. This tip will demonstrate how to display a single value and multi valued report parameter selection values in SQL Server Reporting Services.
Solution
This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) Report with parameters. I have prepared this article in such a way that an SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.
Let’s understand the problem with a simple example
I have already created a data source connection to the AdventureworksDW2008R2 sample database. Let’s create the below three datasets for the sample report.
Report Dataset: This dataset will be used for the report body and it has two query parameters @ManufacturePeriod and @ProductKey. The @ManufacturePeriod query parameter can accept a single value and the @ProductKey query parameter can accept multiple values.
SELECT DaysToManufacture, Productkey, EnglishProductName FROM DimProduct WHERE DaysToManufacture =(@ManufacturePeriod) and ProductKey IN(@ProductKey) ORDER BY DaysToManufacture
ManufacturePeriod Dataset: This dataset will be used to get a list of values for the ManufacturePeriod report parameter.
SELECT Distinct DaysToManufacture FROM DimProduct ORDER BY DaysToManufacture
ProductKey Dataset: This dataset will be used to get a list of values for the ProductKey report parameter.
SELECT DISTINCT ProductKey FROM DimProduct ORDER BY ProductKey
Because the Report Dataset has two query parameters named as @ManufacturePeriod and @ProductKey, SSRS will automatically create two report parameters name as ManufacturePeriod and ProductKey. After creating all three datasets, your Report Data Pane must look like the below image.
Let’s configure both of the report parameters to get a list of parameter values.
Right click on the ManufacturePeriod report parameter and click on Parameter Properties, it will open Report Parameter Properties window. Please make the below changes in the properties of ManufacturePeriod report parameter.
Right click on the ProductKey report parameter and click on Parameter Properties, it will open Report Parameter Properties window. Please make the below changes in the properties of the ProductKey report parameter.
Let’s add a Tablix for data viewing purposes. Please pull all the data fields from Report Dataset into a Tablix. Your report must look like the below image after adding the Tablix.
Now if you will preview the report then it will not display selected parameter values. Please follow the below steps to display the parameter values in the report header.
Steps to display parameter values in an SSRS report header
Our sample report has two report parameters ManufacturePeriod and ProductKey. ManufacturePeriod is a single select and ProductKey is a multi select report parameter. I will update this sample report to display both parameters selection values in the report header.
1. Add the page header in the report.
2. Add two text boxes in the report header to display ManufacturePeriod report parameter value. In the first text box give a meaningful parameter caption for the ManufacturePeriod report parameter. Right click in the second text box and click on expression, it will open the expression window. You have to set the expression to display the ManufacturePeriod report parameter value. You can find the expression below.
3. Add two more text boxes in the report header to display the ProductKey report parameter values. In the first text box give a meaningful parameter caption for the ProductKey report parameter. Right click on the second text box and click on expression, it will open an expression window. You have to set the expression to display the ProductKey report parameter values. The ProductKey report parameter is a multi select report parameter, so I have used the Join SSRS function in the expression. It will display multiple selected parameter values using a comma separator. You can find the expression below.
4. Let’s preview the report. As you can see from the below image, the report displays the parameter selection values, but that’s not all.
5. If your report parameter has a long list of values and you preview the report for all the values then the report will display all parameter values using a comma separator. As you can see from the below image, the report shows all parameter values and it takes lots of report space. It will be better to display a value such as “All” when a report is run with all parameter values.
6. Let’s modify the text box expression which displays the parameter values for the ProductKey report parameter. We will display “All” if all the values are selected from the ProductKey report parameter. You can find the expression below.
7. We have made all necessary changes, let’s preview the report for all parameter values for the ProductKey report parameter. You can see from the below image, the report displays “All” when all parameter values are selected in the ProductKey report parameter.
Next Steps
- Check out all of my tips here.
- Check out other tips on SQL Server Reporting Services Parameters.
- Check out all of the SQL Server Reporting Services tips
