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

Code

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

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

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

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

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

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.

### 30 comments

Manju Redrowthu

In the below expression as you specified

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

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

SQL_Jason

I have corrected the error in your expression below
=code.ColorDWB(sum(Fields!Amount.Value),Max(Fields!Amount.Value,"DataSet1"),Min(Fields!Amount.Value,"DataSet1"),"#2322EE")

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

Don Fisher

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.

SQL_Jason

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.

Michael Barrett

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.

SQL_Jason

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.

Michael Barrett

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.

Michael Barrett

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. – http://s22.postimg.org/8qap2d0fl/Capture.jpg

SQL_Jason

Send me an email at jason143@gmail.com 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.

Peter Vandivier

Love this code!! Thanks so much for posting!!

I wanted to chime in my two cents in case someone coming after wanted to get the same slightly modified use I did (took me a bit of playing around before I figured it out). I used this for font color gradient black-to-red in descending sort from black at 100% to red at 0%. I just set shading start to 0 instead of 255 in the code at line 15

'Find appropriate color shade
Dim Shd As Decimal = 0

Lakshmi Melam

Thanks much for the code…I have to implicate the same in my report… Will this work in 2008 R2?……because I'm getting an error " the background color expression for the text box " " has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set."

Your help would be much appreciated.

Thanks much

[…] The GetColor() is used to select new colors for each category type and the ColorDWB() is used to get a lighter shade of the selected color (you might recognize the ColorDWB function from my post on Custom Code for Color Gradation in SSRS). […]

SG

Thanks SQL_Jason..for such a nice code to capture color variation…Really appreciate.

SG

I am facing one issue after implemented the different color gradation, When rendering the output on excel, it is displaying BLACK color for many Rows\Columns. Could you please suggest any solution.

simone

Were you able to resolve this issue. I have been battling it the past couple days

Paul

Same here. It is MADDENING. It does not matter what colors you use. What is interesting is that one cell that was blacked out yesterday when i generated the report and exported to Excel is shaded as expected today.

Michael

I am having an issue with it telling me the custom coding has an error

System.Web.Services.Protocols.SoapException: There is an error on line 7 of custom code: [BC30037] Character is not valid.
at Microsoft.ReportingServices.Library.ReportingService2005Impl.SetReportDefinition(String Report, Byte[] Definition, Guid batchId, Warning[]& Warnings)
at Microsoft.ReportingServices.Library.ReportingService2010Impl.SetItemDefinition

Any idea what this means?

Abdul

Hi I am getting a similar error.

There is an error on line 7 of custom code [BC30037] character is not valid

did you find out what it was

Jo Bojanowski

Hi, It’s to do with the (re)formatting of the code. Several characters have been auto formatted into illegal characters such as curly quote marks and long dashes.

Maybe the following will work or it might suffer the same:
“`
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
“`

Jo Bojanowski

Maybe this code block will not be reformatted by wordpress:
``` 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 ```

Gunasekar Ramadoss

Hi Jason,

I need to have Green color for positive values – (Darkest Green to Lightest Green ) and Red Color for Negative Values – (Darkest Red to Lightest Red).

Thanks In Advance for your Reply.

[…] the row (cell that has the highest value will be the darkest colored). I’ve tried following this guide to color the cells, but here the entire row is one field, while I have separate fields for […]

Abdul

Hi,

I am getting the following message

There is an error on line 7 of custom code [BC30037] character is not valid

Anthony

Also getting same error here when trying to use
Using ssrs 2014

Paul

I found that if you copy and paste the code, you will receive this error, but if you type it all manually, you do not. There must be a hidden character in there somewhere?

Paul

I found that if you copy and paste the code, you will receive this error, but if you type it all manually, you do not. There must be a hidden character in there somewhere?

RichardAE

manually replace the single-quotes, double-quotes, and the subtraction symbols

Craig

Anyone managed to solve the problem when exporting to Excel? The Excel colour pallete can’t cope and I’m trying to figure a way to convert

Stewart

Does anybody know how to get this script to work with lower values? I have a matrix where the values in each row group could possibly vary from between 1 and 8. When i run the background expression for these numbers there is no shading and it’s all white background.

Jay Walker

I updated the function to allow reversal of the color gradient.

Public Shared Function ColorDWB(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal Neutral As Decimal, ByVal ColStr As String, ByVal Dir as Integer) As String
Dim ColVar1 As Integer
Dim ColVar2 As Integer
Dim ColVar3 As Integer
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)
Dim decPosRange As Decimal = Math.Abs(MaxPositive – Neutral)
Dim Shd As Decimal = 255
Dim iColor1 As Integer
Dim iColor2 As Integer
Dim iColor3 As Integer
Dim strColor As String

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

If (Dir1) Then
‘iColor1 = ((iColor1 – 255)*-1)
iColor2 = ((iColor2 – 255)*-1)
iColor3 = ((iColor3 – 255)*-1)
End If

strColor = “#” & iColor1.ToString(“X2”) & iColor2.ToString(“X2”) & iColor3.ToString(“X2”)
Return strColor
End Function

This site uses Akismet to reduce spam. Learn how your comment data is processed.