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.