SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS expression switch/Else if


SSRS expression switch/Else if

Author
Message
avitale
avitale
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 117
So i've been messing around with this all morning and i just can't quite figure it out. I have a parameter that can be one of two values. Either I or P.

So i need the title on the report to say one thing when I is chosen, one thing when P is chosen, and one thing when both are chosen (this is a multi select parameter)

This is what i currently have but it keeps coming up with #Error.

=Switch(Parameters!clmType.Value = "P","List of Professional Paper Claims ", Parameters!clmType.Value = "I","List of Institutional Paper Claims ", Parameters!clmType.Count > 1 ,"List of Professional & Institutional Paper Claims ")

I've also tried this

=Switch(Parameters!clmType.Value = "P","List of Professional Paper Claims ", Parameters!clmType.Value = "I","List of Institutional Paper Claims ", Parameters!clmType.Value = "P" and Parameters!clmType.Value = "I" ,"List of Professional & Institutional Paper Claims ")

Thanks!!

EDIT: Also would using a nested IIF work? not sure what that would look like, or the order of it.

Tried using this as a Nested IIF and still getting a #Error
=IIF(Parameters!clmType.Count > 1,"List of Professional & Institutional Paper Claims ", IIF(Parameters!clmType.Value = "P","List of Professional Paper Claims ","List of Institutional Paper Claims "))

I know it partially works because i used this and it worked as expected
=IIF(Parameters!clmType.Count > 1,"List of Professional & Institutional Paper Claims ", "only one picked")
Daniel Bowlin
Daniel Bowlin
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17374 Visits: 2629
avitale (7/20/2011)
So i've been messing around with this all morning and i just can't quite figure it out. I have a parameter that can be one of two values. Either I or P.

So i need the title on the report to say one thing when I is chosen, one thing when P is chosen, and one thing when both are chosen (this is a multi select parameter)

This is what i currently have but it keeps coming up with #Error.

=Switch(Parameters!clmType.Value = "P","List of Professional Paper Claims ", Parameters!clmType.Value = "I","List of Institutional Paper Claims ", Parameters!clmType.Count > 1 ,"List of Professional & Institutional Paper Claims ")

I've also tried this

=Switch(Parameters!clmType.Value = "P","List of Professional Paper Claims ", Parameters!clmType.Value = "I","List of Institutional Paper Claims ", Parameters!clmType.Value = "P" and Parameters!clmType.Value = "I" ,"List of Professional & Institutional Paper Claims ")

Thanks!!

EDIT: Also would using a nested IIF work? not sure what that would look like, or the order of it.


Try this variation of your first expression:
=Switch(Parameters!clmType.Value = "P","List of Professional Paper Claims ", Parameters!clmType.Value = "I","List of Institutional Paper Claims ", True,"List of Professional & Institutional Paper Claims ")
Daniel Bowlin
Daniel Bowlin
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17374 Visits: 2629
Try this variation of your first expression:

=Switch(Parameters!clmType.Value = "P","List of Professional Paper Claims ", Parameters!clmType.Value = "I","List of Institutional Paper Claims ", True,"List of Professional & Institutional Paper Claims ")
avitale
avitale
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 117
Daniel, i'm still getting an error using your code

Just FYI, this is the error i'm getting:

Overload resolution failed because no Public '=' can be called with these arguments:
'Public Shared Operator =(a As String, b As String) As Boolean':
Argument matching parameter 'a' cannot convert from 'Object()' to 'String'.
Daniel Bowlin
Daniel Bowlin
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17374 Visits: 2629
Sorry I forgot this is a multi value parameter, they don't hold data the same as a single value parameter, so these comparisons will fail.

I can't really test this, but I think you need something like this:
=IIF(InStr(JOIN(Parameters!clmType.Value,","),"P") > 0,IIF(InStr(JOIN(Parameters!clmType.Value,","),"I") > 0,"List of Professional & Institutional Paper Claims ", "List of Professional Paper Claims "),"List of Institutional Paper Claims ")

Doulbe check I got the ( and the ) in the right places and the right quantity, it is hard to do this without the expression editor.
avitale
avitale
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 117
This works perfectly! thanks so much!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search