Overlapping Column Bar Charts in SSRS

Andrew Smith Posted on

This post draws from www.helpmeviz.com – a website devoted to best practice in data visualisation.

The idea behind Helpmeviz is that anybody creating data visualisations can post their work-in-progress to the site, so that community members can review it and provide constructive feedback. I really like the report shown at the top of this page, and decided to see how close I could recreate it in SSRS (on SQL Server 2014), using data from the AdventureWorksDW database.

This is my version:

overlappingsaleschangebyproduct2

It’s certainly close to the original – the main feature I couldn’t reproduce was the varying font colour on the y-axis, where the original used blue for group headings (i.e. the labels in upper case) and grey for the rest. SSRS does allow expressions to be defined for axis label font colours but I found this feature not to work, and where Microsoft have confirmed this in a reply to a related forum post.

Additionally the legend would have been more consistent if it used the ‘positive’ colours (the 2 blues) for both series, whereas the negative colour (grey) has been used for the wider bars and the positive colour (dark blue) has been used for the narrower bars. SSRS appears to chose which colours to use for the legend based upon the colours used in the first category plotted (i.e. the top row in the chart).

Overlapping Bars

I really like the overlapping bars used in the chart – they save vertical space in bar charts with many categories, compared to the alternative of having the bars side by side (vertically), and it’s also easier to compare both values for each category when they’re overlapped rather than side by side.

The way to do this in SSRS is to initially create the chart as a standard horizontal bar chart, and then right-click the series that will become the wider bars and change its chart type to a range bar chart. Then the point width custom attribute of each series needs changing so that the inner bar and outer bar are different widths. I used 0.7 for the wider series and 0.3 for the narrower series, e.g.:

overlappingpointwidth

The colour change for the bars between negative and positive values was implemented using an expression for the fill colour for each series, e.g.:

=IIF(Fields!NonBikesPCT.Value >= 0, “#1578C1”, “#D03E69”)

Report Query

My query on the AdventureWorksDW database returned data in this format:

overlappingquery

The main point to note here is the existence of the SalesTerritoryRegion and SortOrder columns. Neither appear visually but both are used as part of the sort specification of the StateProvinceName category group. The group sorts first by SalesTerritoryRegion, then by SortOrder and then by StateProvinceName.

The SortOrder column was introduced so that each SalesTerritoryRegion name sorts before its territory region members. (The same result could also have been achieved by taking advantage of the null percentage values for territory region rows, but this would have been more obtuse to understand for anybody else looking at the code (or for me 6 months later!), and would break if ever these nulls were changed to values, e.g. if some form of aggregation was assigned to these rows.)

Report Aesthetics

The original author of this report incorporated into it many aspects that follow best practice data visualisation guidelines, e.g.:

  • The report title states the message that the author wants to convey, as opposed to having a generic title such as “Sales Data: 2007 vs 2008” and hoping that the audience will figure it out
  • The colour palette is restricted, and the colours used are easy on the eye
  • Having the x-axis at the top means the audience knows how to interpret the data before reaching it
  • Not having axis lines for the x and y axes helps eliminate redundant clutter
  • Having no border around the bar chart helps eliminate redundant clutter
  • Having plenty of whitespace eases the comprehension of the data, even though there are many categories
  • Having the bar chart horizontal means that there’s plenty of space for the relatively long province name labels. A vertical chart would either have had to severely abbreviate these or else angle them at 45 or 90 degrees, which is difficult for the eye to read
  • As already mentioned above, the overlapping bars reduce the vertical space taken up and ease the comparison for each category

Many of the observations listed above are explained in detail in Cole Nussbaumer Knaflic’s excellent book Storytelling with Data.

Summary

The report above was relatively simple to implement and demonstrates that SSRS can easily accommodate overlapping bar charts, as well as many aspects of data visualisation best practice. (Being able to have varying axis label colours would have been a bonus though!)