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!
Great post. It's incredible how much you can do with reporting services out of the box, if you just have the right ideas.
Thanks Hilmar, means a lot coming from you! 🙂
Hi Jason,
The first picture is great and your example how to do it is also great. It will definetely solve certain graphs that I had in mind but I couldn't find a proper way to visualize it.
There is something I would also like to share with you. This is only my personal opinion, something I noticed on SQL Bits IX as well 😉
I think having too many colours is very distracting and makes "data reading" much harder. Hilmar examples require much less effort from me to see data. What do you think?
I completely agree with you Emil. I was just too lazy to take care of the visualization part and hence kept the default colors! 🙂
it's a Great post Jason, I hope that you post more about SSRS. Because, i had red some books of SSRS but these are basic.
Great post Jason
Hello Jason,
You are the great, i have been following your posts and blog since almost 3 years.I am maintaining one specific folder your posts, you are the star.
Jason bhai,please please help me by providing the solution for zoom on charts, select a piece of area on the chart and zoom that particular spot,it's like Bing maps/Google maps.I hope i will read that solution on our blog very soon.
Thank you Jason.
A very nice post on charts Jason. Slowly i am becoming a fan of your blog.
Great post. In the last image with the stacked bars, how did you managed to get the blue bars in the background to be wider? can't seem to find the setting to adjust.
It's actually a range column chart with same values and same color but 2 columns. So it looks like one wider column.
Many thanks for sharing your idea.
I've been trying to implement the last image in 2012, but I can't seem to get the inner bar to centralise – it's rendering left or right justified, any thoughts why this is happening?
Ola, ensure that the inner bar is a stacked column chart (so you have series as well as category) and the outer bar is range column chart (and since there are 2 values in series, you will get 2 range charts, one each on the left and right of the stacked column chart). From your description, it looks like there is only one value in the series, in which case it may appear left or right. For just one value, you can just convert the chart to 3 D and reduce the pixel width of the inner bar. If this doesn't answer your question, could you detail out more like what is on your category and series?
Thanks, Jasn. Awesome post – helped me lot to understand the incredible possibilities of SSRS
Hi can you suggest me if we can create a chart as shown or not using SSRS.
Thanking you in advance.
http://tinypic.com/view.php?pic=o0cu41&s=8#.U82Se_ldVDc
Awesome Post. Works like a charm. Thanks !!
Excellent post, this is exactly what I needed, thanks!
However, do you know if it's possible to add a single Data Label atop the calculated measure. If you enable Data Labels for that series then you get multiples overlapping due to the component parts making up the larger cyan bar. For now I have added an expression with SUM() to the Year group, but would prefer it to have the total displayed within the chart. Hope that makes sense. Thanks!
Thank you for the Sum expression by Category. While this is far far from what I needed, it took me towards the solution!
good job….its very use-full for Overlapping graph
Could you please solve the problem of “series label moves out of the bar in horizontal range graph”. The problem is occur only when the amount of data is high. We already check all the series label properties. But, we could not found any solution. Please provide a suitable suggestion as soon as possible. Waiting for your reply.