Parent - child 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

Parent - child grouping

Author

Topic

Notoook
Starting Member

18 Posts

Posted - 2009-02-17 : 10:07:40

Greetings all,

I've a table that contains both parent & children via a recursive group. Here is the query

SELECT T1.EmployeeID, T1.Name AS EmpName, T1.ParentID,
	 T2.Name AS ParentName
FROM Employee AS T1 LEFT OUTER JOIN Employee AS T2
ON T2.EmployeeID = T1.ParentID

I placed a group on the table of the ssrs report, the group is on EmployeeID and the parent group is on ParentID.

And what i'm trying to do is the following :


Parent 1
    Sub-parent 1
       Child 1
       Child 2
    Total Sub-parent 1 (group footer)

    Sub-parent2
       Child 1
       Child 2
    Total Sub-parent 2 (group footer)
Total Parent 1 (group footer)

What happens is that every account is considered a group, and the total is displayed right after the name (of either the child or the parent). What should i do in the group expression to make the group on each parent along with its children, to have one footer at the end of it?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 10:29:22

do you mean recursively moving each time until top level?

Notoook
Starting Member

18 Posts

Posted - 2009-02-18 : 02:32:12

I did the recursive move to the top level by a group on the table on the EmployeeID and the parent group is ParentID. Obviously what happens is that first the parent Employee (ParentID = Null) is a group, giving the total right after the name, like that


Parent 1
Total Parent 1(group footer)
    Sub-parent 1
    Total Sub-parent 1 (group footer)
       Child 1
       Child 2
    Sub-parent2
    Total Sub-parent 2 (group footer)
       Child 1
       Child 2

The totals after the parent is not desirable, i want the to be after each parent's children (recursively)


Parent 1
    Sub-parent 1
       Child 1
       Child 2
    Total Sub-parent 1 (group footer)
    Sub-parent2
       Child 1
       Child 2
    Total Sub-parent 2 (group footer)
Total Parent 1 (group footer)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 11:41:17

put root parent as outermost group

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources