Microsoft SSRS - Add break on character(s)

Question

Good Afternoon,

I have a report on SSRS which grabs a line from a table, this line can have multiple warnings on and each of these warnings begins with either a * or a **.

Some rows may only have one warning, some may have up to 6 and can look something like the below:

* Not Possible * Totally Not Possible ** Do Not Proceed  

I would like there to be a break before every * or ** occurrence. I imagine I will need to include a space before the * so it doesn't split the messages which begin with two **'s and also doesn't include the first instance as there is no space beforehand. Something like " *".

What would be the best way of going about this?

Thank you!

Answer 1

Do you mean a line break so if you have 3 warnings you want them on 3 separate lines within your text block? If so then use an expression to set the textbox value and include vbcrlf in the value. What you'd end up doing is using Split to split the value based upon whatever string criteria you have. Then use Join to combine the values together using vbcrlf as the separator. Excluding some cleanup this expression might work.

   =Join(Split(Fields!Message.Value, " or "), vbCrlf)  

Replace the field name as appropriate. This would remove the or from the string by splitting the value on it. Then the strings would be joined back together with a CRLF. Within a textbox it would display on a separate line for each string.

Answer 2

Hi cooldadtx and thank you for the reply.

This is kind of what I was looking for where I do want 3 separate lines within the text block, however I would like to keep the stars, so it would look something like the following:

 * Not Possible  
 * Totally Not Possible  
 ** Do Not Proceed  

I'm not even sure if this is possible as it will not always be those lines of text, however there will always be a star or two stars at the beginning of each line.

Many thanks

Answer 3

Hi @Lewis

I don't know if my understanding is correct, I achieve the result of yours by using tsql query statement:

  create table testtextsplit  
  (splittest varchar(100))  
  
  insert into testtextsplit  
  select ' * Not Possible * Totally Not Possible ** Do Not Proceed'  
  
  
  select FINALTEXT = concat(N'*',replace(value,N'|',N'*')) from  string_split( (select  replacetext = replace(splittest,N'**',N'*|') from testtextsplit),'*' ) WHERE LEN(value)>0  

Result:

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.