Report Server 2005 - How to SUM value by condition

hi, i need some help on this.

I create Matrix that show the sales and cancels of the sales department.

The TOTAL in the Matrix are fine, but i need to show also how many Sales

(param is bigger then 0) and how many cancels (param is negative, smaller the 0) each

sales man have in the Matrix report. i try to use IIF statement but it only return absolute number:
--
="Sales :" & (IIF(Fields!SalesCommission.Value<0, sum(Fields!SalesCommission.Value), 0)) &
"Cancels :" & (IIF(Fields!SalesCommission.Value>0, sum(Fields!SalesCommission.Value), 0)) &
"Total :" & IIF(sum(Fields!SalesCommission.Value)<0, "(" & Format(ABS(sum(Fields!SalesCommission.Value)),"F1") & ")" ,sum(Fields!SalesCommission.Value))
--

I think i will need to use Costum Code on this one.

anny advice ?

Thanks

It looks like your IF statements are backwards from what you want. Your output for your "Sales:" will return sum(Fields!SalesCommission.Value) when Fields!SalesCommission.Value is less then 0. Your output for your "Cancels:" will return sum(Fields!SalesCommission.Value) when Fields!SalesCommission.Value is greater then 0. This is because IIF( [EXPRESSION], [TRUE PART], [FALSE PART]). So it should look like:

="Sales :" & (IIF(Fields!SalesCommission.Value>0, sum(Fields!SalesCommission.Value), 0)) &
"Cancels :" & (IIF(Fields!SalesCommission.Value<0, sum(Fields!SalesCommission.Value), 0)) &

See what that does for you. Maybe I misunderstood/misread what you wanted for that part. Try and see what that does for you.

|||

Thanks, you are right, i replace the Sales and Cancels places.

I try your sample but again, the Matrix row have both Sales and Cancels in the Matrix TOTAL boxes and the EXPRESSION return the Total of the Sales O.K but the Total of the Cancels is 0 (zero).

What i need is to show in the MATRIX table the totals per salesman (Sales - (Minus) Cancels) and the clculatet Braek of

For exsaple:

Salse: 12.5

Cancels: -6.5

Total: 6

Anny Advice ?

|||So a "Cancels" is commision under 0 dollars. And a "Sales" is commission over 0 dollars. So you want to find how much commission total each sales person made? If I understand you correctly, all you need to do is sum(Fields!SalesCommission.Value) for your total. That should take care of everything. I hope this helps!