SSRS hide #Error displayed in cell

Question

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

Ventriloquist

Answer 1

There is an IsError function, but it won't reduce the code you need to handle this. If you don't like the usual iif work arounds, then I think you need to use your own embedded code in the report. In that code you can have a try catch handler. Create a function that you can call with =Code.MyDivider(Fields!Field1.Value , Fields!ValueThatMightBeZero.Value)

Public Function MyDivider(top As Double, bottom As Double) As Double
    If top = 0 Then Return 0
    If bottom = 0 Then Return 0
    Return top / bottom
End Function

Phylis

+1 for custom code - IMO easier than a complicated expression on each cell. – Cressy

Answer 2

Change the font to be the color of the background if Fields!Value_Denominator.Value=0 and you won't see the error message.

Fancy

+1 - it's a strange solution but +1 for thinking out of the box :). – Lutist

Answer 3

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

Embrocate

Answer 4

Add a small value to the second expression -

Fields!Field1.Value / (Fields!ValueThatMightBeZero.Value+.00001)

That'll return 0.

Comptometer

Answer 5

This is a good solution. However I think it's possible even to improve it. Instead of putting the conditional formatting to "White", I suggest to put the conditional Hidden expression to True instead. In this case, when exported to excel, the cell will contain nothing, unlike the case of changing the font forecolor. Under Visibility --> Hidden =Iif(Fields!denominator.Value=0,True,False)

Bothwell

Answer 6

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

Roth

Answer 7

Use TRY & CATCH in a SSRS Custom Code

Public Function My_Function(ByVal My_Divisor As int) As int  
  Dim My_Result as integer
  TRY
My_Result = 100/My_Divisor
  CATCH
 My_Result = -1
  END TRY
  Return My_Result
End Function 

Sinew

Answer 8

To quickly hide potential #error for many cells at once, you can add =Not(IsNumeric(Me.Value)) in the hidden property for them.

Regretful

Answer 9

You can use IsNumeric to check each expression and display a 0 or better yet do not display anything as 0 might have some meaning.

IsNumeric - Returns a Boolean value indicating whether an expression can be evaluated as a number.

For example:

=Iif(IsNumeric(Fields!YourValue.Value), Fields!YourValue.Value, Nothing)

or

=Iif(IsNumeric(Fields!YourValue.Value), Fields!YourValue.Value, 0)

Homily

I'm not in front of SSRS right now, but I updated my question with more information. Will IsNumeric "Catch" #Error if it is returned by my expression. In other words, can I wrap IsNumeric around my entire expression and use it to determine if I have #Error? – Ventriloquist

@poke: I think you're right, and the #Error will be raised, even if the IIF is contained in an IsNumeric. – Phylis

@JamieF: You're right this does NOT work. Though a straight Iif to check the denominator value works for me on SQL 2008R2 (if only to catch division by 0). =Iif(Fields!Value_Denominator.Value=0, "", (Fields!Value_Numerator.Value/Fields!Value_Denominator.Value)) Interesting though, the errors are not very consistent e.g. Infinity, -Infinity, NaN... – Homily

Answer 10

Another alternative to the suggestions so far is to add the part of the formula that could create the error.

Example: a/b, if b = 0, then the equation gives an error.

{=IIF(b=0, TRUE, FALSE)}

Hal

Answer 11

This may not work in all cases, but for me it did the trick.

I added another column that referenced the textbox with the error (ReportItems!XYZ.Value), SSRS shows #Error as blank using this type of reference. I then hid the column that contained the actual formula, and voila, solved. Albeit in the hackiest way possible.

Eaton

Answer 12

Similar to the answer above of changing the font color based on the field value - add an expression to the field which checks if the field contains "#Error" thus: =IIF(Fields!xxxx.Value="#Error","",Fields!xxxx.Value).

Ashur