CODEWITHSUNDEEP
reporting-servicesssrs-2008
user1687231
user1687231

Reputation: 19

Replace #Error or NAN with -(hyphen) in SSRS

When i get a value into the text box (say "NAN" or "#Err") i want to replace it with -(hyphen). How to achieve this in SSRS reporting ?

Thanks in advance.

Upvotes: 1

Views: 7972

Answers (3)

JsonStatham
JsonStatham

Reputation: 10374

What about:

=IIF(Fields!yourcolumnname.Value = 0, "-", Fields!yourcolumnname.Value*1)

Or if you wanted zero's where you get NAN/#ERROR then you could use:

=IIF(Fields!yourcolumnname.Value = 0, Fields!yourcolumnname.Value*1, Fields!yourcolumnname.Value)

Upvotes: -2

Michael A
Michael A

Reputation: 9920

To elaborate on cmotley's answer, add the following as custom code to your report (to do this go to report properties, then code and then paste this in):

Function SafeDivide(value1 As Decimal, value2 As Decimal) As Decimal

        If (value1 <> 0.0 And value2 <> 0.0) Then
            Return value1 / value2
        Else
            Return 0
        End If

End Function

Then you can use this in your textboxes to divide without receiving not a number and/or error, for example, adding the following as an expression (changing textbox names as required):

=Code.SafeDivide(ReportItems!Textbox186.Value, ReportItems!Textbox184.Value)

Divides two textboxes using our function.

To show a dash you can simply change the formatting of your textbox to 'number' and tick the box indicating that you'd like to replace '0' with '-'.

Alternatively if for some reason 'NaN' is coming through explicitly from a datasource (rare but not impossible when referencing a loaded table in SharePoint) you could use the following:

Function evalutator(value as String) as String

    Select Case value
        Case "NaN"
            Return "-"
        Case "NAN"
            Return "-"

    End Select

    Return value

End Function

Upvotes: 2

user1172023
user1172023

Reputation:

You'll need to write an expression to handle it. Ideally, you would have some custom code function in your report (ie. Code.SafeDivision())

=iif(Fields!denominator.Value = 0, "-", Numerator / Denominator)

Upvotes: 2

Related Questions