IIF statement and the Else clause

Question

IIF statement and the Else clause

mschaper

Ten Centuries

Points: 1120
More actions
May 31, 2017 at 1:16 pm

#323828

I'm trying to add a filter to my report. The report has a column with 3 values (Red / Green / Yellow). The report also has a parameter with 4 values (All / Red / Green / Yellow). The filter below works great if I choose Red / Green / Yellow and it produces the correct results, The problem I'm having is when I when I select All. In this case, I need Red / Green, and Yellow records. I've tried the following for the ELSE portion of this statement:
'*'
"*"
"" (2 double quotes)
"Red" & "Green" & "Yellow"
=iif(Parameters!vType.Value = "Red", "Red", iif(Parameters!vType.Value = "Green", "Green", iif(Parameters!vType.Value = "Yellow", "Yellow", '*')))
I haven't used filters very much so this one is a little more complex for me.
Any suggestions would be greatly appreciated. Thanks for the help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jonathan.crawford SSCertifiable Points: 6611 More actions · Answer 1

I think they're usually csv, have you tried:

=iif(Parameters!vType.Value = "Red", "Red", iif(Parameters!vType.Value = "Green", "Green", iif(Parameters!vType.Value = "Yellow", "Yellow", "Red,Green,Yellow")))

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

mschaper Ten Centuries Points: 1120 More actions · Answer 2

I just tried that and the report returned no records. Wouldn't "Red,Green,Yellow" return only those records that had a string of "Red,Green,Yellow" in that particular column? Thanks for the suggestion..........

mschaper Ten Centuries Points: 1120 More actions · Answer 3

I cameup with this. I thought this would give me all records when “All” isselected since the filter wouldn’t take effect and if it didn’t = “All” then itwould kick off the filter.
=iif(Parameters!vType.Value<> "All", iif(Parameters!vType.Value = "Red","Red", iif(Parameters!vType.Value = "Green","Green", "Yellow")))

I thought thiswould work but I’m getting this message: The FilterValue expression for the dataset "Dashboard_Test' contains an error: [BC30516] Overload resolution failed because no accessible 'Iif' accepts this number of arguments.

I’ve checkedcommas, brackets, etc., and don’t see anything wrong. Can anyone tell me what this message means? Thanks for the help.........

jonathan.crawford SSCertifiable Points: 6611 More actions · Answer 4

there's no false value at the end.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

mschaper Ten Centuries Points: 1120 More actions · Answer 5

Thanks..........I'll look into to that. I have a feeling that I'm going to end up with the same issue when I select All. Thanks for your help.......