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.
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.
Related
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
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")
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.
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.
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.
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.
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.
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
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
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
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?
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
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
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
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.
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