SQL Server Reporting Services Reports With Optional Query Parameters

By:   |   Comments (30)   |   Related: > Reporting Services Parameters


Problem

You have a requirement where users want to run a SQL Server Reporting Services (SSRS) report without passing any parameter values. In this case the SSRS report should show all the records, but the users also want an option to filter the data, if required. In SSRS terms the users want an Optional Query Parameter; which filters the dataset if a value is provided. If a value is not provided, then the query will return all records. So how do you create an Optional Query Parameter in SQL Server Reporting Services?

Solution

In SQL Server Reporting Services (SSRS) we can't enable/disable an SSRS report parameter; if we have created a parameter then the user has to provide a value for the parameter. Optional Query Parameters are not available natively in SSRS, but we can create a workaround to create a report.

If a report has an Optional Query Parameter and the user doesn't provide a parameter value and then previews the report, the report will show all of the data. If the user wants to filter the data then the user can filter by entering the parameter value. Optional Parameters can use any operators such as "=", "<", ">", "<>", “IN”, “BETWEEN”, etc.

I have prepared this tip in such a way that an SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use the AdventureworksDW2008R2 database. You can download the database from codeplex.

Steps to create an SSRS Optional Query Parameter

Step 1 - Create an SSRS Data Source Connection

I am creating an embedded data source connection to the AdventureworksDW2008R2 database. You can refer to the below image to create a new data source connection.

Report Datasource Window

Report Datasource Connection Window

The Data Source connection is successfully created. You can check your new data source connection under Data Sources in the Report Data window.

Step 2 - Create an SSRS Dataset

I am creating a new Dataset; this dataset has one query parameter called @Product. This dataset returns the Productkey and EnglishProductName based on value supplied for the @Product query parameter.

SQL Server Reporting Services Dataset Query

Select ProductKey,ProductEnglishName from DimProduct where Productkey = @Product

You can refer to the image below to create a new dataset.

Report Main DataSet

The dataset is successfully created. The @Product report parameter has been automatically created by SSRS. The @Product parameter is not using any query for available values, so it will allow the user to enter the parameter value at report run time. You can refer to the below image.

Report Parameter

Step 3 - Add an SSRS Tablix for Data Validation

I am inserting a Tablix for data viewing and validation purposes. In the Tablix I have chosen both the data fields from the main dataset. You can refer to the image below.

Tablix in Report

If the report has a parameter then the user has to enter the parameter value to view the report.  If the user doesn't enter the parameter value and tries to view the report then the report will not run and will throw error. You can refer to the image below.

Missing Parameter Value Error Message

If the user wants to ignore this error then this parameter should be allowed to accept a blank value.

I am entering a parameter value and viewing the report. As you can see from the image below, our report is showing data for a supplied parameter value.

Report Output for Supplied Parameter Value

Step 4 - SSRS Optional Query Parameter Implementation

In this step we will configure the @Product parameter as an Optional Query parameter.

Right click on the @Product parameter and click on "Parameter Properties", the Report Parameter Properties window will open. You can refer to the image below.

@Product Parameter Properties

On the "General" tab, check on the "Allow null value" and "Allow blank value" options. This change will allow the user to pass a NULL value for the @Product parameter. Your changes should look like the image below.

Report Parameter Properties General Tab

Click on the "Default Values" tab and then select the "Specific Values" radio button. To add a default value click the "Add" button. It will add "NULL" as a default value for the @Product parameter. Your changes should look like the image below.

Report Parameter Properties Default Tab

Right click on "Main" Dataset and click on "Dataset Properties", it will open Dataset Properties window. We have to make the below changes in the Query.

SELECT     ProductKey, EnglishProductName
FROM    DimProduct
WHERE      ProductKey = @Product OR @Product IS NULL

You can refer to the image below.

Dataset Query Modification

We have made all necessary changes, let's preview the report. Once we clicked on the Preview button, the report started running automatically without asking for a parameter value, by default it is accepting NULL as parameter value because we added NULL as the default parameter value. You can see from the image below the report is returning all the records.

Report Preview for NULL

If the user wants to filter the data then they uncheck the NULL checkbox and then enter the parameter value. As you can see from the image below, the report is showing only records for the supplied parameter value. We have successfully implemented an Optional Query Parameter in SQL Server Reporting Services.  But is that it?  Can we do more to help our users?

Report Preview for Optional Parameter

Pass a Blank Parameter in SQL Server Reporting Services

Many times a user doesn't want to use the NULL check box, because they have to check and uncheck the NULL checkbox to pass the parameter value which wastes time.  The scenario our users want is if they do not pass a parameter then the report should return all the records and if a user enters a parameter then filter the data.

This report also accepts a blank value for the @Product parameter, now it will not throw an error if the parameter value is not provided. You can refer to image below.

Report accepts blank value

Let's implement the solution for the above requirement. This can be easily achieved, by following these steps:

  • Unselect "Allow null value" for @Product parameter. You can refer Step 4.
  • Right click on "Main" dataset and click on Dataset Properties then click on expression button under Parameters Tab. Refer to the image below.


Parameter expression

In the expression window write the following expression.

=IIF(Parameters!Product.Value ="",Nothing,Parameters!Product.Value)

You can refer to the image below.

Parameter expression window

Let's run the report without the parameter value. As you can see from the image below the report returns all records.

Report Preview for blank Optional Parameter

Let's provide one parameter value and preview the report. As you can see from the image below the report is showing data for the supplied parameter value.

Report Preview Optional Parameter
Next Steps
  • Install AdventureworksDW2008R2 database.
  • Try creating Optional Query Parameter for other operators i.e. "<" or ">".
  • Try creating Optional Query parameter for multiple values.
  • Check out other tips on Parameters.
  • Check out all of the SQL Server Reporting Services tips


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



Comments For This Article




Thursday, July 13, 2023 - 9:20:06 AM - Durga Back To Top (91382)
How to pass optional multivalue parameter, if used same expression it's not working, can you please share

Thursday, May 20, 2021 - 11:14:40 PM - owen Back To Top (88725)
Thanks! That really helps!

Thursday, February 18, 2021 - 2:32:24 AM - Gowtham Back To Top (88258)
Hi Ghanesh, I am trying to pass the blank value in ssrs report parameter in multi value quary parameter. is it is possible?

Friday, June 5, 2020 - 11:38:02 PM - JohnH Back To Top (85849)

I have been searching for possibly a whole day for an article that properly explains optional parameters and you nailed it on the head.  Thank-you very much Ghanesh! 


Tuesday, December 17, 2019 - 10:10:11 AM - Bo Back To Top (83449)

Ghanesh, thank you for the solution. 

Ryan thank you for the resolution on multiple parameters issue. I think it should be:

where (field1 = @param1 or @param1 is null)

and (field2 = @param2 or @param2 is null)

instead of

where field1 = (@param1 or @param1 is null)

and field2 = (@param2 or @param2 is null)


Wednesday, September 4, 2019 - 10:07:16 AM - Rohit Back To Top (82240)

Hi Ganesh,

Can we make cascading parameters optional ?

Ex ; Country, States, Capital
If user selects country from drop down then can also view report without having to select states and so on.

Regards,
Rohit


Thursday, March 14, 2019 - 9:56:10 AM - Shraddha Popat Kharmale Back To Top (79290)

 How to create a prompt with properties like it can take user value or select values from list in ssrs.


Friday, March 8, 2019 - 8:55:46 AM - ALEX YUKHVIDIN Back To Top (79215)

Ghanesh,

Very helpful. Pleas keep going.

thank you,

Al.


Wednesday, November 21, 2018 - 1:11:20 PM - David Coleson Back To Top (78304)

 My parms are begindate, enddate and agency

When running with begin and end date , agency value = NULL, the report works fine

When running with date range and agency value the report retuns the selected agency, however the date range parameters are being ignored. Can you suggest how to corrct the problem please? Thanks

IADDS.[SHP#10]
WHERE  
  IADSC.[COS#01]   =     (@AgencyParm) or @AgencyParm is NULL AND 
  IADSS.[ETSD21]     >=  @BeginDate AND IADSS.[ETSD21] <=@EndDate AND
  IADSS.[ETSD21]     <>  0    
AND
   NOT EXISTS
       (
         SELECT 1
         FROM [ISERIES].[AS400].[DMDTALIB].[SRPFBYPD] IADSB
         WHERE IADSC.[COS#01] = IADSB.[DNR#89]
       )


Tuesday, November 20, 2018 - 6:22:39 PM - Derek Trujillo Back To Top (78300)

Would you happen to know how to get this same functionality leveraging an SSAS tabular model as a source?


Saturday, March 31, 2018 - 11:33:24 PM - J.vijayavardhan Back To Top (75570)

i have millions of rows of data in my sql table now in my ssrs report i had created start date,end date, start time, end time input parameters i had created but along with this i want time interval parameter eg: if i give 1 minitue interval my report output has to generate ever next 1 minitue value in my sql table value so what query i have to write plz help me


Thursday, December 14, 2017 - 7:45:18 AM - DYS Back To Top (74002)

Now, what if that parameter loads a list or preset values from a dataset ? I mean, if the user cant manually set a value but choose one from a list, how do you enable a NULL there?

 


Monday, August 14, 2017 - 7:42:36 PM - Perri Spiteri Back To Top (64834)

Hi Ghanesh, Great Post thank you!  

I can get this to work great for a report with only one parameter, however I have a report with multiple parameters to be applied.  I was wondering if you can help.  I need users to select Site and Date Range etc. values to always be applied to the report, but then have Sales Rep as optional.  At the moment my report filters fine if a Rep ID is entered (only bring back data for that rep) but if i blank it out, the report tries to filter all data ignoring date and site etc.

SELECT

       rptVW_GP_RMTransactionHeader.SalesTerritory

       ,rptVW_GP_SalesTerritories.SalesSite

       ,rptVW_GP_SalesTerritories.WMSInstance

       ,rptVW_GP_RMTransactionHeader.SalesAmount

       ,gpVWDebtors.AccountType

       ,gpVWDebtors.CUSTNMBR

       ,gpVWDebtors.CUSTNAME

       ,rptVW_GP_RMTransactionHeader.CostAmount

       ,rptVW_GP_RMTransactionHeader.DocumentNumber

       ,rptVW_GP_RMTransactionHeader.DocumentType

       ,rptVW_GP_RMTransactionHeader.SalesPersonID

       ,rptVW_GP_RMTransactionHeader.GLPostedDate

           ,CASE WHEN rptVW_GP_RMTransactionHeader.SalesTerritory = 'IK' THEN 'FFE' ELSE rptVW_GP_RMTransactionHeader.SalesTerritory END AS 'prSalesTerritory'

           ,CASE WHEN rptVW_GP_RMTransactionHeader.SalesTerritory = 'HP' THEN rptVW_GP_RMTransactionHeader.CostAmount+(rptVW_GP_RMTransactionHeader.CostAmount *  

0.066) ELSE rptVW_GP_RMTransactionHeader.CostAmount+(rptVW_GP_RMTransactionHeader.CostAmount * 0.011) END AS 'CostRecovery6_6'

           ,CASE WHEN rptVW_GP_RMTransactionHeader.SalesTerritory = 'HP' THEN rptVW_GP_RMTransactionHeader.CostAmount+(rptVW_GP_RMTransactionHeader.CostAmount *  

0.088) ELSE rptVW_GP_RMTransactionHeader.CostAmount+(rptVW_GP_RMTransactionHeader.CostAmount * 0.033) END AS 'CostRecovery8_8'

           ,CASE WHEN rptVW_GP_RMTransactionHeader.SalesTerritory = 'HP' THEN rptVW_GP_RMTransactionHeader.CostAmount+(rptVW_GP_RMTransactionHeader.CostAmount *  

0.099) ELSE rptVW_GP_RMTransactionHeader.CostAmount+(rptVW_GP_RMTransactionHeader.CostAmount * 0.044) END AS 'CostRecovery9_9'

 

FROM rptVW_GP_RMTransactionHeader

INNER JOIN rptVW_GP_SalesTerritories ON rptVW_GP_RMTransactionHeader.SalesTerritory = rptVW_GP_SalesTerritories.SalesTerritory

INNER JOIN gpVWDebtors ON rptVW_GP_RMTransactionHeader.CustomerNumber = gpVWDebtors.CUSTNMBR

 

WHERE 

         rptVW_GP_RMTransactionHeader.GLPostedDate BETWEEN @GLPostedDate AND @GLPostedDate2 

AND rptVW_GP_SalesTerritories.WMSInstance IN (@SalesSite) 

AND rptVW_GP_RMTransactionHeader.SalesTerritory IN (@SalesTerritory) 

AND rptVW_GP_RMTransactionHeader.SalesPersonID = @SalesRep OR @SalesRep IS NULL

 

 

Any help is much appreciated


Sunday, August 13, 2017 - 7:21:34 AM - Eylon Meroz Back To Top (64785)

 

Hello Ghanesh,

Good tip...but of course this means that after implementing it I cannot use the visual query designer anymore.  Or - have I missed something?

 

Eylon

 

 

 

 


Friday, June 23, 2017 - 4:05:51 AM - robert Back To Top (58001)

 Thanks a lot, Ghanesh. Your hint was very helpful for me.

Have a nive day

Robert

 


Thursday, April 13, 2017 - 5:01:05 AM - Gergo Back To Top (54791)

It was really helpful and easy to read, thanks! :)


Monday, February 13, 2017 - 3:07:08 AM - Sreekul Nair Back To Top (46264)

Hi Ghanesh,

Nice blog and it helped a lot many of us. However, I was just wondering if you have any way in which you can allow multiple comma separated values as well here. For ex. the product key where you entered 310, you should be able to enter say 1,2,3,310,450 and so on. 

How would you proceed to achieve the above.

Regards,

Sree


Sunday, October 30, 2016 - 8:54:20 PM - Kathy Back To Top (43658)

This is awesome - however.....   I have 2005 version (aargh).  I have a report with multiple parameters and each one of these could potentially have a null value.   Using your method has allowed me to select which parameters I want once the report has run initially.   How do I prevent this inital run of the report with all the parameters being null/blank.    This report has a lot of data so I do not want it to auto run prior to the selecting of parameters


Tuesday, October 18, 2016 - 12:02:38 PM - Krish Back To Top (43587)

In SSRS

I am trying to display all the records with any status. But when i run the report it is excluding the records with blank status.

How can i pull the records with any status(blank,NULL,Approved,etc..) ?

Label: Status

Value: %

 

Also enabled NULL and Blank values in the parameter properties.


Monday, October 17, 2016 - 6:07:12 AM - King George Back To Top (43574)

 Please explain for date report parameters scenario how nul values and blank values can be passed.

 


Tuesday, March 22, 2016 - 6:11:54 AM - Ankita Back To Top (41025)

Hi Ghanesh,

The query is very nicely explianed, however I did the same steps. Not by wqititng codes but using SQL asset builder, I am unable to get the results.

 

I have a report which has to be viewed either by UserId or Project Id

when a user selects Report by user id, the project id drop box should show up as Not applicalbe and bvice a versa.

 

Can you please help me on this

 

Regards,

Ankita Parmar

 

 


Wednesday, November 4, 2015 - 10:35:43 AM - Dustin Harding Back To Top (39018)

Thanks!!!


Friday, July 10, 2015 - 4:29:37 PM - Prakash Back To Top (38177)

Thanks for tips,its helps me lot. But i have a question on this.

 

Is this possible to use Allow Blank Values & Allow Multiple Values in same time.


Thursday, July 2, 2015 - 12:46:30 PM - Mahesh Back To Top (38118)

 

Hello Ganesh

Could you help me out with below sceanrio 

SSRS 2012 Multi value Parameter not to be mandatory while running the report

 

I have to run the report from share point even though the Multi value parameter  are not selected as shown below. Below are the 2 screen shots highlighting the scenario.

Lets call the paramter as Include

Label Value 

A          1
B          2
C          3 
 in the preview  we will get
A
B
C
Now the end user has to  without selecting any of those parameters A,B and C and the report must run
Also if the end usre wants to select the either A or B or C or cobination of any 3 then report must run and there are vivibility conditions for A, B and C as Column grouping and based on that data will get displayed on the report.

The end user can run with out selecting 1,2 and 3  or the user can run with all the 3 selected. If nothing is selected also the report must run 

There are default integer  values passed like 1,2 and 3 and if selected based on the visibility condition the value gets displayed

Currently when we run the parameter displays it cannot be blank. and there are currenlt no dataset for the below  values .

Any solutions please

Thanks

Mahesh


Thursday, June 11, 2015 - 9:39:45 AM - Ghanesh Back To Top (37895)

Hello Ryan,

Great to hear that you solved yourself problem!

My recommendation is always use opening and closing parenthesis when using logical operators in query.

I really appreciate your compliment.

Thank you.

 

Ghanesh


Thursday, June 11, 2015 - 1:54:07 AM - Ryan Back To Top (37893)

I actually solved this one myself. All I needed were some extra brackets

e.g.

 

where field1 = (@param1 or @param1 is null)

and field2 = (@param2 or @param2 is null)

 

Bloody brackets. With only one parameter, no brackets were needed.


Wednesday, June 10, 2015 - 11:44:33 PM - Ryan Back To Top (37890)

This worked perfectly for my report. Brilliant explanation.

How can I achieve this method with two or more parameters passing blank values? My parameters do not rely on a query to obtain the values/input.

I have tried adding another line in my SQL for my main dataset but the report then only works with the second parameter, not the first.

 

e.g. code from my main dataset **I have entered an expression for my second parameter the same as the first**

select ref_num, preference_number, course_code, course_short_name, offer
from #temp_pref
where course_code = @course_code or @course_code is null
and ref_num = @ref_num or @ref_num is null


Wednesday, May 27, 2015 - 4:50:13 AM - bafrani Back To Top (37283)

 

very very good.

thanks. thanks


Thursday, March 26, 2015 - 12:39:23 PM - Kris Back To Top (36720)

Awesome.

I created each step and enjoyed.

Thanks for educating the community and appreciate your volunteer-ship.


Tuesday, February 17, 2015 - 4:10:24 AM - Narasinha Kamath Back To Top (36260)

Nice Article :) Useful Info















get free sql tips
agree to terms