Truncate textbox content with ellipsis in SSRS

Question

By default, when the contents of a textbox in SSRS overflows the width of the textbox, the textbox will grow vertically to accommodate its content. This feature can be turned off, by setting the "CanGrow" property of the textbox to "False".

However, this abruptly cuts off the content, which is not ideal.

I am looking for a way to clearly show the user that the text is too wide to fit the textbox. In the past, I've been using a simple expression to add an ellipsis "...", when the length of the text string was above some fixed number:

=Iif(Len(Fields!CustomerName.Value) > 25, 
     Left(Fields!CustomerName.Value,23) + "...", 
     Fields!CustomerName.Value)

But this does not work well when customer names contain a mixture of capital letters, lowercase letters, punctuation and other stuff that makes the individual character pixel widths vary wildly.

Ideally, some property for the textbox control would allow the report developer to add an ellipsis whenever text would not fit in a textbox.

Does anyone have any suggestions for a more elegant approach to this?

Beaulieu

Answer 1

Another solution I've come up with, is to use VB.NET, specifically the TextRenderer.MeasureText() function.

To make this work, I've added the following code to the report:

Public Function TextWidth(str As String) AS Double
    'Returns the width, in pixels, of a string, assuming Tahoma size 8.
    Dim size As System.Drawing.SizeF
    Dim font As New system.Drawing.Font("Tahoma", 8)
    size = System.Windows.Forms.TextRenderer.MeasureText(str, font)
    TextWidth = size.Width
End Function

Public Function TextCap(str As String, maxWidth As Integer, Optional suffix As String = "") As String
    'Truncates a string to fit within maxWidth pixels, optionally adding a suffix string if
    'any characters were truncated.

    Dim w As Integer, l As Integer
    l = Len(str)
    w = TextWidth(str)
    For i As Integer = 1 To 10
        If (w > maxWidth) Then
            l = (l * maxWidth / w)
            If (l < 0) Then
                l = 0
                Exit For
            End If
            w = TextWidth(Left(str, l) + suffix)
        Else
            Exit For
        End If
    Next i

    If l < Len(str) Then
        TextCap = Left(str, l) + suffix
    Else
        TextCap = Left(str, l)
    End If
End Function

Remember to add references to the assemblies System.Drawing (2.0.0.0) and System.Windows.Forms (2.0.0.0). The TextWidth function will calculate the width of a string of text, using the Tahoma font, size 8. This could easily be made dynamic by adding the font name and the font size as additional parameters to both functions.

When calling the TextCap function from an SSRS expression like this:

=Code.TextCap(Fields!CustomerName.Value, 150, "...")

the text will automatically be truncated at 150 pixels, and the suffix argument "..." will be added in case any characters were truncated.

Beaulieu

Nice work, but this algorithm doesn't take the ellipses into account when measuring the trimmed text. It's theoretically possible to trim down the text to something just under the max width, but when the ellipses are added, it goes back over. I think you need w = TextWidth(Left(str, l) + suffix) inside your For loop. – Mastectomy

You're absolutely right, thank you. I've edited the code accordingly. – Beaulieu

Also doesn't take into account your column is now capped, so the instructions should include a hidden column with the actual text if it matters when exporting. So the solution should really be a formatting option not a text truncated. You could supply the full text in a tooltip as well. – Onesided

Nice option, but could it just pull the width, font and font size from the textbox itself? – Annabelleannabergite