How fast time flies! Another new version of SQL Server has been launched and it seems like it was only a few days ago that SQL Server 2008 was released. Those days, I was mainly focussing on SSRS, and I can’t express in words how happy I was when I saw all the new features in SSRS 2008 and 2008 R2. Today, I was reading through a great post by one of my favourite bloggers, Hilmar Buchta on SSRS Bar Chart Tips and Tricks and this prompted me to add a chart tip of my own here – Overlapping charts in SSRS.
A quick look into this requirement and many of the BI professionals would say that it is not possible to implement this chart in SSRS. Quite often we underestimate the power of SSRS (I myself didn’t know how to implement some of the charts that Hilmar mentioned in his post) and hence I feel it is important to share and increase awareness through blog posts and other mediums. Follow the instructions below to replicate this chart:-
1) Make a new report and use the query below to make a new dataset.
SELECT '2009' AS Year, 'Q1' AS Quarter, 45 AS Sales
UNION ALL
SELECT '2009' AS Year, 'Q2' AS Quarter, 30 AS Sales
UNION ALL
SELECT '2009' AS Year, 'Q3' AS Quarter, 25 AS Sales
UNION ALL
SELECT '2009' AS Year, 'Q4' AS Quarter, 50 AS Sales
UNION ALL
SELECT '2010' AS Year, 'Q1' AS Quarter, 50 AS Sales
UNION ALL
SELECT '2010' AS Year, 'Q2' AS Quarter, 30 AS Sales
UNION ALL
SELECT '2010' AS Year, 'Q3' AS Quarter, 35 AS Sales
UNION ALL
SELECT '2010' AS Year, 'Q4' AS Quarter, 60 AS Sales
UNION ALL
SELECT '2011' AS Year, 'Q1' AS Quarter, 55 AS Sales
UNION ALL
SELECT '2011' AS Year, 'Q2' AS Quarter, 50 AS Sales
UNION ALL
SELECT '2011' AS Year, 'Q3' AS Quarter, 65 AS Sales
UNION ALL
SELECT '2011' AS Year, 'Q4' AS Quarter, 75 AS Sales
2) Make a column chart and use Sales in the Data Values, Year in the Category and Quarter in the series.
3) Right click on the Year category group and check the category group name. Rename it to a user friendly name like Year as shown below
4) Create a new calculated measure by adding a new data value and enter the code below
=Sum(Fields!Sales.Value, "Year")
5) Change the chart type of the new measure to Range Column. Ensure that the chart type of the Sales measure is still column. Also bring the Sales measure to the bottom as shown in the image below
6) Change the color of the Range Column as Aqua (or any color you like) from Automatic, so that all bars of the range column have the same color.
7) You can enable the data labels for the Sales measure and disable the legend for the new calculated measure. Now if you preview the report, you will get the required result.
You can use the same technique (with a little modification in the dataset query) to implement charts like shown below
Hopefully, this post will help in getting your creative juices flow when you are faced with a tough SSRS chart requirement!