SSRS expression switch/Else if

• avitale

  SSC Eights!

  Points: 915

  More actions

  July 20, 2011 at 7:07 am

  #244679

  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

  SSC-Dedicated

  Points: 34566

  More actions

  July 20, 2011 at 7:25 am

  #1355582

  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

  SSC-Dedicated

  Points: 34566

  More actions

  July 20, 2011 at 7:26 am

  #1355583

  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

  SSC Eights!

  Points: 915

  More actions

  July 20, 2011 at 7:28 am

  #1355585

  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

  SSC-Dedicated

  Points: 34566

  More actions

  July 20, 2011 at 8:23 am

  #1355663

  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

  SSC Eights!

  Points: 915

  More actions

  July 20, 2011 at 8:44 am

  #1355697

  This works perfectly! thanks so much!!!