Reporting Services Divide by Zero problem. - Derek Adkins

Reporting Services Divide by Zero problem.

Hi all,

I hope someone can shed some light on how to correct this problem.  I'm calculating a cents per kilometre value on a report in Reporting Services, and I'm using the following expression:

=IIF(Fields!ODO.Value <> 0, (Fields!MAINT.Value + Fields!SERVICE.Value + Fields!TYRES.Value) / Fields!ODO.Value, 0)

Now, for some reason, it's ALWAYS doing the divide. Even when the ODO field is zero.  I've tried various brackets to try force RS to check the value of the IF statement before doing the divide but it doesn't work.

I want to prevent the report showing "#Error", and put ".00: in there instead.

PLEASE HELP!  Thanks!
Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
Published 09 December 2005 11:34 AM by derek
Filed under:

Comments

# Geoff Appleby said on 09 December, 2005 03:50 PM
Hey Derek,

The problem you have here is that you're IIF, and it's a common problem.

People think that IIf means 'if this is true then do this, otherwise do that', but think about IIf as a function call, and what you get is 'if this is true then return the result of calling this, otherwise return the result of calling that - and here's both this and that, and run them please'.

That's sorta long winded, but i guess what i really mean is that both the true part and false part will execute EVERY time - and the results of either the true part or the false part are returned, depending on if the first part is true or false.

if you get what i mean :)
# Nuz said on 09 December, 2005 04:45 PM
Can't you get the values into a variables and then just use the variables to calculate the values on your report?
# Craig Nicholson said on 09 December, 2005 05:11 PM
Is the ODO field perhaps NULL?
# Roaan Vos said on 09 December, 2005 06:13 PM
It's a hack but try
=IIF(Fields!ODO.Value <> 0, (Fields!MAINT.Value + Fields!SERVICE.Value + Fields!TYRES.Value) / IFF(Fields!ODO.Value <> 0, Fields!ODO.Value, 1), 0)
# derek said on 15 December, 2005 09:58 AM
Thanks for your help guys. The strange thing is that I tried the exact same logic on a contractor's laptop, and it worked fine. So maybe it's a service pack issue? Not sure. But Roaan, I'll try your suggestion and see if it works.

Craig: the ODO field is definitely Zero. (not null).
# Daniel Acevedo said on 17 December, 2005 03:25 AM
Use this nifty work-around:

1) put the division in a HIDDEN textbox named Div
2) keep the following logic where you want the results displayed:
=IIF(Fields!ODO.Value <> 0, ReportItems!Div.Value, 0)
# Salt Lake City ASP.NET C# SQL Therapy said on 17 December, 2005 03:54 AM
Problem:I had some code like this: IIF(value &amp;lt;&amp;gt; 0, 1/value, 0) and it produced #Error.
Solution:I...
# Belial said on 27 January, 2006 09:18 PM
Reporting services might be the worst reporting tool ever , it can't even evaluate expressions. thxn microsoft as always giving developers headaches, and selling crappy products.
# cjap10 said on 02 February, 2006 07:21 AM
Hi! I have a set of data (chargeCodes) but what I want to do with this is if the chargeCode <> "fare", then display that chargeCode, otherwise, do not return the data. I am using IIF() but it requires a return data for both true/false conditions. Any workaround for this?
# Vincent Rainardi said on 16 February, 2006 02:31 PM
Daniel Acevedo, thanks. It works.
# Dave said on 21 April, 2006 08:22 PM
I find this a better solution to trap this particular error.

In the code section (properties), Paste:

function NDZ(a, b, c)

if b = 0 then
return c
else
return a/b
end if

end function

Then, whenever you want to divide numbers, use this expression:

=Code.NDZ(Val1, Val2, DZRes)

where:
 Val1 = Number
 Val2 = Divisor
 DXRes = Result to return if Val2 = zero (normally = zero)

# Dawn said on 04 May, 2006 10:04 PM
The comment by Roaan Vos helped me! The divisor goes the other direction and I was able to tweak the code to work with mine. THANKS!!!
# leon said on 09 May, 2006 01:26 AM
Hey! I actually know the answer to this one!!
(I knew that i wasn't wasting my time reading all those books).

The problem isn't reporting services fault: it's the way "iif" is evaluated.

It doesn't do "shortcutting". Hence every expression that gets passed to it must be evaluated.

Hence, what do you think this line of code will do:

IIf(True, MsgBox("hello"), MsgBox("goodbye"))

You might think it would just execute the:
MsgBox("hello")?

But you'd be wrong.

BOTH message boxes will come up, one after the other. Try it and see.

For this reason, a better solution is to use a full if statement

if b = 0 then
return c
else
return a/b
end if


Most importantly, remember that the IIF statement executes everything that's passed to it (but returns only one result).

This is a really deeply ingrained part of vb.

(I know Geoff Appleby already answered this above, but i think it went over some reader's heads)

cheers
lb
# Paul said on 19 July, 2006 03:37 PM
USed the function and works wit great success, did alter it so we don't get the infinity as well. However as derek says, the IIF works on some reports and not on others but with me not even a different machine.... that's was the bit that stumped me
# Justin said on 08 August, 2006 04:59 PM

Great post, solved my problem and taught me a few things about Reporting Services.

# Shriyal said on 13 September, 2006 09:12 PM

I tried Roaan's hack and it worked. Thanks. Also thanks for Geoff Appleby's explanation.

# Arun Kotte said on 02 March, 2007 09:07 PM

Worked with Daniel Acevedo's solution.  THANKS!

# Brett said on 12 April, 2007 02:28 AM

I also tried Roaan's hack and it worked. Thanks. Also thanks for Geoff Appleby's explanation.

# MHasan said on 17 September, 2007 03:45 PM

No Hack needed the real solution is to use nested IIF statement.

=IIF(Fields!ODO.Value = 0, "N/A", (Fields!MAINT.Value + Fields!SERVICE.Value + Fields!TYRES.Value) / IIf(Fields!ODO.Value = 0, 1, Fields!ODO.Value))

If a divide by error condition occurs it seems put N/A in the field.

I know this post is old but I hope this helps someone down the road.

M.Hasan

# Brent said on 19 September, 2007 10:07 PM

Just wanted to say that using Dave and Leon's info to eliminate the error was very successful.  I also learned about embedding custom code into reports, which is very helpful, but not very well documented in most of the resources I've come across.  Thanks guys!

# s.breedon said on 08 February, 2008 07:05 PM

Thanks M.Hasan your solution WORKS GREAT!!!!

# Dave_2.0 said on 29 February, 2008 01:25 AM

Aha!!  Thank you to Dave from a fellow Dave!  Creating the custom function in the properties worked great for me.

# Dee said on 29 May, 2008 05:04 AM

Thanks guys - all the solutions worked for me!

# Martin_B said on 23 June, 2008 12:04 PM

Just wanted to say thanks for the solution.  Saved the day for me.

Your a bunch of stars

# Martin_B said on 23 June, 2008 12:04 PM

Just wanted to say thanks for the solution.  Saved the day for me.

Your a bunch of stars

# João Pereira said on 14 July, 2008 03:00 PM

I have this litle big problem when i divide by zero, the RS shows the #error isn Report, my expression:

=iif(Fields!TRANS.Value <> 0 and not Fields!TRANS.Value is nothing ,(Fields!VB.Value/Fields!TRANS.Value),nothing)

When i use a textbox no make the division, reports the same error..

Where is my error!

Can give a tip..

# Wil said on 09 September, 2008 04:34 PM

Hi the answer mayb there somewhere but cant seem 2 see it my problem is similar but not quiat:

=iif(Fields!Phone.Value is nothing,"No Disponible",iif(Len(Fields!Phone.Value) < 10 or Len(Fields!Phone.Value) > 10,Fields!Phone.Value,Format(cdec(Fields!Phone.Value),"(###) ###-####")))

Where if the value is a NULL or nothing I get "No disponible" n if the value is "1234567890" I get "(123) 456-7890" but if it is "456-7890" I get #Error n know why just wondering if something can b done about it

# James Thurgood said on 11 September, 2008 01:39 AM

the solution by M.Hasan is the best, easy and works like a charm - thanks mate

# Greg said on 07 November, 2008 02:50 PM

I like dave's solution with using code.  It is easier to read than MHasan's solution.  I changed the name of the function to SafeDiv because Dave's function name NDZ doesn't say much.

# Sundar said on 14 January, 2009 10:15 AM

Dave's solution is the best...   It help me to solve the solution...

Thanks Dave...

# Sundar said on 14 January, 2009 10:17 AM

Dave's solution is the best...  I live it...  Thanks Dave...  Here is some enhanced version of it...

Public Function CalcRatio(ByVal Numerator As Object, ByVal Denominator As object, ByVal DivZeroDefault As Object) As Object

  If Denominator <> 0 Then

       Return Numerator/Denominator

  Else

       Return DivZeroDefault

  End If

End Function

# Ed Gass said on 27 January, 2009 12:21 AM

Appreciated the help - spun my wheels for over an hour before resorting to online help. I used Hassan's solution.

# lori said on 09 February, 2009 09:20 PM

Thanks for all of the responses... this has helped tremendously.

# Philip said on 23 March, 2009 03:48 AM

I've fegured out that you can do an inner if

The following attempts to show the % of revenue against budget.  Logic:

If Rows = 0 return 0.

else If Rows != 0

   If Budget = 0, return 0.

   else If Budget != 0 return actua/ Budget

On this last line the logic has to be expanded out to this:

   If Budget != 0 return actua/

       If Budget = 0 use 1 else use Budget

=CDec(iif(CountRows() = 0, 0, iif(sum(Fields!BudgetAmount.Value) = 0, 0, sum(Fields!ActualAmount.Value)/iif(sum(Fields!BudgetAmount.Value)=0,1,sum(Fields!BudgetAmount.Value)))))

Leave a Comment

(required) 
(required) 
(optional)
(required) 

Enter the numbers above: