Using Trim() in SSRS2005 - and how it doesn't work

I found a situation today where the Trim() function doesn't work when used in a textbox expression.

I had a cell in a table that was showing the notes associated with a record, so the expression was simply:

= Fields!Notes.Value

While viewing the generated report, it was noticed that some notes had a lot of whitespace either at the leading or trailing end of the note, which caused the whole row in the table to expand in size as the note text wrapped, so I ended up with an effect like this:



This can look pretty ugly on a report. So the simple solution would be to trim the whitespace from the note text as part of the textbox expression:

= Trim(Fields!Notes.Value)

Bzzzzzzzztttttt!!!!!! Wrong answer - it didn't trim the note text at all. OK, time for Plan B, do a specific trim:

=LTrim(RTrim(Fields!Notes.Value))

But once again, no cigar (and yes, i know that Trim() should just call LTrim() and RTrim() under the hood, but i would be a bad developer if i just went with that assumption and didn't try this option). I'm thinking that this was caused by a failure to implicitly cast the Field!Notes.Value from an object to a string, so the various trim functions failed. So i though of another approach:

=Fields!Notes.Value.ToString().Trim()

and success!!!!!!...... and failure. This expression worked perfectly on rows that had a valid note, but on the ones where the value was null i got the familiar #Error statement in the textbox instead of a blank value:



This meant one more modification was required - i needed to check for a null value before i attempt the ToString().Trim() on the value. I am not a fan of the VB style IIf statement, as it doesn't shortcut (this means every argument in the function gets evaluated, regardless of the result of the expression), this means that the expression:

=IIf(IsNothing(Fields!Notes.Value), Nothing, Fields!Notes.Value.ToString().Trim())

would still error because the false part of the IIf would still get evaluated every time. The easy way round this is to just use a bit of custom code, and this is what i ended up with:

Textbox expression:

=Code.TrimStringValue(Fields!Notes.Value)

and the custom code:

Public Function TrimStringValue(ByVal fieldValue As Object) As Object
    If IsNothing(fieldValue)
        TrimStringValue = Nothing
    Else
        TrimStringValue = fieldValue.ToString().Trim()
    End If
End Function



Keywords: ssrs2005, ssrs, trim, ltrim, rtrim, iif