I have a report issue I’m trying to resolve (SSRS/Report Builder). The comments field from JobOp is on several reports we use in production. Recently I was asked to update that fields’ format with a DMT file and it worked just fine. Spacing is as intended with a separate line for each line of text.
The problem is when the field is modified manually now after that DMT, I seem to loose the format of the text separated into lines. I’ve tried using all kinds of combinations of Chr(10) and Chr(13) along with and without VbCrLf. One of the combinations (I can’t even remember which one now) worked to separate each line of the manually entered text but then added an additional line to the DMT text. Is there a way to manage the differences within Report Builder or do I need to have the text (special characters) adjusted somehow in the text that was brought in with DMT? When I view the special characters from each example they look to be formatted the same to me.
The first image below shows the Special characters existing in the text for CR and LF. The First group(yellow) is the manually entered text in the comments field. The 2nd group (green) is text in the comments fields that came in thru DMT.
The 2nd image below, shows one of the report results. First part was the manually entered text, 2nd & 3rd parts were the comments that came from DMT.
Any help is appreciated! I’m not a code writer by the way - I typically find my answers be searching this forum or Google! Just not having a lot of luck with this one.
I know the following works, I just don’t remember how. I save things that are successful but sometimes forget what problem they solved.
Enter this in the Code section of a report
Public Function SplitComments(ByVal str As String) as String
Dim c() As String = Nothing
dim s as string = ""
c = str.Split(vbcrlf)
Dim i As Integer
For i = 0 To c.Length - 1
If i > 0 Then
s = s & vbCrLf & c(i)
Else
s = c(i)
End If
Next
return s
End Function
Then you can use a formula like this in the report body.
If you click off of the report in the gray area, that will bring up the Report Properties. The first line in the Properties is the Code section where you need to paste the first part I sent.
Shoot! I should not have given you the expression, just the code. I must have needed just the first line at some point. Told you I could not remember what my notes were for! Try this in a text box instead:
That worked thank you! I am still getting extra lines in the Text field where the information was brought in by DMT, which was part of my original problem. This will work for now, but if you have any thoughts on getting rid of those extra blank lines I’ll try it. In the screen shot below the first was entered thru a manual update the operations comment field. The 2nd shows comments that were brought in through a DMT. Any idea’s on why they would be different? When I pull them into Notepad++ they both show the same LF and CR codes.
I believe you do not have to put CRLF in if you are DMT’ing something that already recognizes a new line. If you want to DMT it in, I would do one or the other of the following:
Thank you for all your help (and patience with me) John! The DMT files originally came in just fine, one line for each as expected with no extra empty lines. Then someone made a manual change to the DMT’d text for one part and all the text ended back one big group without LF or CR being recognized even though it was there. I’m going to do some more testing with how that manual text is entered by the user to see what I can do with that, which seems to be the root of my problem!
So what piece of this formula indents the first row of data? If I want to get rid of the indent, what do I need to change?
Public Function SplitComments(ByVal str As String) as String
Dim c() As String = Nothing
dim s as string = “”
c = str.Split(vbcrlf)
Dim i As Integer
For i = 0 To c.Length - 1
If i > 0 Then
s = s & vbCrLf & c(i)
Else
s = c(i)
End If
Next
return s
End Function
Just not having a lot of luck with this one.
