SSRS Report Builder parameter match one to many and return the sum total of the many matches

Hi there, after trying for three days, i'm asking for assistance. Not sure how to post this, so here goes.
I’m building a drill down ledger report in Report Builder and need to return a SUM of the total posting amounts of the matched child codes based off of the parent code.

Basically this is the flow.

Field 1 - return store
Field 2 – return statement code
Field 3 – return posting dollar amount of code in Field 2

Logic for Field 3:
If parent code in field 2 has one or more associated child codes then lookup and sum the posting dollar amount for all the child codes and return that total.
If no child codes exist, then simply ‘return posting dollar amount of code in Field 2‘

What’s the best way to handle this, and would you have any sample code to get me started or through this.

Much appreciation
Calvin LeBlancReporting EngineerAsked:
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.

Russell FoxDatabase DeveloperCommented:
Can you give us a little information on your database schema? I.e., is it
tblStores: ID, Name
tblCodes: ID, ParentID(can be null), Name

Open in new window

Or more broken apart, like
tblStores: ID, Name
tblCodes: ID, Name
tblChildCodes: ID, ParentID (not null), Name

Open in new window

I'm not very familiar with ReportBuilder but have a lot of experience with creating SSRS reports in bids/VS, so I may be able to help with your report query.
Calvin LeBlancReporting EngineerAuthor Commented:
Thanks for the assist Russell

To simplify as much as possible, we can eliminate the ‘store’ for now.  I’ll be using that for grouping later.  
I have a dataset built off of two views , inner joined on company ID and account number

Example:
Select
T1.code
T2.postingamount
From View1 T1 Inner Join View2  T2 ON  T1.companyID = T2.companyID  AND T1.acctnum = T2. Acctnum

I created a reference table which the SQL admin is uploading for me in order to reference the T1.code for any child codes if they exist.  (this next part is where I’m failing) if child codes exist (could be 1 and up to 8 ) create an array of the child codes and then SUM the associated T2.postingamount for each and return that SUM result. If no child codes exist, then I just need to pull the T2.postingamount
 for T1.code

I hope this adds the clarity you are looking for.
Russell FoxDatabase DeveloperCommented:
Maybe this will help: you can use a LEFT JOIN to include child codes only if they exist, and then a COALESCE to pick the first non-NULL value (I'm guessing at the T3 structure). When you say "array" of child nodes, do you need to show the child nodes, or is just a sum of their values okay? This assumes just a sum:
SELECT
	T1.code
	, COALESCE(SUM(T3.PostingAmount), T2.postingamount, 0.00) AS PostingAmount
FROM View1 T1 
	INNER JOIN View2  T2 
		ON  T1.CompanyID = T2.CompanyID  
		AND T1.AcctNum = T2. AcctNum
	LEFT JOIN ChildCodes T3
		ON T3.ParentCode = T1.Code
		AND T3.AcctNum = T2.AcctNum

Open in new window

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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Calvin LeBlancReporting EngineerAuthor Commented:
Thanks for the update Russell.
I see where you're going with this.
A sum of the child code 'postingamounts' from T2 (returned as column2) is my goal to produce along side of the parent code (returned in column1).
They still do not have my reference table up yet, but when they do, i'll test this and let you know sir.
I really appreciate the suggestion/direction.

Calvin
Russell FoxDatabase DeveloperCommented:
No problem at all. I can't count the number of times I've banged my head against a problem for three days. So frustrating.
Calvin LeBlancReporting EngineerAuthor Commented:
Hi Russell, the Left Join concept to a third reference table worked for my solution.  Thank you for taking time to share your thoughts.

I built a reference table (T3) and used the Left Join to relate the parent codes to the child codes (one to many). It is actually going back to the fundamentals elements rather than getting elaborate with a stored procedure or custom function. The only issue I ran up against was in my first join. Unknowingly in the ‘ON’ reference, I had a one to many reference, rather than  a one to one reference, which was causing the query to return more records than actually existed, which seems impossible, but actually is possible. To resolve the issue, I added an additional key reference to assure I was getting a one to one reference and the results started populating correctly, without the extra records.

Thanks again for our time.


Calvin
Calvin LeBlancReporting EngineerAuthor Commented:
Thanks again for the assist Russell.

Calvin
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
SSRS

From novice to tech pro — start learning today.