Recursive Grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Recursive Grouping

Author

Topic

Notoook
Starting Member

18 Posts

Posted - 2009-02-08 : 11:07:42

I have a table that is grouped on Fields!AccountID.Value and the parent group is Fields!ParentID.Value. The group is called "table1_Group1"

The details is grouped on Fields!AccountID.Value and the parent group is Fields!ParentID.Value in order not to show the account more than once in the table details section. The group is called "table1_Details_Group".

My question is; I want a total on the details level (account level) and if the account has parent account(s), the summation must include the summation of the parent level(s).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-08 : 21:26:07

can you illustrate this with the help of some data?

Notoook
Starting Member

18 Posts

Posted - 2009-02-09 : 02:31:43

Ok...here is a part of a chart of accounts

Assets
* Fixed Assets
** Lands
*** Resorts
**** Resort1 ( leaf node)
* Running Assets
** Banks
*** Bank1 (leaf node)
*** Bank2 (leaf node)
--------------------------------------

now, all the parent accounts can't be part of a transaction, leaf ones accept transactions, let's say that

Resort1 -> has a transaction with $500
Bank1-> has a transaction with $1000
Bank2-> has a transaction with $1200

I want the Total of Resorts = $500. The total of Lands = $500. The total of Fixed Assets = $500.

The total of Banks = $2200. The Total of Running Assets = $2200. The total of Assets = 500 + 2200 = $2700.

I don't know how to propagate the total to the upper level.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 03:57:09

thats simple . In expression for top level group just specify the scope parameter. like

=SUM(Fields!field.value,"your group name")....

Notoook
Starting Member

18 Posts

Posted - 2009-02-09 : 04:19:22

I tried the scope parameter in the sum function but it is not working.

the groups i have in the table
1) Grouped on "Fields!AccountID.Value", the parent group "Fields!ParentID.Value".

2) Table Details section grouped on "Fields!AccountID.Value", the parent group "Fields!ParentID.Value".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 04:21:23

why what happened when you used scope?

Notoook
Starting Member

18 Posts

Posted - 2009-02-09 : 04:31:58

the summation is for the leaf accounts level only

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 04:54:07

which group name did you specify in scope?

Notoook
Starting Member

18 Posts

Posted - 2009-02-09 : 05:10:58

the scope's group name = "table1_Group1"

Notoook
Starting Member

18 Posts

Posted - 2009-02-09 : 06:59:19

And here is the query:


 SELECT Account.AccountID, Account.Name, Account.ParentID, AccountValue

FROM Transaction INNER JOIN TransactionDetails

ON (Transaction.ID = TransactionDetails.Trans_ID )

RIGHT OUTER JOIN Account ON TransactionDetails.AccountID = Account.AccountID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 10:15:23

=SUM(Fields!AccountValue.value,"table1_Group1")....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources