#Error Occurs in IIF Function

Question

Post reply

#Error Occurs in IIF Function

Gogula G. Aryalingam

SSCrazy

Points: 2688
More actions
November 29, 2005 at 1:35 am

#115617

I have the following situation:
I have written the following formula in Reporting Services:
IIf ( x = 0, 0, y / x)
But when x = 0, #Error is displayed in the cell
When x <>0, the result of y / x is displayed.
I tried this the following way:
IIf ( x = 0, 0, 4/2)
When x = 0, it displays 0
IIf( x = 0, 0, 4/0)
When x = 0, it displays #Error.
Can somebody give me a reason why this happens like this and any work arounds for this. Thanks...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

padmakumar_pga Right there with Babe Points: 766 More actions · Answer 1

padmakumar_pga

Right there with Babe

Points: 766

November 29, 2005 at 1:51 am

#606331

hi

it is Divide by zero error .

regards

padmakumar

Syed Asim Rizvi Ten Centuries Points: 1112 More actions · Answer 2

Are you sure you are using "/" not "\".

Becasue "/" is for Divide and "\" is for Mod. And if you try to mod a number with zero, it gonna return you with error. And I think thats what happening here.

Dave Klug SSCertifiable Points: 5811 More actions · Answer 3

I've run into the same issue and have not yet figured out why this does not work correctly. What we've done is to create a custom function within the report and then pass in the numerator and denominator for what we're dividing. Within the function, use a try/catch block to attempt the division and return the value.

Hope this helps.

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 4

Thanks for replying. I did not know that you could use try and catch in Reporting Services! What is the syntax like?

Also, I'm using this formula to calculate the SubTotals and Grand Total. The record values are already calculated and returned through an SP. So, in the example I had given, y and x are Sums of two columns.

I would really appreciate it, if you would give me a sample of the custom function which you are using...

Thanks a lot

Jos Janssen Old Hand Points: 322 More actions · Answer 5

Hello all,

The #Error behaviour is by design as far as I know, because in an IIF function, both statements false/true get evaluated regardless of the outcome of the if statement. So, if x = zero, iif(x=0,0,y/x) still evaluates y/x and triggers the divide-by-zero error.

You should write a function to work around this problem. In code, an [if else end if] block evaluates only the path it has to follow according to the if-statement.

Regards,

Jos Janssen