通过

SQL Reporting "How to" - Conditional Color 3/4: Tint & Shade Functions

  • 项目

This "How to" guide provides ideas & code samples on using color to improve your users understanding of the data in your reports. It includes using gradients of color in your tables & charts. It also covers using color to represent a second dimension &/or multiple levels of data in a hierarchy.

Applies to: SQL Server Reporting Services 2005 & SQL 2008 (I’ve not tested it on SQL 2000, nor the version to follow SQL2008, but I expect it will work ok, too.)

 

Part 3 of 4: Continuous Color functions – Color Saturation

In Part 1, I covered simple ways to select from a fixed set of colors & how to assign them to the Color & Backcolor Properties of a Report Item (ie: report textbox), Plus a few other tips.

In Part2, I cover the algorithm logic & provide many samples you might choose to use as is, or as a basis to enhance. This should give you sufficient info to get them working in most report items, ie Tables, Textboxes etc. The focus is on routines that progress thru different colors.

In Part 3, Is a continuation of Part 2, showing how to progress from Light to Dark of a specific color.

In Part 4, I expand on Part 2 & 3 by looking at special needs of charting & multidimensional grouping.

 

Contents - Part 3: Continuous Color functions – Saturation

Changing the Saturation (lightness)
           ColorBWR: Blue-White-Red

     But what if I want to have different colors?
           ColorRWG: Red – White - Green

     What if I want it to go through Black instead of White?
           ColorBack: Any – Black – Any
           ColorFore: Any – White – Any

 

2. Changing the Saturation (Intensity, lightness, Shade & Tint) of a color.

This approach transitions colors thru white (or black).

It is handy when you want to show:-

  1. Greater density of a value
    ie: Higher Populations, greater costs, Product returns or failures per 1,000.
  2. Proportion between 2 extremes
    ; ie: Male vs Female contribution to Total population. US Govt - Republican vs Democrat
  3. Multi-Level Hierarchies - Different children that belong to the same parent
    See Multi-level Data & also Pie Chart discussion in Part 4 of this “How to” guide.

Example of Use: Red - White - Blue. Handy for Gender distribution & Temperature change from Normal.

image  image

In the example above I used the Dundas Map for Reporting Services add-in for SQL 2005 to show how the gradient from Blue - White - Red can be used. It is easy to see that Females between 70-74 yrs have a preference to live in South Eastern States.
Note: The Map control has thematic colors built-in & doesn't need any of the routines I supply in this post. However if you also want to show Gender mix in a Table or Chart then use the routine below.

Sample: ColorBWR Blue-White-Red

 Public Function ColorBWR(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As String
    'Example Use: =code.ColorBWR(expression, Max(expression), Min(expression), 0)
    '=code.ColorBWR( Fields!MalePCT.Value, max(Fields!MalePCT.Value), min(Fields!MalePCT.Value), 0.5)

    'Find Largest Range
    Dim decRange As Decimal
    Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)
    Dim decNegRange As Decimal = Math.Abs(MaxNegative - Neutral)

    decRange = IIf(decPosRange > decNegRange, decPosRange, decNegRange)

    'Find Delta required to change color by 1/255th of a shade
    Dim decColorInc As Decimal = 255 / decRange

    'Find appropriate color shade
    Dim iColor As Integer = CInt(Math.Round((Value - Neutral) * decColorInc))

    'Return Appropriate +ve or -ve color
    Dim strColor As String
    If iColor >= 0 Then
        'Red
        iColor = 255 - iColor 'Thus 0 = White & 255 = RED
        strColor = "#FF" & Math.Abs(iColor).ToString("X2") & Math.Abs(iColor).ToString("X2")
    Else
        'Blue
        iColor = iColor + 255  'NB iColour is -ve;  -1 - -255 
        strColor = "#" & Math.Abs(iColor).ToString("X2") & Math.Abs(iColor).ToString("X2") & "FF"
    End If
    Return strColor
End Function

But what if I want to have different colors?

Given that you transition thru white (or black) the order of the colors in the color wheel is irrelevant, so you can easily combine any color you like. Thus it would be fairly straight forward to give your users parameters so they could select any colors they prefer. Including colors not in the primary group I’ve used throughout this article, ie: Brown or Lilac 

image

Example of color saturation & transitioning via white.

To create a function that transitions from a new set of colors ie Red-White-Green (ColorRWG), start with ColorBWR & just plug in the colors you want. If you can’t recall them, copy them from the Rainbow6 code listed earlier in this blog article.

 Public Function ColorRWG(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As String
    'Example: =code.ColorRWG(expression, Max(expression), Min(expression), 0)
    '=code.ColorRWG( Fields!Sales.Value,max( Fields!Sales.Value),min( Fields!Sales.Value),0)
    'Find Largest Range
    Dim decRange As Decimal
    Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)
    Dim decNegRange As Decimal = Math.Abs(MaxNegative - Neutral)

    decRange = IIf(decPosRange > decNegRange, decPosRange, decNegRange)

    'Find Delta required to change color by 1/255th of a shade
    Dim decColorInc As Decimal = 255 / decRange

    'Find appropriate color shade
    Dim iColor As Integer = CInt(Math.Round((Value - Neutral) * decColorInc))

    'Return Appropriate +ve or -ve color
    Dim strColor As String
    If iColor >= 0 Then
        iColor = 255 - iColor 'Thus 0 = White & 255 = Green
        strColor = "#" & iColor.ToString("X2") & "FF" & iColor.ToString("X2") 
    Else
        iColor = iColor + 255  'RED. Note: iColour is -ve;  -1 - -255 
        strColor = "#FF" & Math.Abs(iColor).ToString("X2") & Math.Abs(iColor).ToString("X2") 
    End If
    Return strColor
End Function

What if I want it to go through Black instead of White?

To change via Black you just need the reciprocal function to what we’ve been using above. Instead of moving to 0xFFFFFF by changing the Color Octets you aren’t interested in. eg: to move from Red to White, you always keep Red at 0xFF & move Green & Blue from 0x00 to 0xFF.

Going thru Black is easier. You only move the color group you are interested in & leave the others at 0xFF.

image

Example of transitioning color via Black & showing the need to also change Font color to maintain readability.

 Public Function ColorBack(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As String
    'Example: =code.ColorBack(expression, Max(expression), Min(expression), 0)
    '=code.colorback( Fields!Sales.Value,max( Fields!Sales.Value),min( Fields!Sales.Value),0)
    'Find Largest Range
    Dim decRange As Decimal
    Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)
    Dim decNegRange As Decimal = Math.Abs(MaxNegative - Neutral)

    decRange = IIf(decPosRange > decNegRange, decPosRange, decNegRange)

    'Find Delta required to change color by 1/255th of a shade
    Dim decColorInc As Decimal = 255 / decRange

    'Find appropriate color shade
    Dim iColor As Integer = CInt(Math.Round((Value - Neutral) * decColorInc))

    'Return Appropriate +ve or -ve color
    Dim strColor As String
    If iColor > 0 Then
        strColor = "#00" & iColor.ToString("X2") & "00"     'Green - Black
    Else
        strColor = "#" & Math.Abs(iColor).ToString("X2") & "0000"   'Red - Black
    End If
    Return strColor
End Function

Remember to change the foreground color too; ie Font Color property

As the Text color is commonly black, anytime you let the background color get really dark eg: Black or Deep Blue, it gets hard to read the text. So you should consider using a color function to change the font color at the same time. The cut off point from Black font to White could be a parameter, but in this function I’ve tuned it to what I think works well with the background I’m using , ie: 126.

 Public Function ColorFore(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As String
    'Find Largest Range
    Dim decRange As Decimal
    Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)
    Dim decNegRange As Decimal = Math.Abs(MaxNegative - Neutral)

    decRange = IIf(decPosRange > decNegRange, decPosRange, decNegRange)

    'Find Delta required to change color by 1/255th of a shade
    Dim decColorInc As Decimal = 255 / decRange

    'Find appropriate color shade
    Dim iColor As Integer = CInt(Math.Round((Value - Neutral) * decColorInc))

    'Return Appropriate +ve or -ve color
    If Math.Abs(iColor) > 126 Then  '<< I’ve hard coded the cut over value, should be a constant or param
        Return "#000000" 'Color.Black
    Else
        Return "#FFFFFF" 'Color.White
    End If
End Function

 

I hope you found this part of the series useful & that it goes someway to improving your users ability to interpret their information & make better decisions, faster.

Please give feedback. Is this series of articles useful? Did it save you time? What was good, What could be better?, Notice any errors? What would you like me to cover? All thoughts, comments, suggestions welcome.

Technorati Tags: SQL Server 2008,SQL 2008,SQL Reporting Services,SQL Server 2005,SQL 2005,SSRS,Color Coding,Colour Coding,Conditional Formating,Charts,Graphs

Comments

  • Anonymous
    February 16, 2009
    PingBack from http://www.clickandsolve.com/?p=9156

  • Anonymous
    February 16, 2009
    wow i say!! David Lean has just released an in depth, 4 part series on how to use conditional formatting

  • Anonymous
    February 19, 2009
    I recently helped my Australian colleague David Lean successfully resolve a small report design challenge

  • Anonymous
    February 22, 2009
    David Lean's Conditional Color in Reporting Services Series