0
votes

I'm having difficulty totalling this IIF expression using report builder 3.0 and SQL Server 2008. Some info on the table, I have a 20% commission and a $5,000 cap. I just need to total the Cap column.

=IIF(Fields!amount.Value>=Fields!commcap.Value,Fields!commcap.Value,sum(Fields!amount.Value*sum(Fields!commpct.Value/100)))

I've tried

=sum(IIF(Fields!amount.Value>=Fields!commcap.Value,Fields!commcap.Value,sum(Fields!amount.Value*sum(Fields!commpct.Value/100))))

and

=sum(IIF(Fields!amount.Value>=Fields!commcap.Value,Fields!commcap.Value,sum(Fields!amount.Value*sum(Fields!commpct.Value/100))),"DataSet1")

Neither worked.

amount    |Commission  |Cap
38,201.74 |7,640.35    |5,000.00
1,002.04  |200.41      |200.41
-----------------------------------
$39,203.78|$15,681.51  |$20,681.51----This total should be $5,200.41

I appreciate any help.

1
Is that your real data in the table? Your IIF looks good (with the dataset specified since I'm not sure what your grouping is on that last row), I would be focusing on this though: sum(Fields!amount.Value*sum(Fields!commpct.Value/100))). SUM is telling it to run the sum of all amounts in current grouping. While you're running the sum of everything in your current group, you're getting another another sum which is getting all of the results from that current grouping. Try replacing that with 0 and make sure you get the sum of all of the caps meeting criteria, then you will know that's the prob.Lucky
I feel like an idiot. removing the sums is exactly what I needed to get the correct numbers. Thank you.Nick

1 Answers

0
votes

Removing the sums from the IIF expression fixed my issue. Thanks Lucky