Alex Rivera | Logout

SSRS hide #Error displayed in cell

Asked 2012-02-04T20:36:05.867
21

I am doing computations on that data that will result in #Error at times. The underlying cause is a divide by zero. I could jump through the necessary work arounds to avoid the divide by zero, but it might be simplier to mask the #Error text and show a blank cell. Is it possible to hide the #Error and just display nothing?

Edit

The expression for the text might display #Error is something along these lines:

Fields!Field1.Value / Fields!ValueThatMightBeZero.Value

I could work around this with some ugly checking, but it might be easier to just catch the #Error. (A straight iif check around the express doesn't work because SSRS evaluates both the true and false clauses first; if it gets a divide by zero on either clause, it will return #Error, even if that clause wouldn't have been used).

reporting-services
Edit
Report

2 Answers

14

It is ugly but here is a way I've found to make it work in the expression and without the custom function.

You have to check in the denominator too and substitute a non-zero divisor there so that divide by 0 never happens (even though we'd like the first half of the IIF to short circuit it and not get there at all): I use 1.
Of course this will then give an incorrect value but then I keep the outer IIF to show whatever I want when the denominator is 0 (I show 0 in my example).

=IIF(Fields!Value_Denominator.Value=0, 0, Fields!Value_Numerator.Value/IIF(Fields!Value_Denominator.Value=0,1,Fields!Value_Denominator.Value))
answered 2012-03-05T15:20:51.610
1

Use the NULLIF function:

DECLARE @a int
DECLARE @b int

SET @a = 1
SET @b = 0

SELECT @a/@b --this returns an error

SELECT @a/NULLIF(@b,0) -- this returns NULL
answered 2013-05-14T10:48:33.043

Your Answer