CODEWITHSUNDEEP
reporting-servicesssrs-2008-r2ssrs-2012reportbuilder3.0
a415
a415

Reputation: 369

SSRS adding '' value as a parameter

I am trying to add '' as an optional parameter for the user to select within the dataset that I am using for the parameter in SSRS.

Within the dataset I have 

SELECT '' as user 
UNION 
SELECT user 
FROM users

This works fine.

However, when I select available values for the parameter using this dataset, I do not '' as a option. I see a select all, but I would imagine that only selects from the values listed.

Is this something that is possible to do within SSRS. Is there an alternative way for me to get all values including the blank

Upvotes: 0

Views: 227

Answers (1)

3N1GM4
3N1GM4

Reputation: 3351

If you really want a parameter value which appears to be empty, try specifying a space instead:

SELECT ' ' AS user UNION SELECT user from users

It's the underlying parameter value which cannot be an empty string, so you can even have a true empty string displayed in the dropdown, as long as the value of the parameter is not an empty string:

SELECT ' ' AS user, '' AS user_desc UNION SELECT user, user_desc from users

or

SELECT 'Blank User' AS user, '' AS user_desc UNION SELECT user, user_desc from users

Although this probably doesn't help you much.

As Mike D. suggests in the comments, it's not good practice to have an entirely empty option in a parameter though, so you might want to consider a more meaningful text description here anyway.

If this answer doesn't solve your problem, it would be helpful to know why you want to have an empty string as a parameter value?

Upvotes: 1

Related Questions