none
SSRS Report Builder - iif formula / expression issue RRS feed

  • Question

  • In one of the Tablix fields, i have this Iff expression

    this works:

    =iif(Fields!LaborCost.Value = 0, nothing, "has value")

    So, if the LaborCost does not = 0, it will display "Has Value". So theoretically I should be able to replace that "has value" with a calculation, but that's when it breaks

    this does not work:

    =iif(Fields!LaborCost.Value = 0, nothing, (Fields!PandLUnadjusted.Value / Fields!LaborCost.Value))

    in the example above, it always performs the division calculation, regardless of the iif conditions being True or False, which ends up returning #error if the denominator is 0.

    Any help?




    • Edited by vilkit Wednesday, October 9, 2019 10:59 AM Clarity, formatting
    Wednesday, October 9, 2019 2:01 AM

All replies

  • Hi Vikit,

    This is quite a common issue when using IIF expression in SSRS.

    Luckily we already have good solution for it. See here :#Error using IIF and divide by zero

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 10, 2019 1:17 AM
  • Hi, 

    If the solution helps, you could kindly mark it as answer, this would help the community.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 11, 2019 8:18 AM