IIf function

8 posts, 0 answers
  1. norris bishton
    norris bishton avatar
    1 posts
    Member since:
    Jun 2012

    Posted 12 Apr 2011 Link to this post

    i know that it has been said that the IIf function evaluates both the true and false expression and reports an error if either causes an error and this is done by design. I'm just confused how, without a user function, to do certain, everyday things. For example.. In case of this simple report:

    item.... sold... avg price.. in stock..  avg days
    A112       5        112.00             5               22
    A113       0            !DIV             1               12
    A114       1        114.00             0             !DIV

    in a normal situation you would get the avg price by using the expression =iif(Fields.sold>0,Fields.totalprice/Fields.sold,0) and avg days in stock =iif(Fields.instock>0,Fields.totaldaysinstock/Fields.instock,0)
    but in telerik reports the expression will cause a divide by 0 error because both expressions in the iif() are evaluated. These are simplified examples and I understand there is an avg() function, but there is more processing going on that voids it's use.

  2. Steve
    Steve avatar
    10941 posts

    Posted 13 Apr 2011 Link to this post

    Hi Norris,

    What you can do to make the expression correct is nest another IIF function to make it valid for evaluation e.g.:


    should be

    =iif(Fields.instock>0,Fields.totaldaysinstock/iif(Fields.instock=0,1, Fields.instock),0)

    Kind regards,
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. Tiago
    Tiago avatar
    2 posts
    Member since:
    Feb 2013

    Posted 06 Dec 2013 Link to this post


    I just came across this issue. There's really no way of saying it other than it's extremely bad and disappointing.

    By the way, my issue is with expressions in the form:

          =IIf(Fields.Price IS NOT NULL,  Fields.Price.MyFormat(), "")

    Which of course blows up the report.

    What now? Do I create (yet another) string property? My domain entities are full of them already. If the reporting engine doesn't really support anything as *basic* as checking for null, why advertise "Expressions" ?

    Can this please be fixed?

  4. Nasko
    Nasko avatar
    944 posts

    Posted 11 Dec 2013 Link to this post

    Hello Tiago,

    We have logged this problem in our system for investigation and when we find a way to overcome it, we will include the fix in a subsequent version of Telerik Reporting. Please excuse us for the temporary inconvenience.

    In the meantime you can work around this issue by creating a custom User Function, which first checks if the Fields.Price is null and then returns the result of MyFormat().


    New HTML5/JS REPORT VIEWER with MOBILE AND TOUCH SUPPORT available in Telerik Reporting Q3 2013! Get the new Reporting version from your account or download a trial.

  5. Steve
    Steve avatar
    5 posts
    Member since:
    Aug 2014

    Posted 29 Aug 2014 in reply to Steve Link to this post

    Wow, during my trial period I ran into all kinds of issues but this one takes the cake!  Working by design but the design is wrong.  Should have called it something other than IIF since it doesn't work like the rest of the world's IIF.  I can't get the belt and suspenders approach to catching divide by zero to work on a total line.
  6. Stef
    Stef avatar
    3611 posts

    Posted 03 Sep 2014 Link to this post

    Hi Steve,

    The previously reported issue in this thread was recognized as a problem with the Null value handling, and is fixed. The IIf conditional function is a function, and as such all arguments are evaluated, whereas an if statement (e.g. C# code) evaluates its arguments differently.
    To avoid the issue with the zero division you can use an expression as:
    =IIF(Fields.Column1=0, "---", Fields.Column2/IIF(Fields.Column1=0, Fields.Column2, Fields.Column1))
    Or a custom function.

    Based on your feedback we will consider adding improvements in expressions handling in such special cases.

    About the other issues you had during your evaluation, please update the corresponding threads in your support history with details if you still experience any problems. We will be happy to assist you further.


    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

  7. Islam
    Islam avatar
    2 posts
    Member since:
    Mar 2016

    Posted 15 Mar 2016 Link to this post


    i have a column return 3 different values

    and i want to check

    if the returned value=1 then "Something" 

    else if returned value=2 then "Something 2" 

    else if returned value=3 then "Something 3" 

    Thanks for your help 

  8. Stef
    Stef avatar
    3611 posts

    Posted 17 Mar 2016 Link to this post

    Hi Islam,

    You can use a complex expression with nested Conditional functions and/or operators.

    For example:
    =IIf(Fields.X=1, 'One', IIf(Fields.X=2, 'Two', 'Else'))

    I hope this information is helpful.

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top