sql server reporting services: Stacked Column Chart Total -- sql server reporting services

You're in the

sql server reporting services

group:

Stacked Column Chart Total

jordanm NO[at]SPAM 37.com
3/30/2005 5:24:33 PM

sql server reporting services:

Hi,

I need to display the total of all series at the top of each stacked
column. E.g for Jan, sales for item 1 is 20, item 2 is 15 & item 3 is
50. Instead of showing 20, 15 and 50 respectively, I need to show the
total 85 at the top of the stacked column. However, I can't seemd to
find any way to achieve this.

Thanks.

Re: Stacked Column Chart Total

Robert Bruckner [MSFT]
3/30/2005 6:37:23 PM

One approach to do this is shown in the sample RDL copied to the bottom of
my posting. Just look at the DataPoint Label expression of the "Order" data
value in the sample.

-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

=======================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="newChart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>20</Inclination>
<Shading>Real</Shading>
<WallThickness>25</WallThickness>
<DrawingStyle>Cylinder</DrawingStyle>
<Clustered>true</Clustered>
</ThreeDProperties>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Firebrick</Color>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Excel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!UnitsInStock.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!UnitsOnOrder.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
<Value>=Sum(Fields!UnitsInStock.Value) +
Sum(Fields!UnitsOnOrder.Value)</Value>
<Visible>true</Visible>
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Caption>Products</Caption>
<Style />
</Title>
<Style>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style>
<BorderWidth>
<Default>1.5pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<MajorInterval>5</MajorInterval>
<MinorInterval>1</MinorInterval>
<CrossAt>0</CrossAt>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Column</Type>
<Top>0.25in</Top>
<Title>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Title>
<Width>5.625in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup2">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!CategoryName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProductName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>5.25in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Stock</Label>
</StaticMember>
<StaticMember>
<Label>Order</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Stacked</Subtype>
<PlotArea>
<Style>
<BackgroundColor>OldLace</BackgroundColor>
</Style>
</PlotArea>
<Left>0.25in</Left>
<ValueAxis>
<Axis>
<Title>
<Caption>Amount</Caption>
<Style />
</Title>
<Style>
<BorderColor>
<Default>IndianRed</Default>
</BorderColor>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>Blue</Default>
</BorderColor>
<BorderStyle>
<Default>Dashed</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>CornflowerBlue</Default>

Re: Stacked Column Chart Total

jordanm NO[at]SPAM 37.com
3/30/2005 7:40:02 PM

Hi,

Thanks for the pointer. I modify the XML file as per the sample RDL.
Instead of showing Total for the stacked column i.e. Item 1 + Item 2 +
Item 3, it only shows Item 3 at the top of the stacked column. So, i
pre-calculate the total in stored procedure and populate an additional
field, "Total_Sale". I then, set the <Value>=Fields!Total_Sale</Value>.
This seems to work alright but when Item 3 is zero, the total is being
displayed at point "0". Is there any way to correct this?

Thanks.

Jordan

[quoted text, click to view]
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn