Blanking out errors when dividing in SSRS 1

[eksortso]

I'm using SQL Server 2005 Reporting Services, and I am having troubles getting a ratio to blank out when the divisor in the calculation is zero or null.

I have a static group in a matrix with three columns. The first textbox is Margin, and the second is Objective. I want to display Margin/Objective, but blank it out when the Objective field is zero or null.

The basic expression for the percentage column's Value is this:

=ReportItems!Margin.Value/ReportItems!Objective.Value

This works fine when Objective has non-zero values. When Margin and Objective are null, I get NaN. When Objective is zero, I get #Error.

I've tried the following expression to blank out the value when Object is 0 or null, but it still gives me #Error values in Report Manager. (I get Infinity values in Visual Studio 2003 Preview, but let's not go there right now.)

=ReportItems!Margin.Value / Iif(ReportItems!Objective.Value = 0, Nothing, ReportItems!Objective.Value)

What can I do to get ride of these error messages? I've looked in Books Online and in other SSRS forums, but nothing has helped so far. Anything you can offer will be greatly appreciated!

 

[Sheco]

You can check for nulls like this

iif(ReportItems!Objective.Value Is Nothing, "it was null", "not null")

Also you could just make a function in the code section:
Public Function SafeDivide(x, y)
If (y is nothing) Then
SafeDivide = ""
ElseIf (y = 0) Then
SafeDivide = ""
Else
SafeDivide = x / y
End IF
End Function


Then you could call it like this:
=Code.SafeDivide(ReportItems!Margin.Value, ReportItems!Objective.Value)


or something like that

 

[eksortso]

Sheco: The Code.SafeDivide() solution worked perfectly. Thank you!