Wednesday, March 28, 2012

Grouping problem

I need to provide a summary row at the bottom of each group that sums accounts of a specific type and subtracts it from the group total.

Acct Balance Prior Balance

A 10 5

B 4 8

C 7 6

D 9 12

Total 30 31

exp accts 13 20

Final 17 11

Is there an easy way to do this in reporting services?

So you want to perform a conditional sum based on another field (account type) in the dataset?

You need add some additional rows to the group footer and put an Iif statement inside the Sum that returns the value to aggregate for a match and 0 otherwise e.g.

Sum(Iif(Fields!account_type.Value = "exclude", Fields!Balance.value, 0))

Then in the last row subtract one row from the other.

The following is RDL code for an example:

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="master">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=.;Initial Catalog=master</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>2dd60f19-bf57-4e66-80c3-288d239d3f80</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>2.5cm</BottomMargin>

<RightMargin>2.5cm</RightMargin>

<PageWidth>21cm</PageWidth>

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

<InteractiveWidth>21cm</InteractiveWidth>

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

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

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Table Name="table1">

<Footer>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox7">

<rd:DefaultName>textbox7</rd:DefaultName>

<ZIndex>5</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>Total</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="balance_1">

<rd:DefaultName>balance_1</rd:DefaultName>

<ZIndex>4</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

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

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="prior_balance_1">

<rd:DefaultName>prior_balance_1</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

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

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox4">

<rd:DefaultName>textbox4</rd:DefaultName>

<ZIndex>8</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>exc accts</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox5">

<rd:DefaultName>textbox5</rd:DefaultName>

<ZIndex>7</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox6">

<rd:DefaultName>textbox6</rd:DefaultName>

<ZIndex>6</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox8">

<rd:DefaultName>textbox8</rd:DefaultName>

<ZIndex>11</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>Final</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox9">

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

<ZIndex>10</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox10">

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

<ZIndex>9</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

</TableRows>

</Footer>

<DataSetName>DataSet1</DataSetName>

<Top>1.5cm</Top>

<Width>6.5cm</Width>

<Details>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="account">

<rd:DefaultName>account</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

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

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="balance">

<rd:DefaultName>balance</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

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

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="prior_balance">

<rd:DefaultName>prior_balance</rd:DefaultName>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

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

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

</TableRows>

</Details>

<Header>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox1">

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

<ZIndex>14</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>account</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox2">

<rd:DefaultName>textbox2</rd:DefaultName>

<ZIndex>13</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>balance</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox3">

<rd:DefaultName>textbox3</rd:DefaultName>

<ZIndex>12</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>prior balance</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

</TableRows>

</Header>

<TableColumns>

<TableColumn>

<Width>2cm</Width>

</TableColumn>

<TableColumn>

<Width>1.75cm</Width>

</TableColumn>

<TableColumn>

<Width>2.75cm</Width>

</TableColumn>

</TableColumns>

<Height>3.1746cm</Height>

</Table>

</ReportItems>

<Height>5cm</Height>

</Body>

<rd:ReportID>2e9a4c57-83bc-4a17-9dcd-287d47e72e3d</rd:ReportID>

<LeftMargin>2.5cm</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select 'A' as account, 1 as type, 10 as balance, 5 as prior_balance

union

select 'B', 2, 4, 8

union

select 'C', 1, 7, 6

union

select 'D', 2, 9, 12</CommandText>

<DataSourceName>master</DataSourceName>

</Query>

<Fields>

<Field Name="account">

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

<DataField>account</DataField>

</Field>

<Field Name="type">

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

<DataField>type</DataField>

</Field>

<Field Name="balance">

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

<DataField>balance</DataField>

</Field>

<Field Name="prior_balance">

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

<DataField>prior_balance</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>16cm</Width>

<InteractiveHeight>29.7cm</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

No comments:

Post a Comment