Developing Matt

My Technical Journal

Divide by 0! and the tempermental conditional IIf

leave a comment »

When working with reports there is this little glitch with the conditional iif statement.  The problem is that even if you check for 0 in your value the engine still tries to process the full statement. If your value = 0 then this will fail:  iif(value=0,1,1/value).  Result: #Error 

Stated again: the engine still tries to process 1/value even though it shouldn’t ever hit it.  So here’s a workaround.

 

Add the following code to your report.  (report properties…code tab) 

Public Shared Function DivideXbyY(ByVal X As Decimal, ByVal Y As Decimal) As Decimal

    DivideXbyY = IIF(Y=0, 0, X /iif(Y=0,1,Y))

End Function

‘examples:

‘ DivideXbyY(1,0) returns 0

‘ DivideXbyY(1,1) returns1

This line: iif(Y=0,1,Y) makes sure the code evaluates correctly.  It technically won’t ever run at runtime, but you won’t get an #error now. 

 

Now you can just call it with an expression

=Code.DivideXbyY(x,y)

 

Ignore the ‘Unrecognized identifyer’ error warning.  The code compiles at runtime so in design mode it doesn’t know what you are referencing. 

 

Code found:  http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1708914&SiteID=1

Author: Jerry Hung

 

Advertisements

Written by matt

May 23, 2008 at 9:47 am

Posted in Report Services

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: