Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (6)   |   Related Tips: > 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


Last Update:






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.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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

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

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

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

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

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,

 

 

 

 

 

 

 

 


Learn more about SQL Server tools