I have multiple parameters for a report in SSRS Report Builder. I want all the parameters to allow blank values if no selection is necessary, but when I try to run the Report, the first parameter will allow a null/blank value and it will prompt me for the rest of the parameters. Is there any way around this?
Asked
Active
Viewed 2.4k times
6
-
Did you set `Allow Blank Value` & `Allow Null Value` for all the parameteres? – Anup Agrawal Jul 09 '14 at 16:05
-
1Yes, I have that set for all parameters. – user3338734 Jul 09 '14 at 16:12
-
Two of the parameters are specified Available Values and two parameters are Date Ranges. It will pass the first blank value, but will prompt me for the second parameter and will not allow me to go on without selecting a parameter. – user3338734 Jul 09 '14 at 16:13
-
In the `Available Values` make sure to add Blank and/or NULL values. – Anup Agrawal Jul 09 '14 at 16:22
-
1If `Available Values` comes from query `UNION` it to bring Blank and/or NULL values. – Anup Agrawal Jul 09 '14 at 16:23
-
Ok, How would I do that? Could you possibly provide me with an example? – user3338734 Jul 09 '14 at 16:30
-
See [this answer](https://stackoverflow.com/a/49786027/7794769) for a possible explanation\solution. – stomy Apr 12 '18 at 16:50
1 Answers
1
Set
Allow Blank Value(and/orAllow NULL Values) for all the parameteres.
If the
Available valuesare set, make sure youravailable valuesfor the parameter brings Blank(and/or NULL) values.If it comes from query. Update your query something like this:
SELECT FieldX FROM Table1 WHERE blah=blah UNION
SELECT '' UNION
SELECT NULLIf it is specify values then specify Blank and NULL values
Set the
Default valuesto pull the blank(OR NULL) depending on your requirement. For Blank leave the value as empty.
Final thing your query which produces the data can handle Blank(and/or NULL) values.
Anup Agrawal
- 6,551
- 1
- 26
- 32
-
3You cannot check both "Allow null value" and "Allow multiple values" or you will get the error "A multi-value parameter cannot include null values". – skeletank Mar 28 '17 at 13:44