Learn more about SQL Server tools

 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Reports With Optional Query Parameters


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


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     



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

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


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

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

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

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

 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

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 04, 2015 - 10:35:43 AM - Dustin Harding Back To Top

Thanks!!!


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

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 02, 2015 - 12:46:30 PM - Mahesh Back To Top

 

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

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

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

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

 

very very good.

thanks. thanks


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

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

Nice Article :) Useful Info


Learn more about SQL Server tools