sql server reporting services: Calculating totals for second group level -- sql server reporting services

Calculating totals for second group level

Jon
10/10/2005 9:15:13 AM

sql server reporting services: I have a report that shows Customers, Categories, and Quantity, grouped
by Customers and Categories. (I'm using the Northwind db to try to
figure this out.) I am able to get a grand total in the header for All
Customers and All Categories, but not for each of the categories.

Does anybody know how to do this?

Current Report:
Customer Category Qty
All Customers All Categories 51,317
Alf. Futterk. 174
Beverages 36
Condiments 44
Dairy 35
....

Desired Report:
Customer Category Qty
All Customers All Categories 51,317
Beverages 9,532
Condiments 5,298
Dairy 9,149

Alf. Futterk. 174
Beverages 36
Condiments 44
Dairy 35
....

rdl for existing report:
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini..."xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>3pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<FontSize>16pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.3in</Height>
<CanGrow>true</CanGrow>
<Value>Northwind Subtotals</Value>
</Textbox>
<Table Name="table1">
<Style>
<BorderWidth>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Customer"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Category"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Quantity</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>

Re: Calculating totals for second group level

Robert Bruckner [MSFT]
10/10/2005 7:05:49 PM

Try this:
* in the second table header, add a list with a details grouping based on
categories
* put two textboxes in that list, one for the category name, the other for
the Sum(...)

I attached the updated report at the bottom of this posting.

-- 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>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>3pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<FontSize>16pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.3in</Height>
<Value>Northwind Subtotals</Value>
<CanGrow>true</CanGrow>
</Textbox>
<Table Name="table1">
<Style>
<BorderWidth>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Customer"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Category"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>