Sum of a Sum (expression)

Hot Topic

Clearing the Error List window
Currency Symbol Problem
How to fix postion of report itme?
Dynamic query: EXEC - Need to get a return value from query
Returning Subsets of the same column in separate columns...
Log Reader Agent Failure
Going From SQL Server 2000 to 2005
how to modify this sql code
More problems with activation
Using Custom Classes

SQL Server

Row Number Tranformation
Displaying huge heirarchial data in reports
Reusing a Chached Lookup component
Routine crashing of Visual Studio 2005 when running or editing SSIS
Replication Montor 2005
how to convert ms access queries into sql server procedure
Problem with managed code simple example "Target string size is too small to represent the XML instance"
Configuration Files
SCD Insert and Identity Columns
CLR Enabled

Sum of a Sum (expression)

Product Category Product Sub Category Product Name Units Sold Bonus  Units
Accessory     62051
  Bike Racks   3166 2000
    Hitch rack - 4 bike 3166  
  Bike Stand   249 249
    All-purpose bike stand 249  
  Bottles & Cages   10552 2000
    water bottle 30 oz 6815  
    Mountain bottle cage 2025  
    Road bottle cage 1712  
  Cleaners   3344 2000
    Bike wash - dissolver 3344  

I want to get a total for Product Category.  Below is the rdl file based on the AdventureWorks2000 database.  The bonus units are calculated with an expression:

=iif(Sum(Fields!UnitsSold.Value)>2000, 2000, Sum(Fields!UnitsSold.Value))

This provides the total for ProductSubCategory. I now want a total for ProductCategory.
How can I get a total at the Product Category level. Can someone help me.

Everything I try results in a scope error. Below is the source rdl file.


< 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">

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<RightMargin>2.5cm</RightMargin>

<Body>

<ReportItems>

<Textbox Name="textbox1">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<BackgroundColor>Brown</BackgroundColor>

<BorderWidth>

<Bottom>3pt</Bottom>

</BorderWidth>

<BorderColor>

<Bottom>Black</Bottom>

</BorderColor>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<FontSize>18pt</FontSize>

<TextAlign>Center</TextAlign>

<Color>Whiteb/Color>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>1</ZIndex>

<rd:DefaultName>textbox1</rd:DefaultName>

<Height>0.8381cm</Height>

<Width>17.75cmb/Width>

<CanGrow>true</CanGrow>

<Value>Totals</Value>

</Textbox>

<Table Name="table1">

<Style>

<BackgroundColor>White</BackgroundColor>

<BorderWidth>

<Top>3pt</Top>

</BorderWidth>

<BorderStyle>

<Top>Solidb/Top>

</BorderStyle>

</Style>

<Header>

<TableRows>

<TableRow>

<Height>0.53333cm</Height>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox2">

bStyle>

<PaddingLeft>2pt</PaddingLeft>

<BorderWidth>

<Bottom>2pt</Bottom>

</BorderWidth>

<BorderColor>

<Bottom>DarkRed</Bottom>

</BorderColor>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>900</FontWeight>

b/Style>

bZIndex>19</ZIndex>

brd:DefaultName>textbox2</rd:DefaultName>

bCanGrow>true</CanGrow>

bValue>Product Category</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox4">

bStyle>

<PaddingLeft>2pt</PaddingLeft>

<BorderWidth>

<Bottom>2pt</Bottom>

</BorderWidth>

<BorderColor>

<Bottom>DarkRed</Bottom>

</BorderColor>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>900</FontWeight>

b/Style>

bZIndex>18</ZIndex>

brd:DefaultName>textbox4</rd:DefaultName>

bCanGrow>true</CanGrow>

bValue>Product Sub Category</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox7">

bStyle>

<PaddingLeft>2pt</PaddingLeft>

<BorderWidth>

<Bottom>2pt</Bottom>

</BorderWidth>

<BorderColor>

<Bottom>DarkRed</Bottom>

</BorderColor>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>900</FontWeight>

b/Style>

bZIndex>17</ZIndex>

brd:DefaultName>textbox7</rd:DefaultName>

bCanGrow>true</CanGrow>

bValue>Product Name</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox8">

bStyle>

<PaddingLeft>2pt</PaddingLeft>

<BorderWidth>

<Bottom>2pt</Bottom>

</BorderWidth>

<BorderColor>

<Bottom>DarkRed</Bottom>

</BorderColor>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>900</FontWeight>

b/Style>

bZIndex>16</ZIndex>

brd:DefaultName>textbox8</rd:DefaultName>

bCanGrow>true</CanGrow>

bValue>Units Sold</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox6">

bStyle>

<PaddingLeft>2pt</PaddingLeft>

<BorderWidth>

<Bottom>2pt</Bottom>

</BorderWidth>

<BorderColor>

<Bottom>DarkRed</Bottom>

</BorderColor>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>900</FontWeight>

b/Style>

bZIndex>15</ZIndex>

brd:DefaultName>textbox6</rd:DefaultName>

bCanGrow>true</CanGrow>

bValue>Bonus Units</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

<RepeatOnNewPage>true</RepeatOnNewPage>

</Header>

<Details>

<TableRows>

<TableRow>

<Height>0.53333cm</Height>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox3">

bStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

b/Style>

bZIndex>4</ZIndex>

brd:DefaultName>textbox3</rd:DefaultName>

bCanGrow>true</CanGrow>

bValue />

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox5">

bStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

b/Style>

bZIndex>3</ZIndex>

brd:DefaultName>textbox5</rd:DefaultName>

bCanGrow>true</CanGrow>

bValue />

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="ProductName">

bStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

b/Style>

bZIndex>2</ZIndex>

brd:DefaultName>ProductName</rd:DefaultName>

bCanGrow>true</CanGrow>

bValue>=Fields!ProductName.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="UnitsSold">

bStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

b/Style>

bZIndex>1</ZIndex>

brd:DefaultName>UnitsSold</rd:DefaultName>

bCanGrow>true</CanGrow>

bValue>=Fields!UnitsSold.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox18">

bStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

b/Style>

brd:DefaultName>textbox18</rd:DefaultName>

bCanGrow>true</CanGrow>

bValue />

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Details>

<DataSetName>AdventureWorks2000</DataSetName>

<Top>0.8381cm</Top>

<Width>17.78968cm</Width>

<TableGroups>

<TableGroup>

<Header>

<TableRows>

<TableRow>

<Height>0.60952cm</Height>

<TableCells>

<TableCell>

bReportItems>

<Textbox Name="ProductCategory">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<BackgroundColor>Gray</BackgroundColor>

<Color>Brown</Color>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>14</ZIndex>

<rd:DefaultName>ProductCategory</rd:DefaultName>

<CanGrow>true</CanGrow>

<Value>=Fields!ProductCategory.Value</Value>

</Textbox>

b/ReportItems>

</TableCell>

<TableCell>

bReportItems>

<Textbox Name="textbox9">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<BackgroundColor>Gray</BackgroundColor>

<FontSize>12pt</FontSize>

<Color>Brown</Color>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>13</ZIndex>

<rd:DefaultName>textbox9</rd:DefaultName>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

b/ReportItems>

</TableCell>

<TableCell>

bReportItems>

<Textbox Name="textbox11">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<BackgroundColor>Gray</BackgroundColor>

<FontSize>12pt</FontSize>

<Color>Brown</Color>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>12</ZIndex>

<rd:DefaultName>textbox11</rd:DefaultName>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

b/ReportItems>

</TableCell>

<TableCell>

bReportItems>

<Textbox Name="textbox12">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<BackgroundColor>Gray</BackgroundColor>

<Color>Brown</Color>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>11</ZIndex>

<rd:DefaultName>textbox12</rd:DefaultName>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!UnitsSold.Value)</Value>

</Textbox>

b/ReportItems>

</TableCell>

<TableCell>

bReportItems>

<Textbox Name="textbox10">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<BackgroundColor>Gray</BackgroundColor>

<FontSize>12pt</FontSize>

<TextAlign>Right</TextAlign>

<Color>Red</Color>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>10</ZIndex>

<rd:DefaultName>textbox10</rd:DefaultName>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

b/ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<Grouping Name="table1_ProductCategory">

<GroupExpressions>

<GroupExpression>=Fields!ProductCategory.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<Sorting>

<SortBy>

<SortExpression>=Fields!ProductCategory.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

</TableGroup>

<TableGroup>

<Header>

<TableRows>

<TableRow>

<Height>0.60952cm</Height>

<TableCells>

<TableCell>

bReportItems>

<Textbox Name="textbox13">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<BackgroundColor>Gainsboro</BackgroundColor>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>9</ZIndex>

<rd:DefaultName>textbox13</rd:DefaultName>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

b/ReportItems>

</TableCell>

<TableCell>

bReportItems>

<Textbox Name="ProductSubCategory">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<BackgroundColor>Gainsboro</BackgroundColor>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>8</ZIndex>

<rd:DefaultName>ProductSubCategory</rd:DefaultName>

<CanGrow>true</CanGrow>

<Value>=Fields!ProductSubCategory.Value</Value>

</Textbox>

b/ReportItems>

</TableCell>

<TableCell>

bReportItems>

<Textbox Name="textbox15">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<BackgroundColor>Gainsboro</BackgroundColor>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>7</ZIndex>

<rd:DefaultName>textbox15</rd:DefaultName>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

b/ReportItems>

</TableCell>

<TableCell>

bReportItems>

<Textbox Name="textbox16">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<BackgroundColor>Gainsboro</BackgroundColor>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>6</ZIndex>

<rd:DefaultName>textbox16</rd:DefaultName>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!UnitsSold.Value)</Value>

</Textbox>

b/ReportItems>

</TableCell>

<TableCell>

bReportItems>

<Textbox Name="textbox14">

<Style>

<PaddingLeft>2pt</PaddingLeft>

<BackgroundColor>Gainsboro</BackgroundColor>

<PaddingBottom>2pt</PaddingBottom>

<PaddingTop>2pt</PaddingTop>

<PaddingRight>2pt</PaddingRight>

<FontWeight>700</FontWeight>

</Style>

<ZIndex>5</ZIndex>

<rd:DefaultName>textbox14</rd:DefaultName>

<CanGrow>true</CanGrow>

<Value>=iif(Sum(Fields!UnitsSold.Value)&gt;2000, 2000, Sum(Fields!UnitsSold.Value))</Value>

</Textbox>

b/ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<Grouping Name="table1_ProductSubCategory">

<GroupExpressions>

<GroupExpression>=Fields!ProductSubCategory.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<Sorting>

<SortBy>

<SortExpression>=Fields!ProductSubCategory.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

</TableGroup>

</TableGroups>

<TableColumns>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

<TableColumn>

<Width>3.5cm</Width>

</TableColumn>

<TableColumn>

<Width>3.25cm</Width>

</TableColumn>

<TableColumn>

<Width>4.25cm</Width>

</TableColumn>

<TableColumn>

<Width>4.25cm</Width>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

<Style />

<Height>3.12381cm</Height>

<ColumnSpacing>1cmb/ColumnSpacing>

</Body>

<TopMargin>2.5cm</TopMargin>

<DataSources>

<DataSource Name="AdventureWorks2000">

<rd:DataSourceID>e4edfdc3-2c66-45a8-a714-7d432cce1447</rd:DataSourceID>

<DataSourceReference>AdventureWorks2000</DataSourceReference>

</DataSource>

</DataSources>

<Width>18.25cm</Width>

<DataSets>

<DataSet Name="AdventureWorks2000">

<Fields>

<Field Name="ProductID">

<DataField>ProductID</DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Field Name="ProductCategory">

<DataField>ProductCategory</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="ProductSubCategory">

<DataField>ProductSubCategory</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="ProductName">

<DataField>ProductName</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="UnitsSold">

<DataField>UnitsSold</DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

<Query>

<DataSourceName>AdventureWorks2000</DataSourceName>

<CommandText>SELECT b a.ProductID, a.ProductCategory, a.ProductSubCategory, a.ProductName, SUM(OrderQty) AS UnitsSold

FROM vwProductListing a, vwSalesOrderDetail b

WHERE a.ProductID = b.ProductID

GROUP BY a.ProductID, a.ProductName, a.ProductSubCategory, a.ProductCategory</CommandText>

</Query>

</DataSet>

</DataSets>

<LeftMargin>2.5cm</LeftMargin>

<rd:SnapToGrid>true</rd:SnapToGrid>

<PageHeight>29.7cm</PageHeight>

<rd:DrawGrid>true</rd:DrawGrid>

<PageWidth>21cm</PageWidth>

<rd:ReportID>50d9efd1-c6af-41bd-88f0-501e89dfcc3a</rd:ReportID>

<BottomMargin>2.5cm</BottomMargin>

<Language>en-US</Language>

</Report>

Answer this question

Sum of a Sum (expression)

  • whooo

    put sum(field) on product category group footer.

  • Sum of a Sum (expression)
Answer this question