Tuesday, February 12, 2013

Custom Code for Color Gradation in SSRS

Throughout my career as a Business Intelligence Consultant, I have met and interacted with a lot of DBAs. One thing (among many others) I particularly admire about them is that they have their own list of SQL scripts that they carry with them from job to job (and my observation is that the list usually grows with experience). This way, they don’t have to remember all of them, but when the need arises, they have the scripts at their fingertips. The only thing I have close to that is my list of custom codes for doing some specific operations in SSRS. One of my most frequently used scripts originates from this series of posts on how to conditionally color in SSRS. Based on my needs, I had tweaked the code such that it will display the color gradation to white for any input color. Thought that this might come in handy for some of you guys.

Custom code for Color gradation in SSRS


Public Shared Function ColorDWB(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal Neutral As Decimal, ByVal ColStr As String) As String
Dim ColVar1 As Integer
Dim ColVar2 As Integer
Dim ColVar3 As Integer

'Split the #RGB color to R, G, and B components
ColVar1=Convert.ToInt32(left(right(ColStr, 6),2),16)
ColVar2=Convert.ToInt32(left(right(ColStr, 4),2),16)
ColVar3=Convert.ToInt32(right(ColStr, 2),16)

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

'Find appropriate color shade
Dim Shd As Decimal = 255
Dim iColor1 As Integer
Dim iColor2 As Integer
Dim iColor3 As Integer
Dim strColor As String

'Reduce a shade for each of the R,G,B components
iColor1 = ColVar1 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar1) / decPosRange)))
iColor2 = ColVar2 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar2) / decPosRange)))
iColor3 = ColVar3 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar3) / decPosRange)))

'Return the new color
strColor = "#" & iColor1.ToString("X2")  & iColor2.ToString("X2") & iColor3.ToString("X2")    

Return strColor
End Function

How To Use

1) Lets say we have a simple report which shows the sales by vehicle type and month


2)  You can either compile this code and use it in your report or just paste it in the code tab of the Report Properties. If we just need to get different shades of a color (say #2322EE), we can just use the expression below as the BackgroundColor property of the cell.

=Code.ColorDWB(sum(Fields!Sales.Value), Max(Fields!Sales.Value, "DataSet1"), Min(Fields!Sales.Value, "DataSet1"), "#2322EE")

Color gradation across entire table

3) If you need the color gradation to be based on each row, then make sure that the max and min values are passed for the row (in this case, the Vehicle group) rather than the whole dataset.

=Code.ColorDWB(sum(Fields!Sales.Value), Max(Fields!Sales.Value, "Vehicle"), Min(Fields!Sales.Value, "Vehicle"), "#2322EE")

Color gradation across each row in table

4) If you need different colors for your vehicle, you can make use of the GetColor() custom code and then pass that in your expression:-

=Code.ColorDWB(sum(Fields!Sales.Value), Max(Fields!Sales.Value, "Vehicle"), Min(Fields!Sales.Value, "Vehicle"), Code.GetColor(Fields!Vehicle.Value)) 

Color gradation across each row in table with different color

Just make sure that the colors in the palette are using the hex values and not just strings like “Red”.

Make sure colors are hex values and not strings in the palette

5) You can also use this code to go across two colors. For eg, what if we want to go from Blue to White to Orange such that the mean is white, more is blue and less is orange? We can write an expression as shown below

=iif(sum(Fields!Sales.Value) >= (Max(Fields!Sales.Value, "DataSet1") + Min(Fields!Sales.Value, "DataSet1"))/2,
Code.ColorDWB(sum(Fields!Sales.Value), Max(Fields!Sales.Value, "DataSet1"), (Max(Fields!Sales.Value, "DataSet1") + Min(Fields!Sales.Value, "DataSet1"))/2, "#6495ED"),
Code.ColorDWB(-sum(Fields!Sales.Value), -Min(Fields!Sales.Value, "DataSet1"), -(Max(Fields!Sales.Value, "DataSet1") + Min(Fields!Sales.Value, "DataSet1"))/2, "#FFC125"))

Note that we are writing a conditional expression to see if the current field is more than the mean (max+min / 2). If yes, then we use the familiar expression with the Blue color (so more the value, darker the blue). If no, we need to do add a negative for all the fields so that the coloring happens in the reverse order (lesser the number, darker the Orange).

Blue to Orange color gradation across entire table

You can keep on tweaking this code and use it for a wide variety of visualizations. I am no VB.NET developer, so it is possible that there is a much better way to do the same. If you do know of any better technique, do post in the comments so that others can benefit from it.


  1. In the below expression as you specified


    in which function "#2322EE" is passed as argument???

    As ColorDWB function accept 4 arguments..

    I have given the above code in expression of BackgroundColor property--> giving me error

    The BackgroundColor expression for the 'textbox2' contains an error: End of statement expected

    1. I have corrected the error in your expression below

      one - you need to have the name of the dataset as a scope in your max statement (though it will not give a syntax error)
      two - you need to include the dataset name as the scope of your min statement which is Min(Fields!Amount.Value,"DataSet1") andnot Min(Fields!Amount.Value),"DataSet1")

  2. Hello Jason. This is an excellent solution. I have it working in my report, but I am dealing with negative numbers in some cases, where the more negative, the more color needed. Your code is doing the opposite. I have tried about 20 different variations, but nothing is working. If you have a spare moment, I would greatly appreciate your assistance.

    1. Point 5 deals with such a scenario. If you look at the expression for the Orange color, you can see that the color gets darker the more negative the number gets. I just use the first parameter as -min and then second parameter as -mean. This would ensure that the more negative number becomes positive, and then it becomes the max value.

  3. Hi, I've tried using your code above, but the coloring stops at the value 255.73 for me. I have 2 columns, 12 rows and the bottom 4 are graduated colour gradients but the top 8 are all white. What am I doing wrong? The values range from 794 to 88...but only 88, 106, 144 and 255 have colours. The rest are white. Help please.

    1. Might depend on the range. If your lowest numbers are really far from the other higher numbers, the color will be closer to white I guess. For eg, in step 3, the column 4 of Trucks is white. You can change that behavior if you want in the code (like changing the 255 to something else so that it will not be white). If that is not the case, you might want to try displaying the output to a textbox and debug what is going on.

    2. Thanks Jason. As these are live sales values, the split is now (MIN) 106 and (MAX) 794. Would you say thats too wide a split? I'm getting the below in the output debug window.... [rsInvalidColor] The value of the BackgroundColor property for the text box ‘Commision_Amount’ is “#FFFFFE38FFFFFE34C8”, which is not a valid BackgroundColor.

      What could cause that? I also tried to replace in EG.3 the 'vehicle' with 'executive' but it didnt like that which im confused as thats the dataset1 value name.

    3. Also, the colour gradution isn't the right way round when looking at your example. I would of expected the highest values to be the hex value and then working towards the lowest value (eventually moving from #2322EE to white/transparent) like in your examples. But thats the reverse for me, now it is just the bottom 3 that are a pale blue but the remaining 9 are all white. -

    4. Send me an email at with the dataset query + output of the dataset, the custom code, the report layout as well as the code you are passing there. I will take a look and let you know what is wrong.