IIf function

1 Answer 8519 Views
General Discussions
norris bishton
Top achievements
Rank 1
norris bishton asked on 12 Apr 2011, 04:30 PM
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.

1 Answer, 1 is accepted

Sort by
0
Steve
Telerik team
answered on 13 Apr 2011, 10:13 AM
Hi Norris,

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

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

should be

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

Kind regards,
Steve
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
Tiago
Top achievements
Rank 1
commented on 06 Dec 2013, 12:25 PM

Hi,

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?

Thanks
Nasko
Telerik team
commented on 11 Dec 2013, 11:53 AM

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().

Regards,
Nasko
Telerik

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.

Steve
Top achievements
Rank 1
commented on 29 Aug 2014, 10:20 PM

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.
Stef
Telerik team
commented on 03 Sep 2014, 03:45 PM

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.

Regards,
Stef
Telerik
 

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.

 
Islam
Top achievements
Rank 2
commented on 15 Mar 2016, 08:48 PM

Hello,

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 

Stef
Telerik team
commented on 17 Mar 2016, 01:55 PM

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.

Regards,
Stef
Telerik
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
HarisB
Top achievements
Rank 1
commented on 30 Jun 2018, 05:31 PM

Hi,

how do I check the checkbox in the Telerik report if I get 1 or 0 data from the report?

= iif(Parameters.Servis.Value = 1 then True else False) 

Thank You

Silviya
Telerik team
commented on 04 Jul 2018, 06:03 AM

Hello HarisB,

Based on the available data, you might check the checkbox with the following IIf function applied to the checkbox item's Visible property:
= IIf(Fields.Value <> Null Or Fields.Value <> "", true, false)

I've attached a sample report for reference.

Regards,
Silviya
Progress Telerik
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
Ryan
Top achievements
Rank 1
commented on 16 Nov 2018, 08:41 PM

I am having trouble on how to get two columns/fields to show for the True part of the IIF. Below is my IIF and for when Fields.inodDetailRowType_typID = 2400, I need it to show the Fields.orpNumber and Fields.orpDescription next to each other on one line. 

 

=IIf(Fields.inodDetailRowType_typID = 2400, Fields.orpNumber Fields.orpDescription, Fields.inodDescription)

 

I cannot figure out how to get both to show and any help would be greatly appreciated!

Sezgin
Top achievements
Rank 1
commented on 03 Mar 2020, 01:59 PM

Hi. Can I visibly change and close the object, depending on the if loop? Valid value is "", visibility is off...
Todor
Telerik team
commented on 06 Mar 2020, 09:07 AM

Hello Sezgin,

You may use Bindings or Conditional Formatting to change the Visible property of the corresponding item. In the Expression, you may utilize User Functions, including IIF; Global Objects; etc.

Regards,
Todor
Progress Telerik

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
Tags
General Discussions
Asked by
norris bishton
Top achievements
Rank 1
Answers by
Steve
Telerik team
Share this question
or