Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sum of Sum base on Condition

Status
Not open for further replies.

Zurich98

Programmer
Apr 8, 2006
64
US

Hello Experts,

I'm using Reporting Services ver 2005. I'm trying to accomplish the following: I have two group in my report group A and group B

Total Amt
group A 100
group A -50
group A -30
group A 150

group B = -80
I need to sum the Total Amt for group B only when the Total Amt in group A is negative. so my total for group B = -80. I have try nest aggregate but seems like RS does not allow me to do this. You input/suggesstion is greatly appreciated.

Thanks
 
=sum(iif(FieldName!Value < 0,FieldName!Value,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
 
Thanks for your response. Maybe my original posted is not clear. Let try again. Group A is base on customer id.

Detail for cust1
custID ord# Amt
cust1 1 $25
cust1 2 $35
cust1 3 $10
cust1 4 $30

so i would have group A as follow

cust Balance
cust1 100 (sum of the orders for a cust1)
cust2 -50
cust3 -30
cust4 150

then Group B is based on Division. I only want to aggregate those customers with a neg balance. So the Total for Group B (Division) should be -80 (Balance of cust2 + Balancee of cust33).

I have tried
sum(iif(sum(Balance,"groupB") < 0,sum(Balance,"groupB"),0)))

but Reporting Services will not allow nested aggregate.

In Crystal I can accomplish this by using Running Total
sum(Amt) based on a formula (sum(Amt),custID) < 0.

Thanks
 
No - it was clear - not sure you need the group data in the SUM formula as RS is context sensitive in groups - the formula I gave would go in the subtotal box for group B and should (hopefully) work without any group reference (which should remove the issue with the nested aggregate)

If that doesn;t work - there is a RUNNINGSUM formula availabel for RS that I would imagine you can use in a similar way as Crystal..

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
 
the expression you provide will not work
=sum(iif(FieldName!Value < 0,FieldName!Value,0))


Here is the detail data for those customers with negative balance. For example cust2 = -50 and cust3 = -30

Cust ID Ord # Balance
cust2 1 100
cust2 2 -150

cust3 1 100
cust3 2 70
cust3 3 -200

Total (net) balance for cust2 is -50 (100 + -150)

Total (net) balance for cust3 is -30 (100 + 70 + -200)


i have to aggregate the customer balance in group A (group by Cust ID)

For the Division group B, I only want to know (aggregate) all customers within that division that have a negative balance (the balance aggregate in group A).

so if i use

=sum(iif(FieldName!Balance.value < 0,FieldName!Balance.value,0))

the Total for a Division is -350 (-150 + -200)
instead of -80 (bal of cust2 + bal of cust3)

in summary, i have to aggregate the balance for each customer, base on the balance of each customer (neg bal only), i aggregate the balance for the Division
 
apologies - only just got that you are summing the net balances that are < 0

Will have a think and get back to you - I'm sure it's possible but need to get my thoughts in order

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top