simple problem with IIf - please help!

  • Hi! When I preview the report I get an error: Attempted to divide by zero. This occurs because other value is empty. So I tried to add iif-statement to expression-field like this:

    =iif(Fields!Forecast.Value<>0,FormatPercent(Fields!CostPrice.Value/Fields!Forecast.Value),0)

    That means if the forecast.value is empty it should just print 0 - but it won't. I don't get it...it just prints Attempted to divide by zero-error.

    Anybody...help?

  • Problem solved....it's a common thing...I used google to find the answer. 😀

  • Could you post the answer and/or the link you found that provided the answer? That way if someone else googles it and finds this link then they will have the answer or have a place to go to get it.

  • I just had the same problem... SSRS doesn't short-curcuit the IIF... meaning it evaluate everything regardless if the results (even that is inconsistant!!).

    So you need the put the IIF again when doing the divisionand that will solve the problem.

  • We use a piece of custom code that is inlcuded in our report templates so the engineers no longer have to continue to write these IIF statements. Anytime they have a formula that requires division, they use this function. (NDZ stands for NO DIVIDE by ZERO)

    using your fields... =code.NDZ(Fields!CostPrice.Value,Fields!Forecast.Value,0)

    Here's the function:

    function NDZ(Numerator, Denominator, DZResult)

    if Denominator = 0 then

    return DZResult

    else

    return Numerator / Denominator

    end if

    end function

  • Is it the same performance than using inline code? I ask because in MS Access, that can really hurt you (not always)... same thing with a functino in t-sql.

  • No impact at all on performance that we've seen and we have some pretty large and complex reports that utilize this function ALOT.

  • RSCERO (12/10/2008)


    We use a piece of custom code that is inlcuded in our report templates so the engineers no longer have to continue to write these IIF statements. Anytime they have a formula that requires division, they use this function. (NDZ stands for NO DIVIDE by ZERO)

    using your fields... =code.NDZ(Fields!CostPrice.Value,Fields!Forecast.Value,0)

    Here's the function:

    function NDZ(Numerator, Denominator, DZResult)

    if Denominator = 0 then

    return DZResult

    else

    return Numerator / Denominator

    end if

    end function

    I've done the same thing and I don't think there is a performance issue. It's all processing row by row in memory so it should be fast.

  • Thanks for the info.

Viewing 9 posts - 1 through 8 (of 8 total)

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