Thursday, March 29, 2012

Error level and expressions

In a report, I have the expression :

=iif(Sum( Fields!X_A_1.Value)<>0,(Sum( Fields!X_A.Value)-Sum( Fields!X_A_1.Value))/Sum( Fields!X_A_1.Value),"NA")

This expression product the error

"The value expression for the textbox ‘textbox12’ contains an error: Attempted to divide by zero."

I think it's because reporting execute the expression
(Sum( Fields!X_A.Value)-Sum( Fields!X_A_1.Value))/Sum( Fields!X_A_1.Value)

even if X_A_1 is equal to 0.

I would like reporting to display NA and not "#Error" when X_A_1 = 0

Is there is a way to catch the error in reporting?

Is there is a way for reporting not to execute the expression

(Sum( Fields!X_A.Value)-Sum( Fields!X_A_1.Value))/Sum( Fields!X_A_1.Value)

when X_A_1 is equal to 0.

Sorry if my english is not very good

Note: IIF() is a function call and evaluates all arguments (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp)

Instead of the pattern =IIF(Y=0, A, X/Y), use =IIF(Y=0, A, X / IIF(Y=0, 1, Y)) to avoid the division by zero.

-- Robert

No comments:

Post a Comment