NewsArticles Minimize
27

Introduction

Effective data presentation techniques help users interpret information quickly and reliably.  Layout, formatting, sizing, labeling, and other report elements may all be used to facilitate analysis and decision-making.  This article will focus on one aspect of formatting:  color.  More than just an aesthetic concern, color serves a valuable purpose to enhance perception and understanding of data.

We will explore methods and reasoning for how color may be presented in charts.  The goal is to provide the report developer with a straightforward approach to handling chart colors, to avoid time spent on trial and error and provide guidelines for best practices.  SQL Server Reporting Services 2008 is used for all examples.

Brief Color Theory Primer

The Color Wheel originated from Isaac Newton’s theory of color.  The three primary colors are red, yellow, and blue (notice the bold triangle in the center of Figure 1).  Likewise, green, orange, and purple are secondary colors because they fall directly between the respective primary colors, and can be created by mixing primary colors.

    
     Figure 1:  Color Wheel

Colors have the following characteristics:
     1.  Hue -- Hue is the color itself, such as red or blue.
     2.  Lightness -- Also referred to as value or tone, lightness signifies the level of darkness of the color.
     3.  Saturation -- Also known as chroma, saturation refers to the intensity or dullness of the chosen color.

When selecting a hue for charts and reports, a report developer needs to decide whether to select colors from opposing ends of the color wheel, or variations which lie closer together.  Making this decision is easier when considered alongside the type of data which is being conveyed.  Maureen Stone, an information visualization expert, devised the concept of Categorical, Sequential, and Diverging sets of colors:

Categorical palettes separate data into distinct, qualitative, groups by using colors which are very different from each other (i.e., from opposite ends of a color wheel).
    
     Figure 2:  Categorical Palette Sample

Sequential palettes focus on quantitative differences by varying lightness and darkness (i.e., close together on the color wheel).  With a sequential palette, zero is on the far left.  
   
     Figure 3:  Sequential Palette Sample


Diverging palettes are similar to Sequential, except that two sets of colors are used which merge in the center.  With a diverging palette, zero is in the middle.
   
     Figure 4:  Diverging Palette Sample

Considerations Before Development Begins

Your organization’s requirements and standards will dictate which of the color approaches discussed in this article will work best.  Considerations include:
     - Is there a custom set of colors that are typical, or required, for your environment?
     - Are your users accustomed to interpreting things based on a particular color scheme?
     - Is the chart placed within a dashboard where other dashboard elements, including colors, influence it?
     - Are there particular colors, common in your workplace, which would enhance the chart’s meaning quickly and intuitively?

With SQL Server Reporting Services 2008 charts, I have found the following approaches to using colors: 
     1.  Built-In Color Palette
     2.  Custom Color Palette
     3.  Expressions
     4.  VB Code

The following is an overview of each of the above SSRS chart capabilities with respect to hue.  This discussion assumes a basic knowledge of SSRS chart functionality.

Built-In Color Palette:  Single Color 

The Built-In Color Palette option is the default setting when an SSRS chart is created.  This section focuses on using one color, which defaults to the first color in the palette.

Pros:
     - Quick to implement
     - Because only a single color is used, there is no confusion as to meaning of color as it relates to the data

Cons:
     - Very few palettes to choose from
     - Restricted to the first color returned by the palette
     - No additional context to assist users’ understanding, other than the height of the bar (or length, or size of slice, etc.) relative to the data points

How it’s used in SSRS:

Add one Data Field and one Category field (with nothing in the Series).  SSRS displays the first color in the color palette specified in the Chart Properties.  Using the default palette color (Bright Pastel) the first color is a bright blue, as shown in Figure 5.

         
      Figure 5:  Sample Chart - Built-In Palette Using a Single Color

This method is perfectly acceptable if the first color that is returned by the palette works for your needs.  Simple reports which don’t require significant formatting may find this approach useful. 

The remainder of this article focuses on using various colors in a chart.  However, each method discussed below may also be translated to a single color.

Built-in Color Palette:  Various Colors

The Built-In Color Palette option is the default setting when an SSRS chart is created.  This section focuses on using all colors available within the palette (as opposed to one color as discussed in the preceding section).

Pros:
     - Straightforward
     - Quick to implement

Cons:
     - Colors are not correlated to the data values
     - Built-in color schemes are somewhat unattractive
     - Limited palette choices
     - Relatively few unique colors before colors repeat

How it’s used in SSRS:
Add one Data Field and one Series field (with nothing in the Category).  SSRS displays various colors from the color palette specified in the Chart Properties.  The default color palette is Bright Pastel. 

This is the same chart as shown in Figure 5.  However, the Category field was moved to the Series which caused the default palette to go from one color to multiple colors.

    
      Figure 6:  Sample Chart - Built-In Palette Using Various Colors

The Built-In Color Palette provides colors that are random and don’t have any meaning.  As such, they can draw attention to certain data arbitrarily.  For example, in Figure 6, the red and orange colors aren’t the lowest percentages that you want to call the user’s attention to.  If your end users aren’t accustomed to using color to derive meaning, this approach could work.  However, sometimes it proves detrimental to use color just for the sake of using color.

The number of unique colors is limited with the built-in color palette.  Depending on which palette you choose, after 10-16 items are shown in the Series (and Legend), the colors will begin to repeat which can confuse users.  Using the default palette color, Bright Pastel, colors start repeating after 15 data items in the chart.

Custom Color Palette

If a Built-in Color Palette discussed previously does not meet your needs, the next logical step to investigate may be a Custom Color Palette.

Pros:
     - Easy to set up
     - Great selection tools in BIDS using color pickers, RGB, and/or hexadecimal codes
     - Straightforward way to conform to a standardized color scheme

Cons
     - Can be time consuming to select colors that work well together
     - Colors are not correlated to the data values
     - Need to ensure enough custom colors are entered to capture all distinct data values

How it’s used in SSRS:
It’s helpful to compile a list of complementary colors before beginning this aspect of chart development.  If standards do not exist for the organization, a site I like to browse for color ideas is ColorSchemer.

There are two steps to implement a custom color palette:

1.   Palette property.  The first step is to select “Custom” for the chart’s color palette.  If you use the Property Pages (i.e., the right-click menu which contains a subset of all properties available for an object) it’s within Chart Properties and is called Color Palette.  In the Properties Pane it’s called the Palette property.

2.   Custom Palette Colors property.  The second step is to define the Custom Palette Colors in the Properties Pane.  This property is only available in the Properties Pane, not within the Property Pages accessed via a right-click.  Use the Add button which opens the color picker window to define the list of colors, keeping in mind they’ll be rendered in the order they appear in the Color Expression Collection Editor.

    
     Figure 7:  Custom Palette Properties

In the chart, if you add one Data Field and one Series field (with nothing in the Category), the custom palette colors are rendered as shown in Figure 8.

    
     Figure 8:  Sample Chart - Custom Color Palette

This method works really well if you need to conform to an organization’s color scheme.  It also works well to standardize the look and feel of a dashboard page. 

If possible, stick with colors that don’t imply meaning like red, yellow, orange, and green do.  In Figure 8, although the colors vary, they shouldn’t confuse the user because the earth tones don’t imply meaning.

When using a custom color palette, estimate the number of unique colors which will be needed to display each data point within the chart.  Include a few extra colors in the custom color palette list, to ensure enough distinct colors are available.

A custom color palette may be used to create a Categorical, Sequential, or a Diverging set of colors – if each color can be used randomly.  Figure 8 would be considered a sequential palette.

Expressions

When there is a requirement to present certain colors based on the data values, there are two ways to accomplish this:  Expressions and VB Code.  In this section we will address Expressions.

Pros:
     - Flexible control of data presentation
     - Useful to call users’ attention to data items of interest

Cons:
     - Can be somewhat time consuming, particularly if you are iteratively deciding on the color choices
     - Need to have a finite list of rules, to capture all possible values, for this approach to work flawlessly

How it’s used in SSRS:
Right-click the chart and select Series Properties.  On the Fill page, select the function (fx) button next to Color.  Enter the expression desired, based on data values.

    
     Figure 9:  Series Properties - Fill Color

In addition to the color names shown in Figure 9, you may also use a hexadecimal code (ex:  "#d01616").  A useful website for handling conversions from RGB to hexadecimal code is at RGBToHex.net.

Using the same chart as the previous examples, Figure 10 shows how the expression is rendered in the report.  This example is done with the School Name on the Category, although this technique works equally as well if the School Name were placed in the Series.

    
     Figure 10:  Sample Chart - Expression Based on Data Values

If you wish to use color to draw attention to problem areas, this is a good solution.  This technique can be particularly useful when there are numerous values present, and when the range of those values can vary extensively.  You may also want to consider providing drill-through to additional details, especially if the end user is expected to act on the information provided.

In some instances, the legend doesn’t reliably represent all of the Fill choices contained with an expression.  Therefore, I usually create a legend manually, displayed in a table just below the chart.  To create a legend:  Add a new table (tablix) data region, with two columns, and insert as many rows as needed.  On the left, create a label for each color choice.  To the right of each label, set the textbox backcolor to equate to what was used within the chart expression.

If you choose to use a nested IIF statement, as shown in Figure 9, be careful with its construct.  For each value, the first criteria which is met will be used.  An alternative in SSRS to the nested IIF statement, which can get convoluted pretty quickly, is the Switch() statement.

Expressions may be used to create a Categorical, Sequential, or a Diverging set of colors.  Figure 10 would be considered a categorical palette. 

VB Code

Using VB Code is an alternative to using SSRS Expressions, as discussed in the previous section.  Nearly all of the same comments from the preceding Expressions section apply here as well.  This is just an alternative method for those developers who feel comfortable using VB.NET code in their reports.

Pros:
     - Flexible control of data presentation
     - Useful to call users’ attention to data items of interest

Cons:
     - Slightly more difficult syntax
     - Need to have a finite list of rules, to capture all possible values, for this approach to work flawlessly

How it’s used in SSRS:
There are two steps to implement VB Code in a report:

1.   Custom code syntax.  Create the method within the Code window, found under Report Properties.

    
     Figure 11:  VB Code Examples (In Report Properties)

In Figure 11, two method examples are shown:  one based on the Color Name; another based on the hexadecimal codes.  Both examples return the same results, so you will want to select the one you are most comfortable using.

2.       Expression.  To call the function within the expression window, right-click the chart and select Series Properties.  On the Fill page, select the function (fx) button next to Color. 

    
     Figure 12:  VB Code Example (Series Properties - Fill Color)

Using the same chart as the previous examples, the following is how the code from Figures 11 and 12 is rendered in the report.  This example is done with the School Name on the Category, although this technique works equally well if the School Name were placed in the Series.

    
     Figure 13:  Sample Chart - VB Code Based on Data Values

Using VB Code is an alternative way to accomplish the same thing discussed in the preceding Expressions section.  Whether or not to use Expressions or VB Code is depends on the preferences, and skills of the developers and support staff in your workplace. 

A custom .Net assembly, which is external to the SSRS report project, may also be called to use the same technique.  If you anticipate reusing the logic within other reports, or even another application, an external assembly may be used to centralize the logic.

Final Thoughts

A few additional thoughts:

  • Consider users who are color blind.  The red, yellow, and green examples used in this article are useful to illustrate the point, but are not optimal for color blind users.  Ideally, red and green are not presented on the same report.  Stephen Few suggests replacing the green color with blue, a hue which can be differentiated by most color blind persons.
  • Try to avoid the temptation to include a variety of colors merely because they are attractive.  When done poorly, color may misdirect the users’ attention so the most valuable information is not the primary focus.
  • Be consistent from report to report, so users become accustomed to what to expect.
  • In addition to the chart itself, consider adding a companion grid with tabular data supporting the chart.  The grid data could be accessed via drill down or drill through, to save screen real estate.  Providing additional data could help a user further understand the chart’s contents and see details in one place.
  • Keep chart labels, backgrounds, and contextual information non-colorful.  Black, white, and shades of grey typically work best.
  • Not all colors are considered web-safe, meaning your browser will try to render the closest match it can find.  When it doubt, consult a web-safe color list online.
  • Consider whether your users are likely to print copies, and if those copies are likely to be color or black and white.  Very dark colors consume more ink.
  • Test the colors on multiple displays, particularly on a laptop where colors tend to look a bit washed out.
  • Don’t hesitate to ask your coworkers for advice.  Some will shrug because they don’t have an eye for visualization, but hopefully you’ll find someone with a keen eye who is interested in offering an opinion.

Summary

Ideally, a report developer has an awareness of color theory to make wise decisions.  Visualization experts such as Stephen Few and Edward Tufte provide helpful, sound advice regarding presentation of data.  Above all, the goal is to be conscious of the data being displayed, and how the colors  enhance the information.

Do you need to… Built-In
Color Palette
Custom
Color Palette
Expressions VB Code
Create a Categorial palette Yes Yes Yes Yes
Create a Sequential palette Yes Yes Yes
Create a Diverging palette   Yes Yes Yes
Conform to existing standard or color scheme Yes Yes Yes
Correlate color with data value     Yes Yes
Call user's attention to data items of interest Yes Yes
Reuse and consolidate logic       Yes

Using color skillfully can greatly enhance usability of charts and reports.  Most information workers have limited time and are subjected to information overload.  Taking a bit of extra time during the report development process to address color, as well as other formatting considerations, will enhance the BI experience for the user – and isn’t that what Business Intelligence is all about?

References

Resources used for this article include:
     - Article by Stephen Few:  Practical Rules for Using Color in Charts 
     - Articles by Maureen Stone:  Choosing Colors for Data Visualization and Expert Color Choices for Presenting Data
     - Wikipedia page:  Color Theory
     - Color Advice for Maps:  ColorBrewer2

About the Author

Melissa Coates is a Business Intelligence consultant with Mariner in Charlotte, NC.  Melissa’s blog is at http://www.sqlchick.com.

 

Post Comment

Only registered users may post comments.

LatestArticles Minimize
Computed column 'mydate' in table 'test2' Cannot be Persisted because the Column is Non-deterministic. by Wayne Snyder
How to create a calculated column which converts a string to a date and persist it. The problem is that the calculated column must be deterministic.

SSAS Process Full from SSIS with Parameter for DatabaseName by Wayne Snyder
We have recently had a need to process tabular models programmatically, but we needed it to be easily done by an end user – self service is the key – right? To solve this problem we have a...

Using Linked Tables in PowerPivot by Wayne Snyder
PowerPivot allows you to create a table in an Excel worksheet, and import it into your PowerPivot model. You can import data from Excel spreadsheets, but this differs in that the Excel data we wish to...

Errors Creating and Maintaining Relationships with PowerPivot Tables by Wayne Snyder
In this article you will learn how to create relationships in PowerPivot. More Importantly, you will learn about many-to-many relationships. Since PowerPivot only supports 1-to-many relationships, you...

Importing Flat Files into PowerPivot– Overcoming Problems by Wayne Snyder
You may run into issues when trying to import flat files into PowerPivot. This article documents some of the issues, and includes workarounds when available. As I bump into new problems, I will try to...

Importing Flat Files Into PowerPivot by Wayne Snyder
Bringing flat files into your Powerpivot model is easy. We will go through the process step by step, so that you can begin mashing up data to get your job done more quickly, more effectively, and with...

Changing Granularity of Leaf Level Calculations in SSAS Tabular by Jason Thomas

Finding Nearest Stores using SSRS Map Reports by Jason Thomas

Heat Maps for SSRS using Map Control by Jason Thomas

Parameters for Analysis Services Reporting: Introduction, Pt. 3 by William Pearson
BI Architect Bill Pearson continues an extended examination of parameterization within Analysis Services reports. In this, Part 3 of the article, we continue to get hands-on practice cr...


Advertisement Minimize

Advertisement Minimize

Copyright 2004-2013 MSBICentral.com Terms Of Use Privacy Statement