SSRS Parameter show 1 and 0

Question

Using SQL Server 2016 & SSRS. I may be missing something simple here, but I have a field (called Errors) in a report that has an Integer value of either 0 or 1.

I've added a parameter to my SSRS report called Errors and added 3 options:

1 = show error
0 = no error
1 or 0 = both

The Both option is not working - it doesn't return any records. I must be missing something. In Available Values I have a Label called 'Both'. On the value I enter the expression:

= 1 OR 0

What am I missing?

Seems like it's working perfectly. Options are not code, and "1 or 0" != 0 — JeffUK
"1 or 0" seems strange, I'd recommend changing it to "-1" or "2" — bastos.sergio
But the error field is an INT and can only ever be 0 or 1. I don't understand -1 or 2? How do I show records with both in SSRS? — Michael
I think you need to change the parameter to multiple-choice with just 1 and 0 as options, then lookup filtering on a multiple-choice parameter.. sorry been a while since I used ssrs but that should get you started — JeffUK
Are you using the parameter to filter the data already queried or are you using the parameter in the query? — Frank Ball

Hannover Fist Hannover Fist · Accepted Answer · 2019-01-11T19:49:36

The parameter doesn't work the way you want it to.

The 1 or 0 value is probably being evaluated as a text value.

My suggestion is to use -1 for both if it can't be in the data and change your parameter to an integer.

If you are using the parameter in a query, you add an OR to check for your both selection (-1).

WHERE (ERROR_FIELD = @ERRORS OR @ERRORS = -1)

This will check to see if the field matches the parameter or if the parameter = -1.

If you are using the FILTER on the dataset or table,

=IIF(Fields!ERROR_FIELD.Value = Parameters!ERRORS.Value OR Parameters!ERRORS.Value = -1, 1 , 0)

And set the Type to Integer and the value to 1.

SSRS Parameter show 1 and 0

2 Answers