SSRS adding '' value as a parameter

Question

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

Seeing an empty selection value in a report parameter selection list sounds like it would be confusing. I would try to put a meaningful label like "<Empty>" or "N/A" instead. But the search (and even the actual value of the parameter) would still look for an empty string. — Mike D.

3N1GM4 3N1GM4 · Accepted Answer · 2016-12-06T09:01:36

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?

SSRS adding '' value as a parameter

1 Answers