Rpt Builder-extracting partial text from large text field until a period is found

[ldwf]

In SSR/SReport Builder, I need to search a large text field for a specific value, and return that value and return everything to the right of that value until a period is found. For example, I have a large text field and I need to search for the text 'Business Requirement IDs:' inside this large field and return this text and everything after it until a period is found. So if the text field value is "Beginning of text. Business Requirement IDs: 01-Export Data to Excel, 02-Export Data to PDF. Rest of text field" then I only want to return "Business Requirement IDs: 01-Export Data to Excel, 02-Export Data to PDF". Do I need to break this down into two parts? i.e, first search for the txt value and return everything to the right of it and then a second field to extract everything from this field until a period is found?

[Anonymous · Accepted solution]

Hi, @ldwf 

I am glad to help you.

Since you didn't give me some data to test, I assumed a simple dataset in SQL Server myself:

Connect to SQL Server using Microsoft Report Builder and select the corresponding database and the corresponding table:

You can create four Calculated Fields to realize the requirements in steps:

StartPosition:

=InStr(Fields!Description.Value, "Business Requirement IDs:")

SubStringFromStart:

=Mid(Fields!Description.Value, Fields!StartPosition.Value)

FirstPeriodPosition:

=InStr(Fields!SubStringFromStart.Value, ".")

FinalExtractedText:

=Mid(Fields!Description.Value, Fields!StartPosition.Value, Fields!FirstPeriodPosition.Value)

Drag the Calculated Fields to the Table/Matrix and click Run:

Output:

Of course you can also create only one Calculated Field directly to fulfill the requirement:

Description1:

=Mid(Fields!Description.Value, InStr(Fields!Description.Value, "Business Requirement IDs:"), InStr(Mid(Fields!Description.Value, InStr(Fields!Description.Value, "Business Requirement IDs:")), "."))

Drag the Calculated Field to the Table/Matrix and click Run:

Output:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ldwf]

I did get this to work following your instructions, and will therefore accept it as the solution. Thank you

[ldwf]

Thanks for the response.  Unfortunately, it doesn't work , although I do not get any error messages.  I will provide you more details now..  The data does reside in SQL server.  The screenshot shows you how it looks when I query it in SQL server where you can see the html characters.  While I don't get any error messages, the first of the four fields defined always returns a value of 0.  Maybe this is because of the html?  Since this field isn't correct, the other fields won't be either.  Here is a screenshot of the data in SQL server.  :