Pages

Sunday, March 18, 2012

Overlapping Charts in SSRS using Range Charts

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.

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.

Column Chart

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

Change category group name 

4) Create a new calculated measure by adding a new data value and enter the code below

=Sum(Fields!Sales.Value, "Year")

Measure expression

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

Column and range chart

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.

Change color of Range chart

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.

Overlapping bar chart

You can use the same technique (with a little modification in the dataset query) to implement charts like shown below

Overlapping stacked charta

Hopefully, this post will help in getting your creative juices flow when you are faced with a tough SSRS chart requirement!

14 comments:

  1. Great post. It's incredible how much you can do with reporting services out of the box, if you just have the right ideas.

    ReplyDelete
    Replies
    1. Thanks Hilmar, means a lot coming from you! :)

      Delete
  2. 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?

    ReplyDelete
    Replies
    1. I completely agree with you Emil. I was just too lazy to take care of the visualization part and hence kept the default colors! :)

      Delete
  3. 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.

    ReplyDelete
  4. 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.

    ReplyDelete
  5. A very nice post on charts Jason. Slowly i am becoming a fan of your blog.

    ReplyDelete
  6. 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.

    ReplyDelete
    Replies
    1. It's actually a range column chart with same values and same color but 2 columns. So it looks like one wider column.

      Delete
  7. 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?

    ReplyDelete
    Replies
    1. 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?

      Delete
  8. Thanks, Jasn. Awesome post - helped me lot to understand the incredible possibilities of SSRS

    ReplyDelete
  9. 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

    ReplyDelete