How to group by partial string in SSRS

Malinda Klein
Malinda Klein used Ask the Experts™
on
I am creating a chart inside my SSRS table.  In my table I have ClaimAccount, Quantity, BillingDate and ChargeComments.  I am creating a report that groups on ClaimAccount.  But for a few accounts there are multiple records per month so I want to split out those reports while grouping the others.  ChargeComments has project details that can use for grouping.  Specifically, I want to group on the part of ChargeComments that starts with a slash and all the way to the last letter of the string.  This section of the string is variable length.

How do I add a group in SSRS based upon this section of ClaimAccount that starts with a slash?

Example:

ClaimAccount     BillingDate    ChargeComments
000450                 01012018     DOIXM/agribix2348             this is one group
000450                 01012018     CIXMS/bourlagzip7              this is one group
000520                 01012018     DASFALKG/zip3                    this is one group
000520                 02012018     DASFALKG/zip3                    this is part of the group above because it has the same ClaimAccount and same BillingDate
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Developer
Commented:
GROUP BY SUBSTRING(ChargeComments, CHARINDEX('/', ChargeComments) + 1, LEN(ChargeComments))
Russell FoxDatabase Developer
Top Expert 2014

Commented:
Personally, I'd create a new field in your SQL query, GroupID:

SELECT GroupID = CASE WHEN CHARINDEX('/', ChargeComments) > 0 THEN LEFT(ChargeComments, CHARINDEX('/', ChargeComments) - 1) ELSE ChargeComments END

Open in new window

Doug BishopDatabase Developer

Commented:
Russell, he wants to group on the characters following the /, not before it.

Author

Commented:
Sorry for the delay in rating.  I thought I had done this long ago.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial