Home Dashboard Directory Help
Search

IIF ( ) Function in SSRS Expression Builder not consistent with SSAS function of same name by Todd C


Status: 

Active


8
0
Sign in
to vote
Type: Suggestion
ID: 546387
Opened: 3/31/2010 10:28:40 AM
Access Restriction: Public
1
Workaround(s)
view

Description

The IIF () function as used in SSRS Expression Builder evaluates BOTH the True and False part before returning its final results. But if one of those evalueations causes an error, the entire result is an error, even if that is NOT the result selected by the evaluation.

For Example, two fields A and B. A = 1, B = 0
Set up an Expression of:
=IIF(B=0, "Can't Do this math", A/B )
This will fail because A/B causes the error, even though you are explicitly asking for something else.

However, in SSAS, in the Calculation Designer, you could actually use this type of logic and know that should it encounter an instance of B = 0 then it will still funciton and return the proper result instead of an error.

Also, there is no BOL documentation for the IIF function in SSRS.
Details
Sign in to post a comment.
Posted by Todd C on 10/6/2010 at 6:05 AM
To Microsoft/Robert Bruckner:

You missed the point entirely!
The point is THIS: The IIF() function in SSRS is NOT the same as the IIF() Function in SSAS!

Functions by the same name AND PRODUCTED BY THE SAME COMPANY should return the same results regardless of which of that company's product you are using to call said function.

It is a big indication of DUPLICATION OF WORK by Microsoft's development teams, and an indication that they don't collaborate on simple CORE stuff!

And don't even get me started with SSIS, they use a construct of <boolean test>?<true part>:<false part>.
Posted by Microsoft on 5/2/2010 at 10:22 PM
Hello Todd,

IIF() is a VB runtime library function call which evaluates all arguments before it executes (you can read about this behavior on MSDN: http://msdn.microsoft.com/en-us/library/27ydhh0d(VS.80).aspx, as well as Wikipedia: http://en.wikipedia.org/wiki/IIf).

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF VB statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

For a future version (which also depends on changes in the VB runtime language), we are considering to provide a replacement so that IIF doesn't evaluate all arguments anymore.

We may close this particular connect bug as a duplicate of the feature request we are already tracking internally.

Thanks,
Robert Bruckner
SQL Server Reporting Services Team
Posted by marvinbobo on 4/1/2010 at 10:19 AM
Our dev team just encountered this issue today and it is as described.
Sign in to post a workaround.
Posted by Todd C on 4/7/2010 at 4:48 AM
This is kind of hokey but it works. You can use an expression like this:
=IIF ( Demoninator = 0 , 0, Numerator / (IIF (Denominator = 0, 1, Denominator)))

This way, if Denominator = 0 then the expression Numerator/1 is evaluated (and NOT used). Result is NOT an error.