My New Favorite Chart — Overlapping Bar Charts

Back in April, I had the pleasure of attending Jason Thomas’s (B | T) Advanced Charting Techniques in SSRS at SQL Saturday #220 in Alpharetta, GA.

During this session, I was introduced to my new favorite SSRS visualization — the Overlapping Bar Chart.

The overlapping bar chart is really just a standard bar chart with a range chart in the background.  The range chart in the background represents the sum of the bars in the foreground.  This allows you to see very quickly the total amount for a category as well as a quick breakdown of the parts that make up that total.

To create an overlapping bar chart, you will first need some data.  The data should have a category (such as dates or products) and then a couple of quantity columns and finally a total column which sums the quantity column.

An example of this might be a table which lists the sales of various products by size.  The following quick T-SQL query will generate some random data for this task.

[Product] VARCHAR(10), 
[NumSmall] INT
[NumMed] INT
[NumLarge] INT
[NumTotal] INT

INSERT INTO @TempTable VALUES (‘8/1/2013’, ‘Gloves’, CAST(RAND() * 100 AS INT), CAST(RAND() * 100 AS INT), CAST(RAND() * 100 AS INT), 0)
INSERT INTO @TempTable VALUES (‘8/1/2013’, ‘Pants’, CAST(RAND() * 100 AS INT), CAST(RAND() * 100 AS INT), CAST(RAND() * 100 AS INT), 0)
INSERT INTO @TempTable VALUES (‘8/1/2013’, ‘Shirts’, CAST(RAND() * 100 AS INT), CAST(RAND() * 100 AS INT), CAST(RAND() * 100 AS INT), 0)

UPDATE @TempTable SET [NumTotal] = ((SELECT [NumSmall] + [NumMed] + [NumLarge] FROM @TempTable WHERE [Product] = ‘Gloves’))  WHERE [Product] = ‘Gloves’
UPDATE @TempTable SET [NumTotal] = ((SELECT [NumSmall] + [NumMed] + [NumLarge] FROM @TempTable WHERE [Product] = ‘Pants’))  WHERE [Product] = ‘Pants’
UPDATE @TempTable SET [NumTotal] = ((SELECT [NumSmall] + [NumMed] + [NumLarge] FROM @TempTable WHERE [Product] = ‘Shirts’))  WHERE [Product] = ‘Shirts’

SELECT [Date], [Product], [NumSmall], [NumMed], [NumLarge], [NumTotal] FROM @TempTable

Produced the following output (note the quantities are random and will vary each time you run it):

In creating the chart, the first step is add a Data Source and a Dataset.  For the purpose of this example, the Data Source can be anything valid (it’ll never actually get used, it just needs to exist) and the Dataset will be an embedded query using the data above.

With the data added to the report, the next step is to insert a blank column chart into the report.

Next, add the category label, in this case it is Product.

At this point, if you view the chart, it should look like your typical bar chart with categories and a breakdown of size for each category.

To create the overlapping effect, we need to add an additional element.  Add an additional chart value to the chart for the Total category.  Initially this will add the element as another bar.  Change the Chart Type for this item to be the Range Column type within the Range section.

Finally, drag the new Range type to the top of the chart values section so that it will appear behind the bars rather than in front of them.

Viewing the report will now show you’re completed overlapping bar chart.

At this point, the only remaining work is to clean it up — Add or remove the Chart Title and Axis Titles, clean up the legend, and adjust the colors as they’re a bit ugly by default.  A cleaned up version will look more like this:

As a final example for the mighty overlapping bar chart, I present a view of time entry.  In this example, a given business day is 9 hours long.  Each hour of the day the activity performed by an individual is recorded — activities may be things like lunch, meetings, or holidays (charge codes), working on client issues (service tickets), or working on projects (longer term client items).  This visualization allows one to quickly see if the individual typically enters all 9 hours per day as well as how the breakdown for the day works.  When the threshold line dips to zero, this indicates a non-work day (such as a weekend or holiday) in which time was entered.

The full sample report and data used in the above product example can be found here.