SSRS Color Wheel

SSRS (2005) Color Wheel

...more like a color list, but who's keeping score

I don't think there is anything in the development world more looked down upon than writing a report. It's kind of got a little shame attached to it. While your friends are (seemingly) out there creating the next Twitter you are stuck building a list of sales grouped by region. However, I have always been a little fond of it. I have an interest in design and layout, problem solving, and organizing data: reporting can touch on all of them.

Recently I had a request to change the color scheme of a report I was working with. This wasn't out of the ordinary except they weren't too sure what they wanted. It got to the point where I started to wonder if their significant other had similar thoughts when they shopped for paint.

However, our team had an idea - why not provide a report that listed all the default colors available in the system, along with all their official names. This had some immediate advantages:

• The user could refer to a color they wanted by name
• SSRS 2005 has a "bug" where colors on the report don't look the same when that report is exported to Excel

 

Now that's what I call a sexy report!

[The Plan]

I looked into different ways of building this report and came up with the plan below. Unfortunately I was not able to generate the color list completely in code. It got to the point where I thought I would have to build an external library to provide the list from System.Drawing, and I'm not a fan of maintaining a code library for a simple color list.

Instead, I built the list of available colors using a simple query. While this may seem a little rudimentary, it has the additional benefit of encouraging management to implement a standard color scheme. This could be the first step towards using the database to provide a standardized color palette for reporting.

I'm not big on providing downloads for examples. I feel that involves a level of trust that I cannot provide. So I explain how to do it below. I use SQL Server SSRS 2005 terms, because that's what the client is using.

[The Dataset]

MainDS:
-- manual list of colors in VS2005 system
Select 'Black' as [color_name] union all
Select 'White' union all
Select 'DimGray' union all
Select 'Gray' union all
Select 'DarkGray' union all
Select 'Silver' union all
Select 'LightGray' union all
Select 'Gainsboro' union all
Select 'WhiteSmoke' union all
Select 'Maroon' union all
Select 'DarkRed' union all
Select 'Red' union all
Select 'Brown' union all
Select 'Firebrick' union all
Select 'IndianRed' union all
Select 'Snow' union all
Select 'LightCoral' union all
Select 'RosyBrown' union all
Select 'MistyRose' union all
Select 'Salmon' union all
Select 'Tomato' union all
Select 'DarkSalmon' union all
Select 'Coral' union all
Select 'OrangeRed' union all
Select 'LightSalmon' union all
Select 'Sienna' union all
Select 'SeaShell' union all
Select 'Chocolate' union all
Select 'SaddleBrown' union all
Select 'SandyBrown' union all
Select 'PeachPuff' union all
Select 'Peru' union all
Select 'Linen' union all
Select 'Bisque' union all
Select 'DarkOrange' union all
Select 'BurlyWood' union all
Select 'Tan' union all
Select 'AntiqueWhite' union all
Select 'NavajoWhite' union all
Select 'BlanchedAlmond' union all
Select 'PapayaWhip' union all
Select 'Moccasin' union all
Select 'Orange' union all
Select 'Wheat' union all
Select 'OldLace' union all
Select 'FloralWhite' union all
Select 'DarkGoldenrod' union all
Select 'Goldenrod' union all
Select 'Cornsilk' union all
Select 'Gold' union all
Select 'Khaki' union all
Select 'LemonChiffon' union all
Select 'PaleGoldenrod' union all
Select 'DarkKhaki' union all
Select 'Beige' union all
Select 'LightGoldenrodYellow' union all
Select 'Olive' union all
Select 'Yellow' union all
Select 'LightYellow' union all
Select 'Ivory' union all
Select 'OliveDrab' union all
Select 'YellowGreen' union all
Select 'DarkOliveGreen' union all
Select 'GreenYellow' union all
Select 'Chartreuse' union all
Select 'LawnGreen' union all
Select 'DarkSeaGreen' union all
Select 'LightGreen' union all
Select 'ForestGreen' union all
Select 'LimeGreen' union all
Select 'PaleGreen' union all
Select 'DarkGreen' union all
Select 'Green' union all
Select 'Lime' union all
Select 'Honeydew' union all
Select 'SeaGreen' union all
Select 'MediumSeaGreen' union all
Select 'SpringGreen' union all
Select 'MintCream' union all
Select 'MediumSpringGreen' union all
Select 'MediumAquamarine' union all
Select 'Aquamarine' union all
Select 'Turquoise' union all
Select 'LightSeaGreen' union all
Select 'MediumTurquoise' union all
Select 'DarkSlateGray' union all
Select 'PaleTurquoise' union all
Select 'Teal' union all
Select 'DarkCyan' union all
Select 'Cyan' union all
Select 'Aqua' union all
Select 'LightCyan' union all
Select 'Azure' union all
Select 'DarkTurquoise' union all
Select 'CadetBlue' union all
Select 'PowderBlue' union all
Select 'LightBlue' union all
Select 'DeepSkyBlue' union all
Select 'SkyBlue' union all
Select 'LightSkyBlue' union all
Select 'SteelBlue' union all
Select 'AliceBlue' union all
Select 'DodgerBlue' union all
Select 'SlateGray' union all
Select 'LightSlateGray' union all
Select 'LightSteelBlue' union all
Select 'CornflowerBlue' union all
Select 'RoyalBlue' union all
Select 'MidnightBlue' union all
Select 'Lavender' union all
Select 'Navy' union all
Select 'DarkBlue' union all
Select 'MediumBlue' union all
Select 'Blue' union all
Select 'GhostWhite' union all
Select 'SlateBlue' union all
Select 'DarkSlateBlue' union all
Select 'MediumSlateBlue' union all
Select 'MediumPurple' union all
Select 'BlueViolet' union all
Select 'Indigo' union all
Select 'DarkOrchid' union all
Select 'DarkViolet' union all
Select 'MediumOrchid' union all
Select 'Thistle' union all
Select 'Plum' union all
Select 'Violet' union all
Select 'Purple' union all
Select 'DarkMagenta' union all
Select 'Fuchsia' union all
Select 'Magenta' union all
Select 'Orchid' union all
Select 'MediumVioletRed' union all
Select 'DeepPink' union all
Select 'HotPink' union all
Select 'LavenderBlush' union all
Select 'PaleVioletRed' union all
Select 'Crimson' union all
Select 'Pink' union all
Select 'LightPink'

[Extra Credit: Show each color's HTML Code color]

Report > Report Properties

References:
Added System.Drawing

Code:
Public Function HTMLColor(my_color as String) as String
Dim colorObj As System.Drawing.Color = System.Drawing.Color.FromName(my_color)
return String.Format("#{0:X2}{1:X2}{2:X2}", colorObj.R, colorObj.G, colorObj.B)
End Function

I got the meat of this function from a StackOverflow post:
http://stackoverflow.com/a/5207560/103131

[Layout]

Having everything in a query allowed me to get the most out of the layout options in SSRS. This made it really easy to setup a Table in the Layout to show what I wanted. I also added a column of White as a control. It really helped see the subtle differences in some of the lighter shades.

I put together a mock-up below:

[Column Name]: Property
[Color Name]: (value)=Fields!color_name.value
[Color]: (BackgroundColor)=Fields!color_name.value
[White]: (BackgroundColor)=White
[HTML Code]: (value)=Code.HTMLColor(Fields!color_name.Value)

I think this qualifies as a boring report, but it can be very useful when communicating which colors users want on their reports.