Creating Subtotals Based on a Substring of a Field in SSRS

Hi Experts,

I am using SSRS and I want to create a subtotal on based on the first 7 character of the AccountID field.

So the example attached, I would have a subtotal for the account 101-002-xx,  101-007-xx and 102-002-xx.

Screenshot_1.png
jnsimexAsked:
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.

Bud DurlandDirector of ITCommented:
Never used SSRS, but bsing SUBSTR should get you what you need:

select SUBSTR(AccountID,1,7), sum(Amount) from <table> group by SUBSTR(AccountID1,7)

Open in new window

jnsimexAuthor Commented:
Ok I was able to add a subtotal to the group of accounts using the mid function but how do I add a total for only a certain account grouping?

In the screen shot below, I want to add a subtotal for all the accounts that begin with 102-xxx i.e. 10,488.57+10,245.71

Screenshot_2.png
irudykCommented:
One idea is from your report's data set could you not created a calculated field based on the first 3 characters
  Left(AccountID,3)
Then add a grouping based on the calculated field that has it's own footer and place the subtotal calculation in that area
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
Regular Expressions

From novice to tech pro — start learning today.