grouping

I have a parent group and a child group. How do you add a child group to the child group?

parent
child
child

  • Edited by ForumHelp7 Wednesday, July 22, 2015 4:24 AM
July 22nd, 2015 4:21am

It's added to the left column instead of below.

parentgroup
child group
child group

Sample data needs to appear like this.

GRAND DAD

dad

mom
child
child
child



  • Edited by ForumHelp7 Wednesday, July 22, 2015 4:54 AM
Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 4:47am

CREATE TABLE [dbo].[FAMILY](
	[GP_ID] [int] NULL,
	[P_ID] [int] NULL,
	[C_ID] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[GRAND_PARENT](
	[ID] [int] NULL,
	[DESCR] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[PARENT](
	[ID] [int] NULL,
	[DESCR] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[CHILD](
	[ID] [int] NULL,
	[DESCR] [varchar](50) NULL
) ON [PRIMARY]

INSERT INTO [TEST].[dbo].[GRAND_PARENT] VALUES (1,'GrandParent1')
INSERT INTO [TEST].[dbo].[GRAND_PARENT] VALUES (2,'GrandParent2')
INSERT INTO [TEST].[dbo].[GRAND_PARENT] VALUES (3,'GrandParent3')
INSERT INTO [TEST].[dbo].[GRAND_PARENT] VALUES (4,'GrandParent4')
INSERT INTO [TEST].[dbo].[GRAND_PARENT] VALUES (5,'GrandParent5')

INSERT INTO [TEST].[dbo].[PARENT] VALUES (1,'Parent1')
INSERT INTO [TEST].[dbo].[PARENT] VALUES (2,'Parent2')
INSERT INTO [TEST].[dbo].[PARENT] VALUES (3,'Parent3')
INSERT INTO [TEST].[dbo].[PARENT] VALUES (4,'Parent4')
INSERT INTO [TEST].[dbo].[PARENT] VALUES (5,'Parent5')
INSERT INTO [TEST].[dbo].[PARENT] VALUES (6,'Parent6')
INSERT INTO [TEST].[dbo].[PARENT] VALUES (7,'Parent7')
INSERT INTO [TEST].[dbo].[PARENT] VALUES (8,'Parent8')
INSERT INTO [TEST].[dbo].[PARENT] VALUES (9,'Parent9')
INSERT INTO [TEST].[dbo].[PARENT] VALUES (10,'Parent10')

INSERT INTO [TEST].[dbo].[CHILD] VALUES (1,'Child1')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (2,'Child2')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (3,'Child3')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (4,'Child4')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (5,'Child5')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (6,'Child6')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (7,'Child7')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (8,'Child8')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (9,'Child9')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (10,'Child10')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (11,'Child11')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (12,'Child12')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (13,'Child13')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (14,'Child14')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (15,'Child15')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (16,'Child16')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (17,'Child17')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (18,'Child18')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (19,'Child19')
INSERT INTO [TEST].[dbo].[CHILD] VALUES (20,'Child20')

INSERT INTO [TEST].[dbo].[FAMILY] VALUES (1,1,1)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (1,1,2)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (1,2,3)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (1,2,4)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (2,3,5)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (2,3,6)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (2,4,7)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (2,4,8)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (3,5,9)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (3,5,10)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (3,6,11)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (3,6,12)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (4,7,13)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (4,7,14)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (4,8,15)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (4,8,16)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (5,9,17)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (5,9,18)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (5,10,19)
INSERT INTO [TEST].[dbo].[FAMILY] VALUES (5,10,20)

  • Edited by ForumHelp7 Wednesday, July 22, 2015 5:59 AM
July 22nd, 2015 5:58am

Hi Forum help7,

Based on your dataset.Below is my dataset query

SELECT DISTINCT GP.DESCR AS GrandPaa, PA.DESCR AS Parent, CH.DESCR AS Child
FROM     FAMILY AS FA INNER JOIN
                  GRAND_PARENT AS GP ON FA.GP_ID = GP.ID INNER JOIN
                  PARENT AS PA ON FA.P_ID = PA.ID INNER JOIN
                  CHILD AS CH ON FA.P_ID = CH.ID

At the report level I have a grouping on GrandPaa column and this is how the output looks like

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 6:16am

SELECT
GP.DESCR
, P.DESCR
, C.DESCR
FROM dbo.FAMILY F
INNER JOIN dbo.GRAND_PARENT GP ON F.GP_ID = GP.ID
INNER JOIN dbo.PARENT P ON F.P_ID = P.ID
INNER JOIN dbo.CHILD C ON F.C_ID = C.ID

This looks like this.

GrandParent1 Parent1 Child1
GrandParent1 Parent1 Child2
GrandParent1 Parent2 Child3
GrandParent1 Parent2 Child4
GrandParent2 Parent3 Child5
GrandParent2 Parent3 Child6
GrandParent2 Parent4 Child7
GrandParent2 Parent4 Child8
GrandParent3 Parent5 Child9
GrandParent3 Parent5 Child10
GrandParent3 Parent6 Child11
GrandParent3 Parent6 Child12
GrandParent4 Parent7 Child13
GrandParent4 Parent7 Child14
GrandParent4 Parent8 Child15
GrandParent4 Parent8 Child16
GrandParent5 Parent9 Child17
GrandParent5 Parent9 Child18
GrandParent5 Parent10 Child19
GrandParent5 Parent10 Child20

It needs to look like this, etc

GrandParent1
Parent1
Parent2
Child1
Child2
Child3
Child4
GrandParent2
Parent3
Parent4
Child5
Child6
Child7
Child8

July 22nd, 2015 7:18am

Hi ForumHelp7

Is this what you are looking for as the end report  ??

Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 11:28am

Hi ForumHelp7,

Based on your sample data, its not able to design a report as your expected results within one tablix. As we tested in our environment, you can create three tablixes in you report, and hide some rows or columns. Then embed those tablixes in a list. Please refer to screenshots below:


Reference:
Lists (Report Builder and SSRS)

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

July 22nd, 2015 11:23pm

This can be achieved in a very simple way.

1.Take a list control

2. Drag three tables in it and keep only one column for each table.

3. Allign the tables below one another. and the data column in each column must be followed in the order of Grandfather(firstTable)>Parent(Second Table)>Child(third Table).

4Now go the row grouping of the list control(i.e Deatils group) Add group by Grandfather.

After setting all the controls it would look something like the below image, Here I have highlighted the parent column which is the second table and on top of it is grandfather column and below is the child column.Remember there is no grouping at any of the individual table.

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 12:43am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics