Optional Parameters in SSRS

Home > Blogs > SQL Server > Optional Parameters in SSRS

Optional Parameters in SSRS

Like This Blog 4Peter Avila
Added by July 27, 2015

Let’s say you have a report, like a sales report that shows sales orders, and some people who run that report need it to show only the one sales order that they specify while others need to see all sales orders. In this article, I’ll show you how to create an optional parameter that allows you to implement this type of flexibility.

To accomplish this, two things need to be done. The first is to create an optional parameter, or a parameter that does not stop SSRS from running the report if no value for it is supplied. The second is to tell the report to select all the data when a value for the parameter is not supplied and to select only some of the data when a value is supplied.

Create an Optional Parameter

  1. With the report open and the Design tab selected, right-click the Parameters folder in the Report Data pane and select Add Parameter…

Add a Parameter in SSRS SQL Server Reporting Services

  1. In the Report Parameter Properties window, make the following entries and selections. Notice that Allow null value is checked.
You may also like:  Microsoft White Paper - SQL Server 2016 and Windows Server 2016 Better Together

Allow Null values feature in in SSRS SQL Server Reporting Services

When the parameter does not allow nulls, SSRS will not run the report if a value for the parameter is not supplied. By telling the report to allow nulls, SSRS will run the report regardless.

  1. Click OK when done.

The next step is to wire up the parameter to the data selection process of the report and to tell the report what to do if no value is supplied.

Configure the Report for the Optional Parameter

Our parameter will be used in the WHERE clause of the query in the dataset, though the same approach applies if you use it in a filter. (I’m putting the query directly in the dataset of the report to simplify this article, but you should always consider using stored procedures when developing reports; among the many benefits that stored procedures offer are that they are faster and keep data access organized and manageable. The same strategy shown in this article can be applied in a stored procedure.)

  1. Right-click the dataset in the Report Data pane and select Dataset Properties.
You may also like:  Microsoft White Paper - SQL Server 2016 and Windows Server 2016 Better Together

Selecting Dataset property in SSRS SQL Server Reporting Services

  1. Add a WHERE clause to the query that restricts the data in the report to the value of the parameter. Include an OR clause that returns True if the parameter is null; that way, the WHERE clause will be True for every row of the query when the parameter is not used.

Adding a WHERE Claues in in SSRS SQL Server Reporting Services

Now, when you run your report, you can either supply a value for the parameter or just ignore it and the report will know what to do in each case.

Enjoy!

Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

Agile Methodology in Project Management

0 56 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

JavaScript for C# Developers – September 24, 2014

0 196 1

In this video Dan Wahlin demonstrate the similarities and differences between C# and JavaScript and the future of JavaScript (ES6).

Detailed Forensic Investigation of Malware Infections – April 21, 2015

2 191 1

In this IT Security training video, Security expert Mike Danseglio (CISSP / CEH) will perform several malware investigations including rootkits, botnets, viruses, and browser toolbars.

Write a Comment

See what people are saying...

  1. Chris Razzano

    Hi Peter, thanks for the above info. I’m using SSRS for labels, and my predecessor made a long list of parameters so that testing was easy. However, when making the call to use the label, it means there is a long, redundant parameter list. Using the above technique coupled with default values, can you still make the call and simply leave out the parameters that have default values? For example, the old call looked like this:
    ProdTextf1=50, ProtTextf2= 100, ProdTextf3=150…
    Can you simply leave out those parameters if they have default values?

  2. Cathy Rudder

    I use your blog regularly with positive results. Thanks Peter! You ROCK!

  3. Priya T

    Hi, I tried this steps but unfortunately do not work on SSRS 2013. Kindly guide what is wrong.
    I created a parameter which gets value from a query. I want this parameter to be optional, hence I specified “Allow null value” and also in DataSet SQL in the where clause gave the condition as parameter can be null.
    Still while I run the report it prompts me to select value.

  4. Bob

    Thank you, the simple syntax fixed the error I was having in passing multiple parameters in a report. Such a simple solution to a problem.

Share your thoughts...

Please fill out the comment form below to post a reply.