How to group by partial string in SSRS

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?


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
Malinda KleinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Doug BishopDatabase DeveloperCommented:
GROUP BY SUBSTRING(ChargeComments, CHARINDEX('/', ChargeComments) + 1, LEN(ChargeComments))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Russell FoxDatabase DeveloperCommented:
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 DeveloperCommented:
Russell, he wants to group on the characters following the /, not before it.
Malinda KleinAuthor Commented:
Sorry for the delay in rating.  I thought I had done this long ago.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.