Post reply

Clustered and Stacked Column and Bar Charts for SSRS

  • May 29, 2012 at 9:23 am

    #269636

    Good Day Everyone,

    Has anyone come across the need to display a chart using the following representation 'clustered and stacked column and bar chart' in SSRS, as shown in the MS-Excel samples here

    I found this solution , but as it uses the secondary axis, I cannot set a target afterwards.

    Here is the actual goal for the chart I would like to achieve:

    Thank you for your feedbacks !

  • May 29, 2012 at 10:52 am

    #1494297

    In summary you need to use the series that needs to be alone on the secondary X axis to separate from the other stacked bars. Demo below from SSRS 2008 R2. Image attached similar to the required. I have also attached the original query and an RDL demo of the requirement (please change relevant file extensions).

    In the demo I have :

    Cat1A, Cat1B and Cat1C (stacked bar),

    Cat2 (single bar),

    Target (line chart - normal)

    Return Perc (line chart - line hidden and markers enlarged)

    1) Create the datasource and dataset as you normally would. In the demo I created a query as below:

    select '2012-01' as Dated, 130 as Cat1A, 180 as Cat1B, 210 as Cat1C, 290 as Cat2,

    400 as TargetValue, 0.98 as ReturnPerc

    union

    select '2012-02' as Dated, 100 as Cat1A, 120 as Cat1B, 130 as Cat1C, 300 as Cat2,

    400 as TargetValue, 0.97 as ReturnPerc

    union

    select '2012-03' as Dated, 150 as Cat1A, 150 as Cat1B, 200 as Cat1C, 300 as Cat2,

    400 as TargetValue, 1.00 as ReturnPerc

    (...continue for the complete year)

    2) Add a chart as you normally would. Change the chart type of the target and Return perc to line. Make changes to the Return Perc (Data4) series properties to only show the markers and change the border to none (no line). Change the Return Perc to the secondary Y axis and format the axis as percentage.

    3) Change any of the Cat1A, Cat1B, Cat1C or Cat2 to a stacked bar. Change Cat2 (Data5 in yours) to use the secondary X axis. This puts the bar next to the stacked set (Cat1x). Remove the secondary X axis (top of chart) to get the output you require.

    Fitz

  • May 29, 2012 at 11:43 am

    #1494321

    This is perfect. Mark, you are very impressive. Thanks again for sharing your knowledge and understanding of the SSRS platform with all of us on this community.

    Cheers,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply