How to Add Line Breaks Between Words in SQL Server Reporting Services Reports using Custom Code
Microsoft SQL Server Reporting Services rapid and customized report developing for many SQL Server developers and database administrators.
I prefer to use SSRS 2005 or SSRS 2008 when a new report should be developed or build.
I had recently a new report request from Buniness Development teams and as a business requirement or a design requirement, I had to break down the words in the column headers into lines.
I mean I had to display each word in a separate line in the table header of the Reporting Services report.
This means I should place line breaks between words in the table columns. This would be easy if the column names were static. But because of the localization requirements and used methods to get localized names from custom asseblies and custom code, I had to deal with dynamic data.
So I decided to build a small function in Custom Code section of the report.
This custom code function will take a string as parameter. And it will process the parameter input, place white spaces with single space. And then replace this single space with line break and return the evaluated parameter as output.
Below you can find the Custom Code I used to solve adding line break between words in column headers. You can use the same VB script in adding line breaks among any where in your reporting services reports.
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
And you should call this function by a simple reporting services custom code call like Code.AddLineBreakBetweenWords with the sample code below:
= Code.AddLineBreakBetweenWords("Business Phone")
Here is a preview output of a sample report which I had developed using MS SQL Server 2005 Reporting Services using the AddLineBreakBetweenWords Custom Code function:
Removing Multiple Extra Spaces Into Single Space in SQL Server Reporting Services Reports using Custom Code
While I was working on the solution of adding a line break between two words, I realized that I had to remove the extra spaces in order to prevent multiple line breaks in the header.
I also should remove the beginning and trailing spaces, so I had to trim the input parameter which is the original column header text.
I thought that for such a problem the best solution would be developed by using Regular Expressions in the custom code function.
For defining the RegularExpressions Regex class I had to use the following code.
Dim rsRegEx as System.Text.RegularExpressions.Regex = new System.Text.RegularExpressions.Regex("\s+")
The regular expression I preferred to use for this case in Custom Code function AddLineBreakBetweenWords is : "\s+" which points to 1 or more white space characters.
So I first replaced extra white space characters into a single white space character.
And then I replaced the single space with vbCrLf to insert new line instead of displaying the space between two words.
words = rsRegEx.Replace(words, " ")
The code block I had given above makes the trick and replaces all multiple spaces or extra spaces with only one space.
And the below code replaces all spaces with new lines (vbCrLf) and trims the beginning or starting and ending or trailing spaces and returns the resultant text as the output of the custom code function.
return words.Replace(" ", vbCrLf).Trim()
I hope this sample will help you solve some of your problems you might be experiencing and help you understand some capabilities of Custom Code in Reporting Services.