Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
#BI 39
BRH 39
#MSBI 29
SQL Server 28
#SSRS 22
#SQL Server 21
Reporting 19

Archive · View All
April 2010 8
May 2010 7
January 2013 6
November 2012 5
March 2012 5
July 2012 4
January 2012 4
October 2011 4
June 2010 4
February 2013 3

Some Random Thoughts

Custom Code for Color Gradation in SSRS

Feb 13 2013 9:09AM by Jason Thomas   

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))
ColVar2=Convert.ToInt32(left(right(ColStr, 4),2))
ColVar3=Convert.ToInt32(right(ColStr, 2))

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


Jason Thomas
19 · 9% · 2997


Your Comment

Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]