locked
Conditional Formatting with a range rather than 1 value RRS feed

  • Question

  • Hey guys,

    I'm aware of the switch function, as explained here: http://weblogs.sqlteam.com/joew/archive/2008/08/20/60693.aspx and I've used this a few times.

    Now I'm trying to achieve the same thing but with a range... along these lines:

    If VALUE = 0 THEN White

    IF Value between 0.01 and 0.03 then Yellow

    IF Value between 0.04 and 0.07 then Orange etc

    I think you can see what I'm getting at?

    I basically want to evaluate the values in a column against 5 categories:

    = 0.00

    +/- 0.01-0.03

    +/- 0.04-0.20

    +/- 0.21-0.99

    +/- 1.00+

     

    I'm struggling to see how I can do this with Switch. Can I? Do I need something else?

    Any help much appreciated :)

     

    Thanks.

    Wednesday, November 23, 2011 1:08 PM

Answers

  • Hi Rob,

     

    Try this:

    = Switch( Fields!Value1.value= 0 , "White",

    Fields!Value1.value>0.01 and Fields!Value1.value<0.03, "Yellow",

    Fields!Value1.value>0.04 and Fields!Value1.value<0.07, "Orange"

    )



    Regards,
    Manoj
    *Happy to help
    http://experiencingmsbi.blogspot.com/
    Experiencing Microsoft BI
    • Proposed as answer by Shahfaisal Muhammed Wednesday, November 23, 2011 3:06 PM
    • Marked as answer by Rob-R Wednesday, November 23, 2011 3:07 PM
    Wednesday, November 23, 2011 1:23 PM

All replies

  • Hi Rob,

     

    Try this:

    = Switch( Fields!Value1.value= 0 , "White",

    Fields!Value1.value>0.01 and Fields!Value1.value<0.03, "Yellow",

    Fields!Value1.value>0.04 and Fields!Value1.value<0.07, "Orange"

    )



    Regards,
    Manoj
    *Happy to help
    http://experiencingmsbi.blogspot.com/
    Experiencing Microsoft BI
    • Proposed as answer by Shahfaisal Muhammed Wednesday, November 23, 2011 3:06 PM
    • Marked as answer by Rob-R Wednesday, November 23, 2011 3:07 PM
    Wednesday, November 23, 2011 1:23 PM
  • Another simple way to handle this is to make this operation as part of the stored procedure/query itself, have a case statement with these ranges and have colors coming as output field name. Map that field to the color property of that control instead of having the formula. Otherwise as Manoj mentioned, you may also use the nested Switch statement.

    HTH

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Wednesday, November 23, 2011 1:26 PM
  • Thank you both, I didn't realise that was how to use the switch statement with ranges, now got this working, as a bonus using ABS too to cater for the negative and positive side of things.

    Next task is to see if I can make this a Public Function within the report as I'll have to call it on about 15-20 different columns... !

     

    Thanks :)

    Wednesday, November 23, 2011 2:31 PM
  • I've got as far as this:

     

    Public Function ColourRange(ByVal Value as Decimal) As String
    Switch(
    Abs(value) = 0, "White",
    Abs(Value) < 0.03, "Yellow",
    Abs(Value) < 0.2, "Orange",
    Abs(Value) < 1, "Red",
    Abs(Value) < 1, "Green"
    )
    End Function

     

    Trying to produce a public function. It doesn't seem to break but it doesn't do anything! Should the above work or have I made a simple error?!

    Wednesday, November 23, 2011 3:15 PM
  • Rob,

     

    For custom code, use this function

     

    Public Function checkVal( ByVal As Decimal)

    Select Case Abs(ByVal)
        Case 0
            return "White"
          
        Case 0.01 To 0.03
            return "Yellow"

        Case 0.04 To 0.07
            return "Orange"

        Case Else
            return " "
    End Select

    End Function



    Regards,
    Manoj
    *Happy to help
    http://experiencingmsbi.blogspot.com/
    Experiencing Microsoft BI
    Wednesday, November 23, 2011 4:23 PM
  • Thanks Manoj, I just tried that and it seems to give 1 warning and 1 error:

    Warning:

    [rsCompilerErrorInCode] There is an error on line 0 of custom code: Function without an 'As' clause; return type of Object assumed.

    Error:

    [rsCompilerErrorInCode] There is an error on line 0 of custom code: Keyword is not valid as an identifier

    I just copy and pasted the code above into the custom code part, does it matter if there's blank lines etc?

    Thanks - clearly I was miles away with my attempts!

    Wednesday, November 23, 2011 4:31 PM
  • It doesn't matter with any blank lines.

    It worked for me in my test environment, try removing the Abs function and share the output



    Regards,
    Manoj
    *Happy to help
    http://experiencingmsbi.blogspot.com/
    Experiencing Microsoft BI
    Wednesday, November 23, 2011 4:35 PM
  • Hmm, that gave me the same error. I'm not using the code anywhere yet, it just doesn't seem to like the custom code. I'm using this:

     

    Public Function checkVal(ByVal As Decimal)
    Select Case ByVal
        Case 0
            return "White"
        Case 0.01 To 0.03
            return "Yellow"
        Case 0.04 To 0.07
            return "Orange"
        Case Else
            return " "
    End Select
    End Function

     

    I tried having ByVal both in brackets and as above.


    Oh and the 'official' errors:

    [rsCompilerErrorInCode] There is an error on line 0 of custom code: [BC42021] Function without an 'As' clause; return type of Object assumed.
    [rsCompilerErrorInCode] There is an error on line 0 of custom code: [BC30183] Keyword is not valid as an identifier.

    • Edited by Rob-R Wednesday, November 23, 2011 4:39 PM
    Wednesday, November 23, 2011 4:38 PM
  • Are u using SSRS 2005?

    Change the function to:


    Public Function checkVal(ByVal As Decimal) As String
    Select Case ByVal
    Case 0
    return "White"
    Case 0.01 To 0.03
    return "Yellow"
    Case 0.04 To 0.07
    return "Orange"
    Case Else
    return " "
    End Select
    End Function


    Regards,
    Manoj
    *Happy to help
    http://experiencingmsbi.blogspot.com/
    Experiencing Microsoft BI
    Wednesday, November 23, 2011 4:58 PM
  • I'm using 2008, (not r2). It still gives me the keyword error, I could see that the As String got around the warning. A bit of research suggested this meant the function was using reserved keywords (the example I found was some dude calling a variable 'global') but I can't see anything like that above!
    Wednesday, November 23, 2011 5:01 PM
  • The following seems to work for me:

    Public Function GetColor(ByVal status as Decimal) as String
    
    IF Abs(status) = 0.00 Then
    
    Return "White"
    
    End IF
    
    IF Abs(status) <= 0.03 Then
    
    Return "PaleGoldenrod"
    
    End IF
    
    IF Abs(status) <= 0.20 Then
    
    Return "Yellow"
    
    End IF
    
    IF Abs(status) <  1.0 Then
    
    Return "Orange"
    
    End IF
    
    IF Abs(status) >= 1.0 Then
    
    Return "Red"
    
    End IF
    End Function


    Syntax is different but logic is roughly the same. Just need to see what the user reckons to my colour choices!

    Thanks for your help

    Thursday, November 24, 2011 10:45 AM