In this blog post, I am continuing to explore the different color palette options for charts, tables and matrices in SQL Server Reporting Services (SSRS). This is a bonus post to our three blog post set that shows you how to create, use and synchronize a color palette between charts, tables and matrices in SSRS reports. In this post, I am taking the custom color palette code from, Color Palette Codes in SSRS Defined and Explained, and showing you how to define colors for a particular value using the GetColor function.

Define Colors

Before getting started, let’s talk a bit about colors. I hope we can all agree that the colour red is associated with words like, “danger,” “crisis,” or, “emergency.” While the colour green is associated with words like, “good,” “safe,” and so on. Similarly, “neutral,” is represented by the colors gray or blue, and the colour orange represents, “fair,” or, “just okay.” I am using these color concepts when coding these colors into my report’s charts.

Define Colors - Before Dashboard

The dashboard, in the above image, displays information about the overall count of operating system types, rings and operating system states. Without defining any of the colors from my custom color palette with a particular value, all of the colors shown in the charts and tables above were randomly assigned by SSRS. It looks nice, but the color choice isn’t particularly helpful. For example, the purple segment shown in the last chart represents End of Support states. Where’s the warning color that brings my attention to this area?

I am going to focus on the last chart and table which displays different operating system states: Supported, Expires Soon, Release Ready, End of Support and Unknown. Each state is a value, so using the color concepts outlined earlier, I am assigning green (#00ff00) to Supported, orange (#ff6347) to Expires Soon, blue (#0000ff) to Release Ready, red (#ff0000) to End of Support and gray (#97989a) to Unknown.

How to Define Colors for a Value

I turn to the GetColor function (as seen in Color Palette Codes in SSRS Defined and Explained) to define these colors. How does this work? The answer is to use an If … Then … Elseif … Else statement within the GetColor function. These statements allow me to assign a color to each of the values listed above.

Code

After adding these statements, this is how the function appears:

Public Function GetColor(ByVal groupingValue As String) As String

Dim c As String

If groupingValue = “Supported” Then

C = “#00ff00”

elseif groupingValue = “Expires Soon” Then

C = “#ff6347”

elseif groupingValue = “Release Ready” Then

C = “#0000ff”

elseif groupingValue = “End of Support” Then

C = “#ff0000”

elseif groupingValue = “Unknown” Then

C = “#97989a”

else

If mapping.ContainsKey(groupingValue) Then

Return mapping(groupingValue)

End If

c = colorPalette(count Mod colorPalette.Length)

count = count + 1

mapping.Add(groupingValue, c)

end if

Return c

End Function

If you were to do a comparison between the original color palette code’s function and this one, you would notice that the functions are basically the same. The only difference is that I am forcing a particular color to be used when the If … Then logical operators match the string. When the string doesn’t match, it falls back to the automatically assigned color from the color palette.

After Defining Colors

Define Colors - After Dashboard

Finally, this is the end result after I customized the report’s properties to display specific colors in the last chart. Doesn’t it make it easier for the end user to see how their environment stacks up at a first glance? Now I can see that the smallest percentage of computers in this collection are End of Support. It’s little tweaks like these that make Enhansoft’s reports so useful for our customers. You might also notice that the colors are different in the first chart, but they remain the same in the second one. This, again, is SSRS randomly assigning colors from my custom color palette. If you want consistency in your reports and to make your charts easier to understand, define what color is used when.

Conclusion

Use this important tip in order to make the end user’s life easier. I recommend enhancing your reports as much as possible to make them more understandable. Let me know if you come across any other such tweaks, and if you have any questions about how to define colors, you can either reach out to me on Twitter @SuaresLeonard or to our Chief Architect @GarthMJ. Till then, good luck.