SSRS – Putting the Squeeze on Column Header Formatting with Line Breaks

In Excel, you can Control + Enter to manually create a line break in a cell to set the custom line formatting you require.  In SSRS you can do this too, but this relies on the committment of the developer to follow this through consistently.  Additionally add multiple developers across different development teams throughout the organisation, some offsite others onsite, some insourced others outsourced, and you might be looking for an approach that allows more consistency.

Open your report, go into the Report properties and add the following function to the code in the Report Properties Code tab.  What this function does is automatically line split your column header title by replacing any space character with a line break.  This gives you the ability to exert a level of control in a large organisation with multiple developers by embedding this code structure in your SSRS RDL template.

Public Function AddLineBreakBetweenWords(words As String) As String

Dim rsRegEx as System.Text.RegularExpressions.Regex = new System.Text.RegularExpressions.Regex("\s+")

words = rsRegEx.Replace(words, " ")

return words.Replace(" ", vbCrLf).Trim()

End Function

In the expression for the column header you can call the expression like this …

= Code.AddLineBreakBetweenWords(Fields!FieldName.Value & " Retail (000's)")

For the Field value “Footwear” the above expression should format the column header something like this …

Footwear

Retail

(000’s)

Alternatively to configure the function to split on the pipe (i.e. | ) character when space is too senstive, then change the function as follows …

Public Function AddLineBreakBetweenWords(words As String) As String

Dim rsRegEx as System.Text.RegularExpressions.Regex = new System.Text.RegularExpressions.Regex("\s+")

words = rsRegEx.Replace(words, "|")

return words.Replace("|", vbCrLf).Trim()

End Function

Yes this still requires committment from the developer to follow this through however in your SSRS RDL template you can prepopulate the tablix column header expressions with placeholder sample values.

Of course if you have SQL 2008 R2 or higher why not also publish the Tablix as a Report Part.

Reference Kodyaz example.

Happy column header formatting!

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s