As I discussed last week in part 1 of this article, color can be used to highlight common elements across groups. As promised, here's the necessary code to achieve this effect in Reporting Services 2005 (or you can download the finished report here):
1. Create a new report, and add a query. I've used AdventureWorks and a simple query that returns employees and years of service:
--get employees by Department, with years of service from AdventureWorks
SELECT TOP 50 D.[Name] AS [DepartmentName],
EMP.[EmployeeId], C.[FirstName] + ' ' + C.[LastName] AS [EmployeeName],
--just an example: really simple, bogus calculation for years of service
--from start date till now
DATEDIFF(year, EMP.[HireDate], GETDATE()) AS [YearsOfService]
FROM --Employees
HumanResources.[Employee] EMP WITH (NOLOCK) INNER JOIN
--Person details (name)
Person.[Contact] C WITH (NOLOCK) ON
EMP.[ContactID] = C.[ContactID] INNER JOIN
--current/last Department
HumanResources.[EmployeeDepartmentHistory] EDH WITH (NOLOCK) ON
EMP.[EmployeeId] = EDH.[EmployeeId] AND
EDH.[EndDate] IS NULL INNER JOIN
--Department details
HumanResources.[Department] D WITH (NOLOCK) ON
EDH.[DepartmentID] = D.[DepartmentID]
ORDER BY D.[Name], EMP.[EmployeeId]
2. Create a table in the report. For my AdventureWorks sample, I've listed the employee's name and years of service by department groups.
3. Add the code for assigning a random color based on a passed value. This goes under "Properties", "Code". The code is fairly self-explanatory; it creates an empty dictionary called m_dic_KeyAndColors and each time GetBackgroundColor is called, checks if the passed item is already in the dictionary. If the passed item is in the dictionary, the color associated with it is returned. If not, new random color is assigned and added to the dictionary before being returned:
''' <summary>
''' Dictionary of passed string and matching colors, populated as it is used
''' </summary>
Private m_dic_KeyAndColors As New System.Collections.Generic.Dictionary(Of String, String)
''' <summary>
''' New random object, based on code at http://www.developerfusion.co.uk/show/3940/
''' </summary>
Private objRandom As New System.Random(CType(System.DateTime.Now.Ticks Mod System.Int32.MaxValue, Integer))
''' <summary>
''' Returns a set color based on the passed string.
''' </summary>
Public Function GetBackgroundColor(ByVal s As String) As String
Try
' does the dictionary have an entry with the passed string and matching color?
If m_dic_KeyAndColors.ContainsKey(s) Then
' return the pre-stored color
Return m_dic_KeyAndColors(s)
Else
' get a new random color by calling "GetRandomBackgroundColor", add it and the
' passed string to the dictionary, and return the color
m_dic_KeyAndColors.Add(s, GetRandomBackgroundColor())
Return m_dic_KeyAndColors(s)
End If
Catch ex As Exception
' return neutral color
Return "#ececed"
End Try
End Function
''' <summary>
''' Return a random web color where 2 of R, G or B are fixed by the passed
''' "Highest" and "Lowest" hues, and a random value between the two is generated
''' for the remaining. Which values get assigned to R, G and B is also randomised.
''' For .NET-centric explanation of colors, see Dave Lean's blog post at
''' http://blogs.msdn.com/davidlean/archive/2009/02/17/sql-reporting-how-to-conditional-color-2-4-functions-for-tables-charts.aspx
''' </summary>
''' <param name="Highest">Highest hue value (0-255), set to light color with low saturation by default</param>
''' <param name="Lowest">Lowest hue value (0-255), set to light color with low saturation by default</param>
''' <returns>Hex color string in he format "#RRGGBB"</returns>
''' <remarks>Adapted from http://www.perlmonks.org/?node_id=305209 </remarks>
Public Function GetRandomBackgroundColor(Optional ByVal Highest As Integer = 215, Optional ByVal Lowest As Integer = 153) As String
' sanity check: ensure "highest" and "lowest" are between 0-255
If Highest > 255 Then Highest = 255
If Highest < 0 Then Highest = 0
If Lowest > 255 Then Lowest = 255
If Lowest < 0 Then Lowest = 0
' sanity check: is "highest" higher than "lowest"
If Highest < Lowest Then
Dim temp As Integer
temp = Lowest
Lowest = Highest
Highest = temp
End If
' get a random number in the middle of highest and lowest
Dim Middle As Integer = objRandom.[Next](Lowest, Highest + 1)
' create an array with the 3 values - Highest, Lowest and Middle
Dim a() As Integer = New Integer() {Highest, Lowest, Middle}
' randomise the order of the 3 values with Fisher-Yates shuffle
' see also http://www.codinghorror.com/blog/archives/001015.html
For i As Integer = 2 To 0 Step -1
Dim n As Integer = objRandom.[Next](i + 1)
Dim temp As Integer = a(i)
a(i) = a(n)
a(n) = temp
Next
' return a web color string using hex/"X2" formatting
Return String.Format("#{0:X2}{1:X2}{2:X2}", a(0), a(1), a(2))
End Function
4. In the background color expression for the table cell to be colored, call "=Code.GetBackgroundColor([YourFieldValue])". In my example, the background color expression is "=Code.GetBackgroundColor(Fields!YearsOfService.Value)".
Here's the end result, downloadable from CodePlex:
Aside: Why Did I Randomly Generate the Colors?
Initially I had an array of pastel colors that I'd already selected. That was until I came across Dave Lean's series on generating random colors for reports which explains how to generate random colors and convert decimal to hex. Suitably inspired, I looked around and found a useful snippet to generate light random colors only at this PERL forum posting (which forms the basis for my GetRandomBackgroundColor function).
Optional Steps
Repeating text can be turned off so that the color "groups" the data together.
Limitations
- Color should not be used as the sole indicator to make distinctions for important data, as there's a percentage of the population who have trouble telling colors apart (color blindness or color deficiency). You can find more information here.
- This technique only makes sense on a small scale and I would not use it on large amounts of data. For large amounts of data, groups and sub-groups identify relationships in data better than color alone. Also, with many distinct items, you run the risk of having very similar colors that makes highlighting by color less effective.
- This technique has only been tested in Visual Studio 2005.
- As you can see from the finished result, this is not a heatmap. Each item is assigned a new totally random color.
- Every time the report is run, different random colors will be generated.
Tags: reporting services, visual, group, color