How to implement optional parameter in SSRS

There is a stored procedure called GetClients that returns a list of all client records. GetClients is called by other applications in the organisation (cannot be changed). The report is to display data for either: All clients if the user does not select a client, or The selected client.

I have created a dataset that calls the GetClients stored procedure. I have created a report parameter @Client whose Available Values are obtained from the dataset.

But when I run the report, SSRS does not allow the @Client parameter to be not selected. It displays a message saying "Please enter a value for the parameter 'Client'" It does this even when I have Allow NULL values and/or Allow blank values.

How can I resolve this?

more ▼

asked Jan 28, 2011 at 04:26 AM in Default

avatar image

905 60 64 68

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It seems like you have a multi select parameter. In such case it cannot be null or blank. In this case you should add a special item for eg. All. Or do not use multi selection param if you do not need it.

more ▼

answered Jan 28, 2011 at 04:50 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

@Pavel Pawlowski: No, it is not a multi-select parameter, just a single client can be selected. As I said above "The report is to display data for EITHER: All clients ... OR the selected client."

Jan 28, 2011 at 05:05 AM xnl28

Then if you set Allow NULL, then a checkbox NULL checkbox should be next to the parameter. If you check the NULL value checkbox, you should pass NULL value into the parameter.

Jan 28, 2011 at 05:23 AM Pavel Pawlowski

@Pavel Pawlowski: That doesn't work. I have ticked the "Allow null value", but when viewing the report, this NULL checkbox next to the parameter that you describe is not there. SSRS forces a value to be selected for the parameter.

Feb 02, 2011 at 06:33 AM xnl28
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 28, 2011 at 04:26 AM

Seen: 3645 times

Last Updated: Jan 28, 2011 at 04:26 AM

Copyright 2018 Redgate Software. Privacy Policy