0

I have this string that is coming from the db and the words are separated by a space. the number of words can vary from a minimum of one to a maximum of six, so what i essentially want is to start a new line immediately after the second word. For example the string "Natural Financial Services" needs to show as:expected result in the report. i case if there is only one word then there should not be any line break, i have tried this Replace(Fields!CustodianNameTxt.Value," ",Vbcrlf) but this will cause each word of the string to appear in a separate line, like this:result with my current expression Which is not what is expected, anyone please suggest if there is any solution to achieve this? Thanks in advance.

ArK
  • 47
  • 1
  • 1
  • 7

2 Answers2

0

One way it can be done is by splitting the string into an array of words and then rebuilding it with the added CRLF using choose function In this example, the choose are inside an isnothing to cater for lines with less than six words:

=IIF(not isnothing(choose(1,split(Fields!CustodianNameTxt.Value," ")))," " + choose(1,split(Fields!CustodianNameTxt.Value," ")),"") 
+IIF(not isnothing(choose(2,split(Fields!CustodianNameTxt.Value," ")))," " + choose(2,split(Fields!CustodianNameTxt.Value," ")) + Vbcrlf,"") 
+IIF(not isnothing(choose(3,split(Fields!CustodianNameTxt.Value," ")))," " + choose(3,split(Fields!CustodianNameTxt.Value," ")),"") 
+IIF(not isnothing(choose(4,split(Fields!CustodianNameTxt.Value," ")))," " + choose(4,split(Fields!CustodianNameTxt.Value," ")) + Vbcrlf,"") 
+IIF(not isnothing(choose(5,split(Fields!CustodianNameTxt.Value," ")))," " + choose(5,split(Fields!CustodianNameTxt.Value," ")),"") 
+IIF(not isnothing(choose(6,split(Fields!CustodianNameTxt.Value," ")))," " + choose(6,split(Fields!CustodianNameTxt.Value," ")),"")
Jayvee
  • 10,670
  • 3
  • 29
  • 40
  • thanks for the response, but the existing expression that i'am using is already very complex and adding this to it would make it more complicated, is there any shorter version for this ? please advise – ArK Mar 07 '18 at 20:08
  • Another way of doing it is via function, which you can define at report level as code. Then just call the function on your expression passing the field to be splitted as parameter. The good thing about functions it’s not only more readability but also that you can use many specific Vb functions, for example indexof(), which will come handy in a case like this. Please let us know if you need help coding the function. – Jayvee Mar 08 '18 at 07:38
  • I wanted to mention that in my previous comment, i could definitely use it as a code, can you please provide more details about how the code needs to be and how to use that code in my expression, thank you. – ArK Mar 08 '18 at 11:24
0

Select report properties -> Code

Add this function (there might be better ways of achieving the same in VB, this is just one example):

Public Function splitline(byval line as string) as string   

dim words() as string=line.split(" ") ' separate the lines into array of words
dim pos as integer ' to calculate the position for the breaks
dim newlines as string = line ' string for the line with added breaks

if words.length > 2 then  ' there are 3 or more words add break after second word
    pos=len(words(0)) + len(words(1)) + 1 ' this will be the position of the first break
    newlines=newlines.remove(pos,1).insert(pos,VBCRLF) ' remove the space and add a break
end if

if words.length > 4 then  ' there are 5 or more words add break after forth word
    pos=len(words(0)) + len(words(1)) + len(words(2)) + len(words(3)) + 4 ' adding 4 because 2 spaces + cr + lf
    newlines=newlines.remove(pos,1).insert(pos,VBCRLF) ' remove the space and add a break
end if  

return newlines

End Function

The expression in the will then just be:

Code.splitline(Fields!CustodianNameTxt.Value)
Jayvee
  • 10,670
  • 3
  • 29
  • 40
  • Sorry for delayed response , i was away for a few days. I tried the above code you mentioned and i had to edit the Code.splitlines(Fields!CustodianNameTxt.Value), since the function name mentioned includes an "s" extra and i tried to preview the report and i get an error, i did not make any changes to your code, just used as is, please advise – ArK Mar 13 '18 at 12:20
  • there was an error on the 3rd line of the function, I've updated it and also updated the call using the function name without the s, please try it now – Jayvee Mar 13 '18 at 14:18
  • that worked beautifully, thank you very much for the help. – ArK Mar 13 '18 at 14:56