Subtotaling on IIF statements
I am VERY new to SSRS but I am trying to write an AR Aging report.
My report is working very well EXCEPT I can not figuring out how to create the subtotal for my COL5 COL6, COL7 and COL8 that contain IIF statements and SUM.
The expressions on the columns I am having a problem are as follows:
COLUMN 5: =IIf(Fields!AgingDays.Value <= 0, SUM(Fields!TransAmt.Value),
"")
COLUMN 6: =IIf((Fields!AgingDays.Value > 0
AND Fields!AgingDays.Value < 31), SUM(Fields!TransAmt.Value),
"")
COLUMN 7: =IIf((Fields!AgingDays.Value > 30
AND Fields!AgingDays.Value < 61), SUM(Fields!TransAmt.Value),
"")
COLUMN 8: =IIf((Fields!AgingDays.Value > 60), SUM(Fields!TransAmt.Value),
"")
Here is a sample of the report:
Column1
Col2
Col3
Col4
Col5
Col6 Col7
Col8
Customer
Invoice
DueDate
Amount
Current
1-30 31-60
61+
Adam Ant
A1234
5/1/2011
$100
$100
Tina Turner
B5678
4/1/2011
$200
$200
Elvis Presley
C9012
3/1/2011
$300
$300
Chuck Berry
D4456
5/15/2011
$50
$50
TOTAL
$650
$150 $200
$300
Can someone provide some ideas on how to write the EXPRESSION’s for my subtotals or if I need to use another function.
Thank you for your help.
May 24th, 2011 5:35pm
Hi,
Generally, we can also use the Sum function for conditional aggregate calculations. For example, if a dataset has a field that is named State with possible values Not Started, Started, Finished, the following expression, when placed
in a group header, calculates the aggregate sum for only the value Finished:
=Sum(IIF(Fields!State.Value = "Finished", 1, 0))
Hope this helps.
Thanks,
Tony Chain
Tony Chain [MSFT CSG] | Microsoft Community Support
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
June 6th, 2011 10:57pm