Issue with SUM in Group and Report Footer

[SSRSRpt]

I have a simple 2005 SSRS report, an overview of Accounts grouped by date.
Every Date shows sum of RECEIPT and DISBURSEMENT for respective columns for all the account in group footer and total sum in report footer.
I want to have a total of 2 colums, both in group footer as in the table footer.


In attached SSRS 2005 report i am grouping by date and within date i'm showing acconuts.
In group footer i'm trying to do sum depends on TYPE field.
For Receipt Column
=IIf((Fields!Type1.Value= "R"),Sum(Fields!Amount.Value),"")
For Disbursement Column
=IIf((Fields!Type1.Value= "D"),Sum(Fields!Amount.Value),"")


But report is putting sum either in RECEIPT or in DISBURSEMENT column depends on whatever record is 1st.

For Receipt Column in report footer
=IIf((Fields!Type1.Value="R"), Sum(Fields!Amount.Value), "$0.00")
For Disbursement Column in report footer
=IIf((Fields!Type1.Value="D"), Sum(Fields!Amount.Value), "$0.00")

Same way page footer is not showing the correct value.
Instead of $257673.61, it should have been 257673.61-9340-2875.98 = $245457.63
And Disbursement column in report footer instead of $0.00 it should be $12215.98
Any Suggestions what i'm doing wrong here.
Thanks,
PS
Date / Account Receipt Disbursement
AUD 100 0
3/2/2009 100 0 Group Footer
AUD 120 80
SID 50 30
LID 40 20
3/4/2009 210 130 Group Footer
AUD 101 84
SID 54 34
LID 45 24
3/6/2009 200 142 Group Footer
AUD 155 85
SID 35 34
LID 37 25
3/10/2009 227 144 Group Footer
AUD 160 70
SID 150 55
LID 240 120
3/14/2009 550 245 Group Footer
Report Footer 1287 661

 

[RiverGuy]

Instead of this:

=IIf((Fields!Type1.Value= "R"),Sum(Fields!Amount.Value),"")

Try this:

=SUM(IIf(Fields!Type1.Value= "R", Fields!Amount.Value, 0))

 

[xlbo]

Dependant on your data types you may also need to convert to consistent data types:

=SUM(IIf(Fields!Type1.Value= "R", cdbl(Fields!Amount.Value), cdbl(0)))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question