# Error with an expression in the footer of group

• ### Question

• =iif(sum(Fields!QTY.Value) <>0.00, sum(Fields!QTY.Value)/sum(Fields!PR.Value),0.00)

I have the above expression that says if the sum of one value is greater than 0.00, then do blah blah and else return a 0.00.  However, this expression works for some groups, but on others I get an error that looks like "#error".  The report in BI studio says it is trying to divide by a 0.00 which gives that error.  In my expression, it should never divide by a zero.  Any thoughts on what is wrong with my expression?

Ryan Mcbee
Monday, January 5, 2009 12:31 AM

### All replies

• Hello Ryan,

if you search this forum, you will find several related threads that explain the difference between IIF (which is a VB function call that evaluates all arguments) and an IF statement, and how to resolve this issue.  Check this thread as an example: http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/cc8de00b-e131-45ab-b4a0-a6a865489d22/

Brian Welcker also wrote up a more detailed blog posting about division by zero / IIF a while back: http://blogs.msdn.com/bwelcker/archive/2006/09/26/End-of-Amnesia-_2800_Avoiding-Divide-By-Zero-Errors_2900_.aspx

Hope this helps,
Robert
Robert Bruckner; http://blogs.msdn.com/robertbruckner; This posting is provided "AS IS" with no warranties, and confers no rights.
• Marked as answer by Tuesday, January 6, 2009 4:01 AM
Monday, January 5, 2009 1:35 AM
• Your expression never evaluates the denominator, sum(Fields!PR.Value), for a zero value.  You should be evaluating this in your expression to ensure that you are not dividing by zero.

=iif(sum(Fields!QTY.Value) = 0, 0.00, iif(sum(Fields!PR.Value) = 0, 0.00, sum(Fields!QTY.Value)/sum(Fields!PR.Value)))
Dan English -- http://denglishbi.spaces.live.com
Monday, January 5, 2009 12:41 PM
• Hello,

Try this expression...

=IIF(Sum(Fields!QTY.Value) = 0 OR Sum(Fields!PR.Value) = 0, 0.00, Sum(Fields!QTY.Value)/Sum(Fields!PR.Value))