Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
SSRS 2008 R2 - grouped stacked column chart - possible?

Answered SSRS 2008 R2 - grouped stacked column chart - possible?

  • Monday, January 10, 2011 8:57 PM
     
     
    Sign In to Vote
    0
    I would like to created a grouped (or clustered) stacked column chart using SSRS 2008 R2. I can created a grouped column chart and a stacked chart using SSR2, but it is not clear if I can created a 'grouped, stacked column.' Here is a link to an visual example using Excel (published by Peltier Technical Services -  http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html).  I would like this type of effect using SSRS. Can it be done?
     

All Replies

  • Tuesday, January 11, 2011 2:01 AM
     
     Proposed Has Code
    Sign In to Vote
    0

    Hi,

    I'm afraid whether SSRS provides the option to create Grouped-Stacked Column chart, But we can achieve the nearest look like that.
    May be this would help you. Please let us know

    <?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
 <Body>
  <ReportItems>
   <Chart Name="Chart3">
    <ChartCategoryHierarchy>
     <ChartMembers>
      <ChartMember>
       <Group Name="Chart3_CategoryGroup">
        <GroupExpressions>
         <GroupExpression>=Fields!Conti.Value</GroupExpression>
        </GroupExpressions>
       </Group>
       <SortExpressions>
        <SortExpression>
         <Value>=Fields!Conti.Value</Value>
        </SortExpression>
       </SortExpressions>
       <ChartMembers>
        <ChartMember>
         <Group Name="Chart3_CategoryGroup1">
          <GroupExpressions>
           <GroupExpression>=Fields!year.Value</GroupExpression>
          </GroupExpressions>
         </Group>
         <SortExpressions>
          <SortExpression>
           <Value>=Fields!year.Value</Value>
          </SortExpression>
         </SortExpressions>
         <Label>=Fields!year.Value</Label>
        </ChartMember>
       </ChartMembers>
       <Label>=Fields!Conti.Value</Label>
      </ChartMember>
     </ChartMembers>
    </ChartCategoryHierarchy>
    <ChartSeriesHierarchy>
     <ChartMembers>
      <ChartMember>
       <Group Name="Chart3_SeriesGroup">
        <GroupExpressions>
         <GroupExpression>=Fields!Quarter.Value</GroupExpression>
        </GroupExpressions>
       </Group>
       <SortExpressions>
        <SortExpression>
         <Value>=Fields!Quarter.Value</Value>
        </SortExpression>
       </SortExpressions>
       <Label>=Fields!Quarter.Value</Label>
      </ChartMember>
     </ChartMembers>
    </ChartSeriesHierarchy>
    <ChartData>
     <ChartSeriesCollection>
      <ChartSeries Name="Sales">
       <ChartDataPoints>
        <ChartDataPoint>
         <ChartDataPointValues>
          <Y>=Sum(Fields!Sales.Value)</Y>
         </ChartDataPointValues>
         <ChartDataLabel>
          <Style />
         </ChartDataLabel>
         <Style />
         <ChartMarker>
          <Style />
         </ChartMarker>
         <DataElementOutput>Output</DataElementOutput>
        </ChartDataPoint>
       </ChartDataPoints>
       <Subtype>Stacked</Subtype>
       <Style />
       <ChartEmptyPoints>
        <Style />
        <ChartMarker>
         <Style />
        </ChartMarker>
        <ChartDataLabel>
         <Style />
        </ChartDataLabel>
       </ChartEmptyPoints>
       <ValueAxisName>Primary</ValueAxisName>
       <CategoryAxisName>Primary</CategoryAxisName>
       <ChartSmartLabel>
        <CalloutLineColor>Black</CalloutLineColor>
        <MinMovingDistance>0pt</MinMovingDistance>
       </ChartSmartLabel>
      </ChartSeries>
     </ChartSeriesCollection>
    </ChartData>
    <ChartAreas>
     <ChartArea Name="Default">
      <ChartCategoryAxes>
       <ChartAxis Name="Primary">
        <Style>
         <FontSize>8pt</FontSize>
        </Style>
        <ChartAxisTitle>
         <Caption>Axis Title</Caption>
         <Style>
          <FontSize>8pt</FontSize>
         </Style>
        </ChartAxisTitle>
        <ChartMajorGridLines>
         <Enabled>False</Enabled>
         <Style>
          <Border>
           <Color>Gainsboro</Color>
          </Border>
         </Style>
        </ChartMajorGridLines>
        <ChartMinorGridLines>
         <Style>
          <Border>
           <Color>Gainsboro</Color>
           <Style>Dotted</Style>
          </Border>
         </Style>
        </ChartMinorGridLines>
        <ChartMinorTickMarks>
         <Length>0.5</Length>
        </ChartMinorTickMarks>
        <CrossAt>NaN</CrossAt>
        <Minimum>NaN</Minimum>
        <Maximum>NaN</Maximum>
        <ChartAxisScaleBreak>
         <Style />
        </ChartAxisScaleBreak>
       </ChartAxis>
       <ChartAxis Name="Secondary">
        <Style>
         <FontSize>8pt</FontSize>
        </Style>
        <ChartAxisTitle>
         <Caption>Axis Title</Caption>
         <Style>
          <FontSize>8pt</FontSize>
         </Style>
        </ChartAxisTitle>
        <ChartMajorGridLines>
         <Enabled>False</Enabled>
         <Style>
          <Border>
           <Color>Gainsboro</Color>
          </Border>
         </Style>
        </ChartMajorGridLines>
        <ChartMinorGridLines>
         <Style>
          <Border>
           <Color>Gainsboro</Color>
           <Style>Dotted</Style>
          </Border>
         </Style>
        </ChartMinorGridLines>
        <ChartMinorTickMarks>
         <Length>0.5</Length>
        </ChartMinorTickMarks>
        <CrossAt>NaN</CrossAt>
        <Location>Opposite</Location>
        <Minimum>NaN</Minimum>
        <Maximum>NaN</Maximum>
        <ChartAxisScaleBreak>
         <Style />
        </ChartAxisScaleBreak>
       </ChartAxis>
      </ChartCategoryAxes>
      <ChartValueAxes>
       <ChartAxis Name="Primary">
        <Style>
         <FontSize>8pt</FontSize>
        </Style>
        <ChartAxisTitle>
         <Caption>Axis Title</Caption>
         <Style>
          <FontSize>8pt</FontSize>
         </Style>
        </ChartAxisTitle>
        <ChartMajorGridLines>
         <Style>
          <Border>
           <Color>Gainsboro</Color>
          </Border>
         </Style>
        </ChartMajorGridLines>
        <ChartMinorGridLines>
         <Style>
          <Border>
           <Color>Gainsboro</Color>
           <Style>Dotted</Style>
          </Border>
         </Style>
        </ChartMinorGridLines>
        <ChartMinorTickMarks>
         <Length>0.5</Length>
        </ChartMinorTickMarks>
        <CrossAt>NaN</CrossAt>
        <Minimum>NaN</Minimum>
        <Maximum>NaN</Maximum>
        <ChartAxisScaleBreak>
         <Style />
        </ChartAxisScaleBreak>
       </ChartAxis>
       <ChartAxis Name="Secondary">
        <Style>
         <FontSize>8pt</FontSize>
        </Style>
        <ChartAxisTitle>
         <Caption>Axis Title</Caption>
         <Style>
          <FontSize>8pt</FontSize>
         </Style>
        </ChartAxisTitle>
        <ChartMajorGridLines>
         <Style>
          <Border>
           <Color>Gainsboro</Color>
          </Border>
         </Style>
        </ChartMajorGridLines>
        <ChartMinorGridLines>
         <Style>
          <Border>
           <Color>Gainsboro</Color>
           <Style>Dotted</Style>
          </Border>
         </Style>
        </ChartMinorGridLines>
        <ChartMinorTickMarks>
         <Length>0.5</Length>
        </ChartMinorTickMarks>
        <CrossAt>NaN</CrossAt>
        <Location>Opposite</Location>
        <Minimum>NaN</Minimum>
        <Maximum>NaN</Maximum>
        <ChartAxisScaleBreak>
         <Style />
        </ChartAxisScaleBreak>
       </ChartAxis>
      </ChartValueAxes>
      <Style>
       <BackgroundGradientType>None</BackgroundGradientType>
      </Style>
     </ChartArea>
    </ChartAreas>
    <ChartLegends>
     <ChartLegend Name="Default">
      <Style>
       <BackgroundGradientType>None</BackgroundGradientType>
       <FontSize>8pt</FontSize>
      </Style>
      <ChartLegendTitle>
       <Caption />
       <Style>
        <FontSize>8pt</FontSize>
        <FontWeight>Bold</FontWeight>
        <TextAlign>Center</TextAlign>
       </Style>
      </ChartLegendTitle>
      <HeaderSeparatorColor>Black</HeaderSeparatorColor>
      <ColumnSeparatorColor>Black</ColumnSeparatorColor>
     </ChartLegend>
    </ChartLegends>
    <ChartTitles>
     <ChartTitle Name="Default">
      <Caption>Chart Title</Caption>
      <Style>
       <BackgroundGradientType>None</BackgroundGradientType>
       <FontWeight>Bold</FontWeight>
       <TextAlign>General</TextAlign>
       <VerticalAlign>Top</VerticalAlign>
      </Style>
     </ChartTitle>
    </ChartTitles>
    <Palette>BrightPastel</Palette>
    <ChartBorderSkin>
     <Style>
      <BackgroundColor>Gray</BackgroundColor>
      <BackgroundGradientType>None</BackgroundGradientType>
      <Color>White</Color>
     </Style>
    </ChartBorderSkin>
    <ChartNoDataMessage Name="NoDataMessage">
     <Caption>No Data Available</Caption>
     <Style>
      <BackgroundGradientType>None</BackgroundGradientType>
      <TextAlign>General</TextAlign>
      <VerticalAlign>Top</VerticalAlign>
     </Style>
    </ChartNoDataMessage>
    <DataSetName>DataSet4</DataSetName>
    <Top>0.31125in</Top>
    <Left>0.67584in</Left>
    <Height>2in</Height>
    <Width>5.21875in</Width>
    <Style>
     <Border>
      <Color>LightGrey</Color>
      <Style>Solid</Style>
     </Border>
     <BackgroundColor>White</BackgroundColor>
     <BackgroundGradientType>None</BackgroundGradientType>
    </Style>
   </Chart>
  </ReportItems>
  <Height>2.60291in</Height>
  <Style />
 </Body>
 <Width>7.2175in</Width>
 <Page>
  <LeftMargin>1in</LeftMargin>
  <RightMargin>1in</RightMargin>
  <TopMargin>1in</TopMargin>
  <BottomMargin>1in</BottomMargin>
  <Style />
 </Page>
 <AutoRefresh>0</AutoRefresh>
 <DataSources>
  <DataSource Name="DataSource1">
   <DataSourceReference>AdventureWorks</DataSourceReference>
   <rd:SecurityType>None</rd:SecurityType>
   <rd:DataSourceID>20a94d6b-a40a-4b51-bf0d-e92d2d81d3a2</rd:DataSourceID>
  </DataSource>
 </DataSources>
 <DataSets>
  <DataSet Name="DataSet1">
   <Query>
    <DataSourceName>DataSource1</DataSourceName>
    <CommandText>SELECT    1 AS SeasonID, 'Spring' AS Season, 100 AS Val, 100 AS Val1
UNION
SELECT    2 AS SeasonID, 'Summer' AS Season, 200 AS Val, 200 AS Val1
UNION
SELECT    3 AS SeasonID, 'Fall' AS Season, 300 AS Val, 300 AS Val1
UNION
SELECT    4 AS SeasonID, 'Winter' AS Season, 400 AS Val, 400 AS Val1</CommandText>
   </Query>
   <Fields>
    <Field Name="Season">
     <DataField>Season</DataField>
     <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="SeasonID">
     <DataField>SeasonID</DataField>
     <rd:TypeName>System.Int32</rd:TypeName>
    </Field>
    <Field Name="Val">
     <DataField>Val</DataField>
     <rd:TypeName>System.Int32</rd:TypeName>
    </Field>
    <Field Name="Val1">
     <DataField>Val1</DataField>
     <rd:TypeName>System.Int32</rd:TypeName>
    </Field>
   </Fields>
  </DataSet>
  <DataSet Name="DataSet2">
   <Query>
    <DataSourceName>DataSource1</DataSourceName>
    <CommandText>SELECT    1 AS K, 'A' AS val
UNION
SELECT    1 AS K, 'B' AS val
UNION
SELECT    1 AS K, 'C' AS val
UNION
SELECT    2 AS K, 'C' AS val</CommandText>
   </Query>
   <Fields>
    <Field Name="K">
     <DataField>K</DataField>
     <rd:TypeName>System.Int32</rd:TypeName>
    </Field>
    <Field Name="val">
     <DataField>val</DataField>
     <rd:TypeName>System.String</rd:TypeName>
    </Field>
   </Fields>
  </DataSet>
  <DataSet Name="DataSet3">
   <Query>
    <DataSourceName>DataSource1</DataSourceName>
    <CommandText>select 1 as EmpID
union
select 2 as EmpID
union
select 3 as EmpID
union
select 4 as EmpID
union
select 5 as EmpID
union
select 100 as EmpID</CommandText>
   </Query>
   <Fields>
    <Field Name="EmpID">
     <DataField>EmpID</DataField>
     <rd:TypeName>System.Int32</rd:TypeName>
    </Field>
   </Fields>
  </DataSet>
  <DataSet Name="DataSet4">
   <Query>
    <DataSourceName>DataSource1</DataSourceName>
    <CommandText>SELECT    'Asia' AS Conti, '2001Q1' AS Quarter, 20 AS Sales,'2001'as year
UNION
SELECT    'Asia' AS Conti, '2001Q2' AS Quarter, 20 AS Sales,'2001'as year
UNION
SELECT    'Asia' AS Conti, '2001Q3' AS Quarter, 20 AS Sales,'2001'as year
UNION
SELECT    'Asia' AS Conti, '2001Q4' AS Quarter, 20 AS Sales,'2001'as year
UNION
SELECT    'Europe' AS Conti, '2001Q1' AS Quarter, 20 AS Sales,'2001'as year
UNION
SELECT    'Europe' AS Conti, '2001Q2' AS Quarter, 20 AS Sales,'2001'as year
UNION
SELECT    'Europe' AS Conti, '2001Q3' AS Quarter, 20 AS Sales,'2001'as year
UNION
SELECT    'Europe' AS Conti, '2001Q4' AS Quarter, 20 AS Sales,'2001'as year
union
SELECT    'Asia' AS Conti, '2002Q1' AS Quarter, 20 AS Sales,'2002'as year
UNION
SELECT    'Asia' AS Conti, '2002Q2' AS Quarter, 20 AS Sales,'2002'as year
UNION
SELECT    'Asia' AS Conti, '2002Q3' AS Quarter, 20 AS Sales,'2002'as year
UNION
SELECT    'Asia' AS Conti, '2002Q4' AS Quarter, 20 AS Sales,'2002'as year
UNION
SELECT    'Europe' AS Conti, '2002Q1' AS Quarter, 20 AS Sales,'2002'as year
UNION
SELECT    'Europe' AS Conti, '2002Q2' AS Quarter, 20 AS Sales,'2002'as year
UNION
SELECT    'Europe' AS Conti, '2002Q3' AS Quarter, 20 AS Sales,'2002'as year
UNION
SELECT    'Europe' AS Conti, '2002Q4' AS Quarter, 20 AS Sales,'2002'as year</CommandText>
   </Query>
   <Fields>
    <Field Name="Conti">
     <DataField>Conti</DataField>
     <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="Quarter">
     <DataField>Quarter</DataField>
     <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="Sales">
     <DataField>Sales</DataField>
     <rd:TypeName>System.Int32</rd:TypeName>
    </Field>
    <Field Name="year">
     <DataField>year</DataField>
     <rd:TypeName>System.String</rd:TypeName>
    </Field>
   </Fields>
  </DataSet>
 </DataSets>
 <rd:ReportUnitType>Inch</rd:ReportUnitType>
 <rd:ReportID>ca0482aa-d723-4c9e-98eb-9ca50b6dba90</rd:ReportID>
</Report>

    Thanks

    Please click the 'Mark as Answer' button if my Reply helped you to solve your problem! Thanks M.Mahendra
     
  • Tuesday, January 11, 2011 8:52 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Jmcquil,

    You could give two Category groups to the stacked column chart, then you will get a more clear report.

    Challen Fu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

     
  • Tuesday, January 11, 2011 10:00 PM
     
     Answered
    Sign In to Vote
    0
    Thank you both for your replies. My solution is not ideal but it works, I added 'blank' data into my data series which creates the desired space between my columns and visually offsets the real data into 'groups.' I also reduced the PointWidth property to .9 to further the visual effect. Thank you again.
    • Marked As Answer by jmcquil Tuesday, January 11, 2011 10:00 PM
    •  
     
  • Monday, November 28, 2011 12:47 AM
     
     
    Sign In to Vote
    0
    Can you share printshot of your end result (graph). Appreciate your response.
    Sarang
     
  • Wednesday, December 14, 2011 11:45 PM
     
     Proposed Has Code
    Sign In to Vote
    1

    This is actually much easier to do in SSRS than in Excel, as long as you only want two columns per group. (As far as I can tell, the Excel solution linked to above will also only allow two columns per group). The approach is to associate the first column in the group with the chart's primary axis and associate the second column with the secondary axis. This will make the two columns adjacent when both axes are defined the same way.

    Follow these steps: (*Note that I'm working in SSRS Denali CTP3, so your menu options may be a little different)

    1. Create a report. Add the applicable data source and dataset. Here's a little t-sql query you can run if you'd like to try this with the data from the Excel example:

    declare @Revenue table (Product varchar(10), Q1Actual smallint, Q2Actual smallint, Q1Budget smallint, Q2Budget smallint)

insert into @Revenue
values('Coffee',1900,2200,2000,2000),
	  ('Tea',1600,1650,1500,1500),
	  ('Milk',1000,950,1200,1200)

select * from @Revenue

    2. Add a chart to the report. Select the stacked column type.

    3. In the Chart Data box, add the numeric columns under Values. We'll add Q1Actual, Q2Actual, Q1Budget, and Q2Budget. Also change the Category Group to be the appropriate group column, Product in our case.

    4. Right-click on Q1Budget series in the chart (or left-click on the down arrow for the series in the Chart Data box) and select Series Properties. Under Axes and Chart Area, there is a Horizontal Axis setting. Set this to Secondary and click OK.

    5. Repeat step 4 for the Q2Budget series.

     

    If you preview now, it should be pretty close to what you're going for. There are a few more formatting things I'd recommend at this point, but they're optional:

    i) Right-click on the top axis and uncheck 'Show Axis Title'. Also uncheck 'Show Axis'.

    ii) Adjust the colors for each of your defined series.

    iii) Turn off the tick marks on the horizontal axis, as they look a bit awkward between the columns. Right-click on the horizontal axis and select Horizontal Axis Properties. Under Major Tick Marks, check the box that says 'Hide major tick marks'.

    iv) Adjust the min, max, and scale for your vertical axis. If you keep it set to Auto, it appears to adjust to the original ungrouped, stacked chart, so it'll have a lot of white space at the top. To set these properties, right-click on the vertical axis and choose Vertical Axis Properties. You may want to come up with a formula fitted to your data for the Minimum, Maximum, and Interval settings. For the sake of brevity, I've set these to 0, 5000, and 1000 respectively.

     

    And this is what we get:

    • Proposed As Answer by C Birkett Wednesday, October 24, 2012 8:07 AM
    •  
     
  • Monday, July 02, 2012 8:24 PM
     
     Proposed
    Sign In to Vote
    1

    Hey,

    I realize this is an old post but I've been running all over the internet looking for a way to do this and just happened to stumble across a way playing around in SSRS 2008. Here is what I did:

    1.) Create a 3D stacked column graph

    2.) Right click the graph select either "3D Effects

    3.) Enable Series clustering

    4.) Add your category groups, the sub group should be added second.

    5.) Change chart type back to a 2D Graph

    Hope this helps.

    • Proposed As Answer by EitanBlumin Thursday, April 25, 2013 8:21 AM
    •  
     
  • Friday, December 21, 2012 3:23 PM
     
     
    Sign In to Vote
    0
    Hi Eddie_E - I'm having a little trouble following your directions.  I'm trying to create a graph with 3 stacked columns (each column having two series).  Any advice would be appreciated.
     
  • Monday, January 28, 2013 7:28 PM
     
     
    Sign In to Vote
    0
    You can actually get four stacks, but using both vertical and both horizontal axes.