SQL Reporting "How to" - Conditional Color 2/4: Functions for Tables & Charts

[アーティクル]
02/16/2009

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.)

SQL Reporting "How to" – Conditional Color for Tables & Charts. 2/4

Part 2/4 Continuous Color functions - Hue

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 Part 2, 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.

In Part 3, 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.

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

Example of using some of the techniques outlined in Part 2 to draw attention to specific values in a table.

Contents - Part 2: Continuous Color functions - Hue

A quick “Computer Colors” 101

The algorithm logic

Changing the color (hue)
           ColorRYG: Red – Yellow – Green

     Optimising the code – Using Module Level Variables

     Swapping the Color Transition
           ColorRPB: Red – Purple – Blue
           ColorBPR: Blue – Purple - Red

     The Rainbow Functions
           Rainbow3: Cyan – Green – Yellow – Red
           Rainbow4: Blue – Cyan – Green – Yellow – Red
           Rainbow5: Purple – Blue – Cyan – Green – Yellow – Red
           Rainbow6: Red – Purple – Blue – Cyan – Green – Yellow – Red

     Using the parameters to achieve different effects / emphasis.

Overview

When displaying continuous color you have two dimensions you can vary; (i) the brightness (eg: Light blue to Dark Blue) & (ii) the Hue (the color).

Changing the brightness, also called Saturation, is nice because every color can fade to white (or black) & then fade back out to any other color. So this permits you to easily combine any color with any other. eg: Gender: Blue for Boys & Pink for girls, or Rainfall: Brown for Deserts thru to Olive for Forests.

Changing the Color (Hue) is nice because you can maintain the same light intensity, it seems to look nicer, & can more thru multiple “neutral” zones not just the one White/black. The disadvantage is that colors only combine thru the order of the frequency spectrum, as seen in a rainbow or color wheel.

A quick “Computer Colors” 101

Colors on most computers are represented by 24 bits, this consists of three 8 bit Octets representing in order Red, Green, Blue abbreviated to RGB. .NET provides a variety of methods / functions to help to convert this into human readable form. Often it is simply displayed in hex (eg: #FF00FF which translates to Max Red, Min Green, Max Blue which displays as Purple.)

Sometimes you will see ARGB, referring to a 32 bit color which is a simple extension adding an alpha channel which displays how transparent the color will be. While .NET Colors classes uses ARGB & SQL2008 color dialogs let you specify ARGB color. The Reporting Services codes you generate only need to be RGB.

As a handy reference I’ve included this table showing the transition around the outermost edge of a color wheel; Bright Red, thru Green, Blue & back to Red. This is also an example of how you can easily incorporate this color into your tables & chart.

Changing the Color (Hue) is quite straightforward. The colors are represented by RGB (Red,Green,Blue) each color group is 1 byte, so it ranges from 0 – 255 (or 0x00 - 0xFF).

If you are writing your own function to change color it is easy to confuse what color octet you need to be changing. I hope the table above will assist in helping you see which RGB group you need to adjust in order to move thru the rainbow.

It is worth noting that I’ve gone with the maximum intensity, leaving the non-changing values at their extremes (ie 00 or FF). If you’d prefer pastels or less bright, you can reduce the range of values (eg: perhaps 04 – C0), in order to get that effect.

I hope you might find it a handy reference when writing code. Most of routines remain the same, you just adjust the order of the color creation statements. Looking at what column you are changing eg: Red to Yellow may help you realise that it is the middle octet you need to be changing.

Disclaimer: This is only intended to give you a background on what you need to use color in a .NET environment. Other computer platforms & graphics systems may offer a completely different way to encode color.

The algorithm logic.

Regardless of moving thru Colors & Shades, the logic for all the routines included in this set of articles are similar.

As seen above, in a 32 or 24 bit color system there is only a finite number of steps to move from one base colour (eg: Red ) to another (eg Yellow ). To move from Red (#FF0000) to Yellow (#FFFF00), you change the Green byte. There is only 256 unique color steps.

We need to discover the range of data values you wish to put the colors to. eg: If the highest value in your data is 50 & the lowest value is -50. Then your range is 100.

Then determine the size of data change in order to make one color step.
ie: Data Range / Color steps = 100/256 = 0.39. So if your data has 2 values 1 & 1.8 there will be 2 color steps different. As 0.8 / 0.39 = 2 ignoring remainder.

Find the appropriate color by, <Distance from Lowest value> * Color steps.

Example: Your range is 100 & the minimum of –50 is Red. If a DataPoint has a value = 10, it will be 60 color steps above Red. Looking at the Green byte, for Red the Hex is 0x00. So 60/0.36 = 154 or 0x99. So the color for the value of "10" is 0xFF9900.

1. Changing the color (hue)

While it is simpler to write a function to just transition from one color to another purely by changing only one of the octets (R, G or B). It is not as useful a moving thru 2 scales. Say, Red to Yellow & then Yellow to Green. Red to Green seems to be the most popular & ironically the one most likely to trouble those affected by colour blindness.

To make this work we need know 4 values; the Max, the Min, the value which you want the middle color to be & the value of the data you need the color for.

Because we want the gradient to be the same from either direction from the middle, we determine which has the largest range, (Max – Middle) or (Middle – Min). Using the greatest range we then determine the step size & follow the algorithm outlined above. Except as we now have 2 color ranges we need to determine which range that our data point belongs to by comparing it to the neutral value.

As mentioned below you might want to make the code more robust by validating the parameter values. See “Warning” below.

It is also possible to optimise the code by only doing the range test once. But be careful as it may constrain you to only using it in one part of the report. See “Optimising the code” section below.

ColorRYG: Red – Yellow – Green

Not only is the code below intended to be an example of implementing the algorithm logic. This function is the one I use the most. People love stoplight colors. People don’t seem concerned about color blind viewer, “they can read the numbers or shapes”

Sample Uses: Everything, especially where you can equate good & bad. eg: Financial Progress: Above/Below Budgets. Production Inefficiencies, HR Employee churn, Road injury, Delivery delays, Days Sales Outstanding (DSO) etc.

Note in all the sample graphics I’m using just 0 – 255, this is to help you see what the function displays. The functions aren’t limited to that range, in fact it is designed to work with any numbers [positive or negative) & any size interval.

 Public Function ColorRYG(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)

    'Force color into Max-Min Range. Important if you want to Clip the color display to a subset of the data range. 
    Value = Switch((Value > MaxPositive), MaxPositive, Value < MaxNegative, MaxNegative, True, Value)

     '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
        'Green
        iColor = 255 - iColor 'Thus 0 = White & 255 = Green
        strColor = "#" & iColor.ToString("X2") & "FF00"
    Else
        'Red
        iColor = iColor + 255  'NB iColour is -ve;  -1 - -255 
        strColor = "#FF" & Math.Abs(iColor).ToString("X2") & "00"
    End If
    Return strColor
End Function

Warning: To reduce computing overhead I’ve not included many parameter checks in all the code samples in this post. That might also be OK for you especially if you are passing values obtained from the data. ie: MAX(myfield), Min(Myfield) & Avg(MyField).

If you expect to have data values beyond the Max/Min values you provide, I suggest you include additional error checking similar to :-
Is Neutral between MaxPositive & MinNegative?

If Value is greater than MaxPositive, set to MaxPositive
If Value is Less than MaxNegative, set to MaxNegative
 'Force color into Max-Min Range. Important if you want to Clip the color display to a subset of the data range.
Value = Switch((Value > MaxPositive), MaxPositive, Value < MaxNegative, MaxNegative, True, Value)

'Find appropriate color shade
Dim iColor As Integer = CInt(Math.Round((Value - Neutral) * decColorInc))
If MaxPositive < MaxNegative, swap their values. (or let it die & have the report developer catch their error. )
What you do in these circumstances is up to you. Some people may just hard code an “Error” color to return, perhaps Black or Red, Others may just return “transparent”

Optimising the code – Using Module Level Variables.

It is possible to store state in module level variables. So rather than calculate the Range & Color increment for each data value in the report, you could do it once on the first call & just refer to it for the subsequent calls.

On an extremely large report, or one used very frequently, this may save some CPU cycles.

The disadvantage is that the optimisation of the color coding is only going to be called once. Subsequent calls assume the same set of parameters. This may be OK if you are only using in one column or chart or if all report items need the same Min,max & threshold values (perhaps KPI’s all set the same). But it would prevent you from having different color ranges per group. (unless you create multiple copies of your function each with slightly different name.)

The main reason I mention it here is to provide an example of using Module level variables, as it might handy for other functions you write. If Report Services didn’t already provide functions like; Previous, RowNumber, RunningValue, it would be easy to write your own equivalents using this technique.

So think broader. You could write your own Moving Average function or some other calculation that uses info from prior rows. If your datasource is transactional or XML it is much more efficient to code-up your own function in Reporting services. Note: If your datasouce is Analysis Services, do it in your MDX.

Tip: For more on writing your own Moving Average Function, see Get More Out of SQL Server Reporting Services Charts its in the last 1/3rd of the article. This whole article is worth a read.

 ' Module level Variables 
Private bInitialized As Boolean = False
Private decColorInc As Decimal

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)

    '--- Execute once per report -----
    If Not bInitialized Then
        '-- Find Largest Range
        Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)
        Dim decNegRange As Decimal = Math.Abs(MaxNegative - Neutral)
        Dim decRange As Decimal = IIf(decPosRange > decNegRange, decPosRange, decNegRange)

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

        bInitialized = True
    End If

    '-- 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")  'xxFFxx 
    Else
        iColor = iColor + 255  'NB iColour is -ve;  -1 - -255 
        strColor = "#FF" & Math.Abs(iColor).ToString("X2") & Math.Abs(iColor).ToString("X2") 'FFxxxx 
    End If
    Return strColor
End Function

Swapping the Color Transition

This section covers how you specify which value you want to represent the lowest values & which the higher ones.

To explore this concept I created 2 functions ColorBPR (Blue/Purple/Red) & ColorRPB. As you will notice both of these functions are almost identical to the ColorRYG function. The only difference is which RYG element we change & what color we start with. So to get the mirror order we just swap the 2 lines that create the color.

ColorRPB (Red/Purple/Blue) is the 1st column in the table above.

 Public Function ColorRPB(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As String
    'Example: =code.ColorRPB(expression, Max(expression), Min(expression), 0)
    '=code.ColorRPB( 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 = CInt(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
        'Blue
        iColor = 255 - iColor 'Thus 0 = Blue & 255 = Purple
        strColor = "#" & iColor.ToString("X2") & "00FF"         '<<=== Swap these Lines
    Else
        'Red
        iColor = iColor + 255  'NB iColour is -ve;  -1 - -255 
        strColor = "#FF00" & Math.Abs(iColor).ToString("X2")    '<<=== Swap these Lines
    End If
    Return strColor
End Function

ColorBPR (Blue/Purple/Red) is the 2nd column table shown in the graphic above.

 Public Function ColorBPR(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As String
    'Example: =code.ColorBPR(expression, Max(expression), Min(expression), 0)
    '=code.ColorBPR( 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 = CInt(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 = Red & 255 = Purple
        strColor = "#FF00" & Math.Abs(iColor).ToString("X2")        '<<=== Swap these Lines
    Else
        'Blue
        iColor = iColor + 255  'Thus 0 = Purple & 255 = Blue
        strColor = "#" & Math.Abs(iColor).ToString("X2") & "00FF"   '<<=== Swap these Lines
    End If
    Return strColor
End Function

The Rainbow Functions

The Rainbow functions are here just to assist those who don’t want to think about the color chart & which RGB byte to change in order to move from one value to the other. These functions are limited in that they only take the 2 two extremes (Max & Min) & then generate a smooth transition of color between them.

You can make them more useful by extending them with additional parameters that permit you specify a specific value for each transition point.

To do so, look to the logic provided in ColorRYG & extend it to create a range for each color transition. eg:
 Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)
Dim decNegRange As Decimal = Math.Abs(MaxNegative - Neutral)

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

I’ve not provided this solution “out-of-the-box” due to the variety of permutations you could desire. The two key ones being finding the set of parameters with the largest range & letting that determine how fast colors change, or just changing colors evenly (but at different rates) between the values you supply as middle threshold parameters.

NB: In case you wonder, the numeral X after the RainbowX function name indicates the number of transitions, not number of colors.

NB: Some people feel that the Rainbow of colors is a bit bright. If you like, try washing them out either by making them more transparent (in SQL2008, Alpha value) or combine with the Saturation code <below> & add “whiteness” (by not going all the way to #00, in the non-transitioning byte – the one that is changing the color.)

Rainbow3: Cyan – Green – Yellow – Red

Sample Uses: Showing Human habitable temperatures. ie: Cyan = 0 °C, Green = 24 °C, Yellow = 37 °C & Red=60 °C (feel free to convert to your scale of choice; Fahrenheit or Kelvin).

Achievement: Red = Poor, Yellow = Borderline, Green = on target, Blue = Blew the numbers off the chart!!

 Public Function ColorRainbow3(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal) As String
    'Find Largest Range
    Dim decRange As Decimal = MaxPositive - MaxNegative
    Dim decColorRange As Decimal = decRange / 3

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

    'Find appropriate color shade
    Dim iColor As Integer = CInt(((Value - MaxNegative) Mod decColorRange) * decColorInc)

    'Return Appropriate  color
    Dim strColor As String

    Select Case Value
        Case Is >= MaxPositive    'Red (100)
            strColor = "#FF0000"
        Case Is >= MaxNegative + 2 * decColorRange  ' Yellow (80) -> Red
            iColor = 255 - iColor
            strColor = "#FF" & iColor.ToString("X2") & "00"
        Case Is >= MaxNegative + decColorRange  ' Green (60) -> Yellow
            'iColor = 255 - iColor 
            strColor = "#" & iColor.ToString("X2") & "FF00"
        Case Is >= MaxNegative    ' Cyan (40) -> Green
            iColor = 255 - iColor
            strColor = "#00FF" & iColor.ToString("X2")
        Case Is < MaxNegative               ' Blue
            strColor = "#00FF00"
        Case Else
            strColor = "Transparent"
    End Select
    Return strColor
End Function

Rainbow4: Blue – Cyan – Green – Yellow – Red

Sample Uses:
Once more, Human habitable temperatures, but extended to include colder climates like USA in winter.

Rainfall, Fire Risk, Crime Rates, Insurance Risk, &/or anything where 2 factors mix.

 Public Function ColorRainbow4(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal) As String
    'Find Largest Range
    Dim decRange As Decimal = MaxPositive - MaxNegative
    Dim decColorRange As Decimal = decRange / 4

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

    'Find appropriate color shade
    Dim iColor As Integer = CInt(((Value - MaxNegative) Mod decColorRange) * decColorInc)

    'Return Appropriate  color
    Dim strColor As String

    Select Case Value
        Case Is >= MaxPositive    'Red (100)
            strColor = "#FF0000"
        Case Is >= MaxNegative + 3 * decColorRange  ' Yellow (80) -> Red
            iColor = 255 - iColor
            strColor = "#FF" & iColor.ToString("X2") & "00"
        Case Is >= MaxNegative + 2 * decColorRange ' Green (60) -> Yellow
            'iColor = 255 - iColor 
            strColor = "#" & iColor.ToString("X2") & "FF00"
        Case Is >= MaxNegative + decColorRange   ' Cyan (40) -> Green
            iColor = 255 - iColor
            strColor = "#00FF" & iColor.ToString("X2")
        Case Is >= MaxNegative    ' Blue(20) -> Cyan
            'iColor = 255 - iColor 
            strColor = "#00" & iColor.ToString("X2") & "FF"
        Case Is < MaxNegative               ' Blue
            strColor = "#0000FF"
        Case Else
            strColor = "Transparent"
    End Select
    Return strColor
End Function

Rainbow5: Purple – Blue – Cyan – Green – Yellow – Red

Sample Uses:
Once more, Human habitable temperatures, I used this in the World Temps graphic above.

 Public Function ColorRainbow5(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal) As String
    'Find Largest Range
    Dim decRange As Decimal = MaxPositive - MaxNegative
    Dim decColorRange As Decimal = decRange / 5

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

    'Find appropriate color shade
    Dim iColor As Integer = CInt(((Value - MaxNegative) Mod decColorRange) * decColorInc)

    'Return Appropriate  color
    Dim strColor As String

    Select Case Value
        Case Is >= MaxPositive    'Red (100)
            strColor = "#FF0000"
        Case Is >= MaxNegative + 4 * decColorRange  ' Yellow (80) -> Red
            iColor = 255 - iColor
            strColor = "#FF" & iColor.ToString("X2") & "00"
        Case Is >= MaxNegative + 3 * decColorRange ' Green (60) -> Yellow
            'iColor = 255 - iColor 
            strColor = "#" & iColor.ToString("X2") & "FF00"
        Case Is >= MaxNegative + 2 * decColorRange  ' Cyan (40) -> Green
            iColor = 255 - iColor
            strColor = "#00FF" & iColor.ToString("X2")
        Case Is >= MaxNegative + decColorRange   ' Blue(20) -> Cyan
            'iColor = 255 - iColor 
            strColor = "#00" & iColor.ToString("X2") & "FF"
        Case Is >= MaxNegative      ' Purple(0) -> Blue
            iColor = 255 - iColor
            strColor = "#" & iColor.ToString("X2") & "00FF"
        Case Is < MaxNegative               ' Blue
            strColor = "#0000FF"
        Case Else
            strColor = "Transparent"
    End Select
    Return strColor
End Function

Rainbow6: Red – Purple – Blue – Cyan – Green – Yellow – Red

Sample Uses:
Showing relative combination of 2 independent factors. Say you are in insurance & need to display the possibility of Flood Risk & Bushfire Risk for a property:-

High Fire Risk = Red, High Flood Risk = Blue. Zero Flood & Zero Fire Risk = Green

Thus you move from Red to Green showing Fire Risk for properties with Negligible Flood risk.

You move from Blue to Green showing Flood Risk for properties with Negligible Fire risk.

The Red to Blue showing relative risk of Flood & Bush Fire

Once more, Human habitable temperatures, but extended to include colder climates like Antarctica, especially if you change the code from the first Red into white, so that it moves from White to Purple.

 Public Function ColorRainbow6(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal) As String
    'Find Largest Range
    Dim decRange As Decimal = MaxPositive - MaxNegative
    Dim decColorRange As Decimal = decRange / 6

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

    'Find appropriate color shade
    Dim iColor As Integer = CInt(((Value - MaxNegative) Mod decColorRange) * decColorInc)

    'Return Appropriate  color
    Dim strColor As String

    Select Case Value
        Case Is >= MaxPositive    'Red (100)
            strColor = "#FF0000"
        Case Is >= MaxNegative + 5 * decColorRange  ' Yellow (80) -> Red
            iColor = 255 - iColor
            strColor = "#FF" & iColor.ToString("X2") & "00"
        Case Is >= MaxNegative + 4 * decColorRange ' Green (60) -> Yellow
            'iColor = 255 - iColor 
            strColor = "#" & iColor.ToString("X2") & "FF00"
        Case Is >= MaxNegative + 3 * decColorRange  ' Cyan (40) -> Green
            iColor = 255 - iColor
            strColor = "#00FF" & iColor.ToString("X2")
        Case Is >= MaxNegative + 2 * decColorRange  ' Blue(20) -> Cyan
            'iColor = 255 - iColor 
            strColor = "#00" & iColor.ToString("X2") & "FF"
        Case Is >= MaxNegative + decColorRange      ' Purple(0) -> Blue
            iColor = 255 - iColor
            strColor = "#" & iColor.ToString("X2") & "00FF"
        Case Is >= MaxNegative      ' Red -> Purple(0)
            'iColor = 255 - iColor 
            strColor = "#FF00" & iColor.ToString("X2")
        Case Is < MaxNegative               ' Blue
            strColor = "#FF0000" '"#0000FF"
        Case Else ' Will never get here
            strColor = "Transparent"
    End Select
    Return strColor
End Function

Using the parameters to achieve different effects / emphasis.

Visual Grouping & Sorting - Color by specifying the endpoint extremes. (Value, Max, Min)

When writing code to create a color gradient you need to decide if the middle position is important. In some cases, all you want to do is to visually group similar values with a similar color. For this it might be sufficient to specify a minimum & maximum value & have the colors slide evenly between the two. A common use example is; setting the highest number is Green, the lowest to Red & any gradients in between may assist you to quickly compare the data values. NB: Given the significant number of males who are color blind (~7% of population) you might consider using Blue / Yellow or something other than Red/Green.

I’ve found that sliding between only 2 colors is handy in a minority of situations, but perhaps your work is different. Even less used is the case where you decide to slide evenly between 4 or more colors. In the majority of cases it is much more useful is to specify the color value of the midpoint.

A much simpler variant of this is to pick specific colors for specific values. eg: China = Red, Britain = Blue etc. As seen in the simple CASE statement in Part 1 of this post.

Highlight tipping points - Color by specifying the distance from key value. (Value, Max, Min,Threshold)

More often I find myself using color to highlight extremes from a value. eg: Above/Below Budget, Above/Below Average, Above/Below 100%, Above/Below 0. etc.

While is is possible to have many midpoint colors, (see Rainbow6), specifying 3 colors is the most used, moving from Min, Centre, Max. Because the human eye seems to comprehend & compare color gradients so well, I’ve found it best to use the same color increment for both sides of the centre. ie: If I have 10 steps to go from 0 (Green) to 100 (Yellow), then I’d prefer to use 10 steps to go from Yellow (100) to Red (200). So if the highest data value was only 133, as the range from Green-Yellow was 100, I’d never show Red. The value 133 would only be a shade of Orange, but that would permit comparison with 67 (100-33) which is the same distance from 100 but in the other direction.

So far that seems to be the preference of the vast majority of people who’ve used these reports. So that is the basis of the code samples shown here.

Ideas on how to set parameters for various applications:

Only show transition between 2 colors.

Set 4th parameter to equal either Max or Min extremes.

Example: Dam Levels: range between 0 - 100%. They can’t be more nor less.

=code.colorXYZ(Fields!DamLevel.Value , 100, 0, 0)
Specify a Target (or middle value) & specify extremes.

Example: Air Temperature: between -5 & 45 degrees Celsius. Perhaps you’d make the range wider but the idea is the colors are always the same, so you can compare from one report to the next. (like temperatures on the USA Today’s color weather map.

=code.colorXYZ(Fields!Temp.Value , 45, –5, –5)
Specify the Target & Limit the extremes with hardcoded values

NB: If you do this, remember to modify the code to add the check for Data values outside of Max-Min range.

Example: Show Actuals or Budget.

The color range is from 80% – 120%, the middle color is 100. Below 80% is Red & Above 120% is Green.

=code.colorXYZ(Fields!PctProfit.Value , 1.2, 0.8, 1.0)

Give the User control,

Create Report Parameters & use them as the values for your color function.

=code.colorXYZ(Fields!X.Value, Parameters!pMax.Value, Parameters!pMin.Value, Parameters!pThreshold.Value )
Adjust to the Data's Minimum or Maximum values

=code.colorXYZ (DataValue, Max(myField), Min(MyField), Avg(myField))
1. Remember the Aggregate functions are very powerful. The 2nd parameter is the Data Group name. If you specify that you can have the conditional colors tailored to the specific range for each grouping set in your report.
2. Unfortunately neither Reporting Services 2005 or 2008 supports Aggregates of Aggregates. Ie: Max( Sum(myfield) ). So if you need that as you occasionally do using the Matrix or Tablix items, Create a separate database query to calculate that & pass the results back either as a hidden parameter or simply as another data set. Eg:
  
  =code.colorXYZ( Sum(value), parmMax(SumValue), parmMin(SumValue) , 0
Show distance from average

Highlight underperformers, by setting threshold to the average of the group.

=code.colorXYZ
(DataValue, Max(myField), Min(MyField), Avg(myField))
Show distance from a fixed threshold

Example: Highlight those who fail to meet a business metric; KPI or Quality level. ie: (Value, Max, Min, Threshold Constant)

=code.colorXYZ (DataValue, Max(myField), Min(MyField), <Report Parameter or other database field>)

Warning: When you deal with aggregates pay very close attention to the 2nd parameter “Scope”.

In SQL2005 Tables it is optional, as if blank it automatically raises its scope to the parent. But it is not wise to get into this habit. If you leave scope blank in SQL2005 Charts, SQL2008 Tables & Charts you will get a single row. This is bad, none of your functions will display anything. So ALWAYS specify scope, If you haven’t got a clue what scope is, try using the name you called the dataset, or the name of the table or chart. eg: =code.colorRainbow5(Fields!MAX.Value, Max(Fields!MAX.Value, "WorldTemps"), Min(Fields!MIN.Value, "WorldTemps") ) or use the name of any grouping level you created.

In a Chart it is often the value of the category group.

=code.ColorTwoLevelShade(Fields!RegionID.Value, CountRows("chart3_CategoryRegion"),RowNumber("chart3_CategoryRegion"))

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
This "How to" guide provides ideas & code samples on using color to improve your users understanding
Anonymous
February 16, 2009
Part 4 of 4: Continuous Color functions for Multi-Dimensional analysis (in charts) This "How to" guide
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
Anonymous
August 18, 2010
So useful. Thanks for this. I've been looking everywhere. Could you tell me how any of these could be modified to use a Green-Yellow color scale like what's used in Excel?
Anonymous
May 08, 2013
This is great! One issue I have though is exported to excel. I get through about have of the cells and then the cells background color turns to black. There are a few that show color sporadically after that but for the most part it's all black down to the last place rank. I'm using Rainbow3 function. Anyone else come across this ?

次の方法で共有