Clever Geek Handbook

SSRS 2008 multi-line data displayed as one line in a report

I have a report that returns one data field in a tablix control field. The data field contains multi-line information in the database. See example below. In SQL Server
"Dan Chief - Zeo Office - Indiana"
"Dan Chief -" and vbcrlf and "Ceo" and "Indiana Office"

vbcrlf does not show up in SQL Server, but I have verified that they are present with instr () in SSRS.

I want the data to be displayed like this. I can remove hyphens and spaces.
Dan Chief
Seo
Indiana Office

But in SSRS report, it displays everything on one line. I have an autoload checkbox selected / enabled and still nothing.

Any ideas are appreciated.

Thank.

+3


source to share


4 answers


There are two directions for this. The method you want depends on whether you are formatting the text as HTML (right click on the text in the cell, select Placeholder Properties

. If the markup type is HTML, use the first approach below.)

It looks like you are rendering them as HTML, as vbcrlf will show up if you set it to None.

Processing text as HTML:

You need to replace vbcrlf with tags <br/>

. This is easiest if you use inline code in your report. Create a function in the report code: "Report" menu, "Report properties" → "Code" section.



Public Function ReplaceLineBreaks(ByVal s As String) As String
   Dim strBuilder As New System.Text.StringBuilder(s)
   If s.Contains(vbCrLf) Then
      strBuilder.Replace(vbCrLf, "<br/>")
      Return strBuilder.ToString()
      Else : Return s
   End If
End Function

Now set the field to use this code: = Code.ReplaceLineBreaks (Fields! ColumnA.Value)

If you are not processing text as HTML

vbCrLf should be saved and displayed in this case, so if this is indeed what you saved in SQL then you should be fine. If there are several other characters on the line, you can modify the ReplaceLineBreaks function above to insert vbCrLf.

+4


source


If you have vbCRLF in your table, this is not a good fit to solve this problem. SSRS will display vbcrlf as a string and not as a carriage return channel this way.

You must return 3 separate columns to your set:

Name, Role, Office



Dan Chief, General Manager, India Office

In your tablix, you can make an expression where you want to display this line:

=DataSet!Name.Value & vbCRLF & DataSet!Role.Value & vbCRLF & DataSet!Office.Value
+1


source


I haven't worked too much on SSRS. But one thing I would like to suggest is to create a placeholder in this text box. Then you can use html style tags to split the data across multiple lines. But of course I believe that you can only use this method if you have multiple expressions to fill in the same field. Havent tried a situation where the database itself contains multi-line information. Hopefully the link below explains the procedure better.

http://sqlserverpedia.com/blog/sql-server-bloggers/using-different-formats-within-a-single-textbox-in-ssrs/

You can see what all html tags are supported in ssrs here

http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/3bffb40a-c67f-4cb2-a3dd-ce711200e8db

+1


source


In my case, the Full_Address field I was outputting to the textbox contained CR (CHAR (10), in SQL terms), but not LF (CHAR (13)). (This was discovered using the CHARINDEX function.) So I was thinking that the individual lines of the complete address are not interrupts on the SSR output. It was like a line break as he exited the room in the SSRS report text box, horizontally.

So what I did in my saved proc was this: SELECT ... REPLACE (Full_Address, CHAR (13), CHAR (10) + CHAR (13)) AS FullAddress, ...

+1


source






More articles:


All Articles