CODEWITHSUNDEEP
reporting-services
poke
poke

Reputation: 2982

SSRS hide #Error displayed in cell

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

Upvotes: 21

Views: 89082

Answers (12)

Heffalumpene
Heffalumpene

Reputation: 21

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

Upvotes: 1

morrisong
morrisong

Reputation: 31

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

Upvotes: 0

MPJ567
MPJ567

Reputation: 553

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.

Upvotes: 0

Eduardo Slimming
Eduardo Slimming

Reputation: 11

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

Upvotes: 1

Jamie F
Jamie F

Reputation: 23809

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

Upvotes: 19

Anthony Smith
Anthony Smith

Reputation: 111

Add a small value to the second expression -

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

That'll return 0.

Upvotes: 11

Cameron
Cameron

Reputation: 1

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

Upvotes: 0

Paolo
Paolo

Reputation: 41

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)

Upvotes: 4

whistler
whistler

Reputation: 11

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

Upvotes: 1

Gil
Gil

Reputation: 261

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

Upvotes: 25

Andy Needham
Andy Needham

Reputation: 141

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

Upvotes: 14

xm1994
xm1994

Reputation: 473

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)

Upvotes: 0

Related Questions