Firehose mode: balance

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

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

</ConnectionProperties>

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

</DataSource>

</DataSources>

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

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

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

<Body>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>Total</Value>

</Textbox>

</ReportItems>

</TableCell>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

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

</Textbox>

</ReportItems>

</TableCell>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

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

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>exc accts</Value>

</Textbox>

</ReportItems>

</TableCell>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

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

</Textbox>

</ReportItems>

</TableCell>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

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

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>Final</Value>

</Textbox>

</ReportItems>

</TableCell>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

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

</Textbox>

</ReportItems>

</TableCell>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

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

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<DataSetName>DataSet1</DataSetName>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

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

</Textbox>

</ReportItems>

</TableCell>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

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

</Textbox>

</ReportItems>

</TableCell>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

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

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Details>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>account</Value>

</Textbox>

</ReportItems>

</TableCell>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>balance</Value>

</Textbox>

</ReportItems>

</TableCell>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>prior balance</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

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

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

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

<DataField>account</DataField>

</Field>

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

</Field>

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

<DataField>balance</DataField>

</Field>

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

<DataField>prior_balance</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

I'm trying to resolve the problem :
List the customer number, customer name, rep number, and credit balance for all customers whose credit balance is greater than the average credit balance for the customers who have the same rep. Sort by rep number. Use of 'exists' is required.
See the table behind . My query is

select customer_num, customer_name, rep_num, balance
from customer a
where exists
(select * from customer b
where balance >
(select AVG(balance) from customer c
where a.rep_num=b.rep_num)
and a.rep_num=b.rep_num)
order by rep_num;

but it lists all the customers from the table. Thank you.

CUS CUSTOMER_NAME STREET CITY ST ZIP BALANCE CREDIT_LIMIT RE
-- ------- ----- ---- -- -- ---- ---- --
148 Al's Appliance and S 2837 Fillmore FL 33336 6550 7,500 20
port Greenway

282 Brookings Direct 3827 Grove FL 33321 431.5 10,000 35
Devon

356 Ferguson's 382 Northfield FL 33146 5785 7,500 65
Wildwood

408 The Everything Shop 1828 Crystal FL 33503 5285.25 5,000 35
Raven

462 Bargains Galore 3829 Grove FL 33321 3412 10,000 65
Central

524 Kline's 838 Fillmore FL 33336 12762 15,000 20
Ridgeland

608 Johnson's Department 372 Sheldon FL 33553 2106 10,000 65
Store Oxford

687 Lee's Sport and Appl 282 Altonville FL 32543 2851 5,000 35
iance Evergreen

725 Deerfield's Four Sea 282 Sheldon FL 33553 248 7,500 35
sons Columbia

842 All Season 28 Lakeview Grove FL 33321 8221 7,500 20select customer_num, customer_name, rep_num, balance
from customer a
where a.balance > (select avg(balance) from customer where rep_num = a.rep_num);

Wednesday, March 28, 2012

Grouping problem

Monday, March 26, 2012

grouping by the avg

Firehose mode

Blog Archive

About Me