Include Report Parameter Selection Values in Report Output for SQL Server Reporting Services

By:   |   Updated: 2015-06-11   |   Comments (13)   |   Related: > Reporting Services Parameters


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.

Report Data Pane Window

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.

ManufacturePeriod Report Parameter Properties Window

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.

ProductKey Report Parameter Properties Window

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.

Adding 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.

ManufacturePeriod Report Parameter Expression

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.

ProductKey Report Parameter Expression

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.

Report Preview

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.

Report Preview with Select All Problem

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.

ProductKey Report Preview SelectAllExpressi on

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.

Report Preview SelectAll Soution
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2015-06-11

Comments For This Article




Tuesday, December 26, 2023 - 7:37:51 PM - bob Back To Top (91816)
Your advice worked! I simply wanted to add the notification to the header if all parameters were selected (Y, N, or Y&N) regarding a certain field. A little tinkering got me there.
=join(Parameters!CaresParm.Value,"&") Where the parameter CaresParm could be Y, N or the customer could select all.
When all is selected Y&N is displayed on the report, as the customer requested.
It took a while to find it, but yours was the solution best suited to my need. Thank you so much.

Wednesday, February 23, 2022 - 7:23:13 AM - Beeresh M P Back To Top (89825)
For the Steps which u have followed in Step 6 and step 7 is not working for me so could u please help me out?

Wednesday, August 8, 2018 - 7:43:11 AM - Rajni Reddy Back To Top (77074)

 

 

Code is good but i need somthing more in reports like how to call stored procedure in report parameter then RDLC report will display.

Will you help me?

Monday, May 28, 2018 - 2:47:42 AM - Balaji Back To Top (76032)

This is working fine in a report that has MDX as source query. But failed when the data sets are created using DAX. Any resolution for this? 

 

Tuesday, March 27, 2018 - 2:06:06 PM - Ajit Warrier Back To Top (75529)

I have one parameter for which the user can select multiple values, say "regions". If the user selects all the regions, I want the next (cascading) parameter, say "states" to use those values. However the list of regions is long, if all are selected, which crosses the maximum number of values allowed. So, in the dataset for "states", I want to change the query from "select distinct stateid from locations where region in (@region)" to "select distinct stateid from locations", since the "regions" parameter's value is not needed because all are selected.p>

How can I do this? Is there an equivalent to the ”if the number of rows in the dataset = count of the Parameters” that can be used in the query for the dataset

Monday, February 12, 2018 - 12:22:44 AM - Greta Young Back To Top (75177)

Hi,

 

I need to check within the Reportdata Query if select ALL  has been selected for a multivalue parameter. Is this possible? All the information I have found so far does not work within the ReportData Query.

 

Thanks!

 

Monday, September 25, 2017 - 10:23:27 AM - Randy Back To Top (66593)

 My parameters are text.  On the initial report I am selecting all parameters, but on rendering the report - there is an error.  What is the workaround for this?

 

Sunday, February 14, 2016 - 11:45:46 PM - Susan Mitchell Back To Top (40679)

Thank you very much for posting this.

Very concise and more importantly... worked perfectly for my report.

It was the "All" solution that I had wanted

 

Sue :)

 

 

Thursday, August 13, 2015 - 2:41:29 PM - Zach Back To Top (38443)

Do you know if there is a way to select and print a value that wasn't selected by the user? I have a list of states and it is pretty common for a user to select all of the states except one. I would like to print "Companywide less 'State'" instead of joining all of the selections.

Wednesday, June 17, 2015 - 1:24:53 PM - Jorge Back To Top (37943)

Ganesh,

Thank you so much for your reply !!!

In the store procedure I have:

  @Parm_list AS VARCHAR(400)

This is to allow in case the user selects ALL. There are 75 Values.

So 75 X 4 = 300 + 75 commas = 375


-- The parm gets its values  from this query --

SELECT 
  DMGCV.ValueID,   -------------     In the table varchar 4
  DMGCV.Name
 
FROM MyValueTable AS DMGCV 
WHERE 
      AND DMGCV.Code ='01'     
      AND DMGCV.Active = 'Y'
ORDER BY DMGCV.Name

Sample data
-------------
Value   Description
3115 Descr1
3112 Descr2
3142 Descr3


In the MAIN SP the selection looks like this :

 WHERE  
    BCT.Amount <> 0
   --AND BCT.Corporation + SUBSTRING(BCT.ProcedureChargeDept,3,5) IN (SELECT Field FROM #ValuesTable)
   AND BCT.CorpValue IN  (@Parm_list)


IF ONLY ONCE Value is selected IT WORKS  but if it s 2 or ALL it does not !!

 

THANKS AGAIN !!!!

 

Jorge,

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Friday, June 12, 2015 - 6:04:26 AM - Ghanesh Back To Top (37905)

Hello Jorge,

Can you Please check if your database column data type and parameter data type are same? if no then please make sure both are same.

Ghanesh

 

Thursday, June 11, 2015 - 12:35:47 PM - Kris Maly Back To Top (37900)

I followed all the steps explained and created the report and it is working fine.

I would recommend others to read this article.

This is one of the important in Report Developers day to day work.

 

Thanks for educating the community and appreciate your volunteership.

 

Pl;ease keep publishing such articles.

 

Thanks a bunch

Thursday, June 11, 2015 - 11:08:25 AM - Jorge Back To Top (37898)

Ghanesh, 

 

Great tip.

 

However my parameter tpe is character so the selection filter
WHERE  BCT.Code  IN ( @Parm_list )  did not worked since I believe the values are coming in comma separated BUT without quotes.

 

Is there a flag somewhere in SSRS to tell it to include the quotes ?

 

Thanks,

 

Jorge,

 

 

 

 

 

 

 

 














get free sql tips
agree to terms