Post reply

#Error Occurs in IIF Function

  • 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...

  • November 29, 2005 at 1:51 am

    #606331

    hi

    it is Divide by zero error  .

     

    regards

    padmakumar

  • November 29, 2005 at 8:23 am

    #606427

    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.

  • November 30, 2005 at 4:47 am

    #606668

    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.

  • November 30, 2005 at 8:54 am

    #606775

    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

  • December 2, 2005 at 2:58 am

    #607285

    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

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

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