Displaying data in a Tree ... GROUP BY? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Displaying data in a Tree ... GROUP BY?

Author

Topic

LaurieCox

158 Posts

Posted - 2007-09-27 : 10:49:42

Hi,

I am new to Reporting Services and am trying to produce what (at least to me) seems like a complicated report. The report should print data in a tree view and suppress the printing of any parents that two or more children have in common.

I have the following data (Ddl and insert statements for this table at end of post):


SortID	CatagoryType	IndentLevel	ItemName		ItemText
A	L		0		TestLinks3	
A	P		1		A New Problem		to test sorting stuff
A	G		2		Self Management		Self Management
A	O		3		Groups			Participation in Social Groups
A	I		4		Community Intervention	Community Intervention
B	L		0		TestLinks3	
B	P		1		Productivity		Productivity
B	O		2		Access			Access to Community Resources
C	L		0		TestLinks3	
C	P		1		Supportive Needs	Supportive Needs
C	G		2		Community		Community Participation Level
C	O		3		Access			Access to Community Resources
D	L		0		TestLinks3	
D	P		1		Supportive Needs	Supportive Needs
D	G		2		Community		Community Participation Level
D	O		3		Activities		Participation in Community Activities
E	L		0		TestLinks3	
E	P		1		Supportive Needs	Supportive Needs
E	G		2		Social			Social Interaction Level
E	O		3		Activities		Participation in Community Activities
F	L		0		TestLinks3	
F	O		1		Activities		Participation in Community Activities

In order to avoid an extra wide post I have replaced the sortId field values with a letter where: 
A = TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention
B = TestLinks3.1.Productivity.3.Access
C = TestLinks3.1.Supportive Needs.2.Community.3.Access
D = TestLinks3.1.Supportive Needs.2.Community.3.Activities
E = TestLinks3.1.Supportive Needs.2.Social.3.Activities
F = TestLinks3.3.Activities

Notes:

SortId makes sure that all nodes of a given branch are sorted together in the correct order

IndentLevel makes sure that children follow parents on any given branch and determines how deep to indent the given node. I cannot use CategoryType to determine indent as a Category can be on any level.

CategoryType is used to determine what precedes the ItemName on the report. E.g. Category of L will place the string: "Library: " in front of the ItemName

The tree output should look something like this:


Library: TestLinks3 
    Problem: A New Problem - to test sorting stuff
        Goal: Self Management - Self Management
            Objective: Groups - Participation in Social Groups
                Intervention: Community Intervention - Community Intervention
Library: TestLinks3 
    Problem: Productivity - Productivity
        Objective: Access - Access to Community Resources
Library: TestLinks3
    Problem: Supportive Needs - Supportive Needs
        Goal: Community - Community Participation Level
            Objective: Access - Access to Community Resources
Library: TestLinks3
    Problem: Supportive Needs - Supportive Needs
        Goal: Community - Community Participation Level
            Objective: Activities - Participation in Community Activities
Library: TestLinks3
    Problem: Supportive Needs - Supportive Needs
        Goal: Social - Social Interaction Level
            Objective: Activities - Participation in Community Activities
Library: TestLinks3 
    Objective: Activities - Participation in Community Activities

But with the duplicate parents suppressed (the lines with a strickthru above), so that the final report will look like this:


Library: TestLinks3 - 
    Problem: A New Problem - to test sorting stuff
        Goal: Self Management - Self Management
            Objective: Groups - Participation in Social Groups
                Intervention: Community Intervention - Community Intervention
    Problem: Productivity - Productivity
        Objective: Access - Access to Community Resources
    Problem: Supportive Needs - Supportive Needs
        Goal: Community - Community Participation Level
            Objective: Access - Access to Community Resources
            Objective: Activities - Participation in Community Activities
        Goal: Social - Social Interaction Level
            Objective: Activities - Participation in Community Activities
    Objective: Activities - Participation in Community Activities

Note: Duplicate children of different parents should not be suppressed. E.g. Objective: Activities - Participation in Community Activities is found in three places in the tree, but it has three parents and therefore needs to be in all three places.

I had an earlier thread about this (before I had all the particulars of the data ironed out): Sidebar: Uniquely defined Hierarchical Data to see if I was on the right track as far as getting this data in a format for reporting services. I got this one response:

quote:

This is a normal typical question for anyone new to RS. Your problem is very simple as this is exactly what RS can do. In fact the indentation that you have stated can be simulated using GROUP BY rows as well as a final details section. Each indentation is a group by. They follow one another I assume because you have foreign keys.

… so, I think my answer lies in GROUP BY and I am currently reading about it. But I am having trouble figuring out how to apply it to this data. If anyone can give me pointers on what to do or where to look I would greatly appreciate it.

Thanks,

Laurie

This is the code to get the test data (referenced in the post) into the table #ExpandedTree.


create table #ExpandedTree
 (sortId       varchar(300),
  CatagoryType char(1), -- A character representing the catagory:
                        -- (L)ibrary, (P)roblem, (G)oal, (O)bjective, (I)ntervention
  IndentLevel  Int,     -- When displayed in the tree view the indention level of the item
  ItemName     varchar(50), -- for reasons that do not matter called wiley_<category>_id 
                            -- in the <catagory>_data tables.
  ItemText     varchar(5000))

insert into #ExpandedTree
SELECT 'TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention', 
                'L',0,'TestLinks3',NULL UNION ALL
SELECT 'TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention', 
                'P',1,'A New Problem','to test sorting stuff' UNION ALL
SELECT 'TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention', 
       'G',2,'Self Management','Self Management' UNION ALL
SELECT 'TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention', 
       'O',3,'Groups','Participation in Social Groups' UNION ALL
SELECT 'TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention', 
       'I',4,'Community Intervention','Community Intervention' UNION ALL
SELECT 'TestLinks3.1.Productivity.3.Access', 
       'L',0,'TestLinks3',NULL UNION ALL
SELECT 'TestLinks3.1.Productivity.3.Access', 
       'P',1,'Productivity','Productivity' UNION ALL
SELECT 'TestLinks3.1.Productivity.3.Access', 
       'O',2,'Access','Access to Community Resources' UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Community.3.Access', 
        'L',0,'TestLinks3',NULL UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Community.3.Access', 
        'P',1,'Supportive Needs','Supportive Needs' UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Community.3.Access', 
       'G',2,'Community','Community Participation Level' UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Community.3.Access', 
       'O',3,'Access','Access to Community Resources' UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Community.3.Activities', 
       'L',0,'TestLinks3',NULL UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Community.3.Activities', 
       'P',1,'Supportive Needs','Supportive Needs' UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Community.3.Activities', 
       'G',2,'Community','Community Participation Level' UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Community.3.Activities', 
       'O',3,'Activities','Participation in Community Activities' UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Social.3.Activities', 
       'L',0,'TestLinks3',NULL UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Social.3.Activities', 
       'P',1,'Supportive Needs','Supportive Needs' UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Social.3.Activities', 
       'G',2,'Social','Social Interaction Level' UNION ALL
SELECT 'TestLinks3.1.Supportive Needs.2.Social.3.Activities', 
       'O',3,'Activities','Participation in Community Activities' UNION ALL
SELECT 'TestLinks3.3.Activities', 
       'L',0,'TestLinks3',NULL UNION ALL
SELECT 'TestLinks3.3.Activities', 
       'O',1,'Activities','Participation in Community Activities'

SELECT * FROM #ExpandedTree ORDER BY sortId, IndentLevel

heze
Posting Yak Master

192 Posts

Posted - 2007-09-27 : 12:37:03

look for recursion in reporting services, its really easy and you dont need to code anything

LaurieCox

158 Posts

Posted - 2007-09-27 : 16:21:41

quote:
Originally posted by heze

look for recursion in reporting services, its really easy and you dont need to code anything

Hi heze,

Well I have been reading about recursion. It looks like I am going to have to go back to my SP and add links from children to parents in order to make use of recursion.

Anyway, I'm away from the office tomorrow, so I will be working on this Monday. Might have more questions by than.

If anybody can see how I can do this without adding the links (though I don't think it will be to hard to do it), let me know.

Thanks for the response,

Laurie

LaurieCox

158 Posts

Posted - 2007-10-02 : 13:16:07

Ok …

I went back to my stored procedure and added two columns (ItemId and ParentId) to the result set.

This produced the following data (see end of post for script to reproduce this table):


IID	PId	SId	CT	Level	ItemName		ItemText
L11		A	L	0	TestLinks3	
P90	L11	A	P	1	A New Problem		to test sorting stuff
G433	P90	A	G	2	Self Management		Self Management
O1264	G433	A	O	3	Groups			Participation in Social Groups
I2473	O1264	A	I	4	Community Intervention	Community Intervention
L11		B	L	0	TestLinks3	
P6	L11	B	P	1	Productivity		Productivity
O1265	P6	B	O	2	Access			Access to Community Resources
L11		C	L	0	TestLinks3	
P7	L11	C	P	1	Supportive Needs	Supportive Needs
G427	P7	C	G	2	Community		Community Participation Level
O1265	G427	C	O	3	Access			Access to Community Resources
L11		D	L	0	TestLinks3	
P7	L11	D	P	1	Supportive Needs	Supportive Needs
G427	P7	D	G	2	Community		Community Participation Level
O1266	G427	D	O	3	Activities		Participation in Community Activities
L11		E	L	0	TestLinks3	
P7	L11	E	P	1	Supportive Needs	Supportive Needs
G428	P7	E	G	2	Social			Social Interaction Level
O1266	G428	E	O	3	Activities		Participation in Community Activities
L11		F	L	0	TestLinks3	
O1266	L11	F	O	1	Activities		Participation in Community Activities

Note: In order to avoid an extra wide post I shortened the column headers and replaced the sortId field values with a letter where:

A = TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention
B = TestLinks3.1.Productivity.3.Access
C = TestLinks3.1.Supportive Needs.2.Community.3.Access
D = TestLinks3.1.Supportive Needs.2.Community.3.Activities
E = TestLinks3.1.Supportive Needs.2.Social.3.Activities
F = TestLinks3.3.Activities

Following the directions in the book Professional SQL Server 2005 Reporting Services) I set up a recursive relationship:

On the detail group I put =Fields!ItemId.Value in the expression’s box and set the parent group to =Fields!ParentId.Value.

I put a table control on the report and in the first detail cell put the expression:


=Switch(Fields!CatagoryType.Value = "L", "Library: " & Fields!ItemName.Value,
       Fields!CatagoryType.Value = "P", "Problem: " & Fields!ItemName.Value,
       Fields!CatagoryType.Value = "G", "Goal: " & Fields!ItemName.Value,
       Fields!CatagoryType.Value = "O", "Objective: " & Fields!ItemName.Value,
       Fields!CatagoryType.Value = "I", "Intervention: " & Fields!ItemName.Value)

I set the padding left property (of the first detail cell) to: =level("table1_Details_Group") * 20 & "pt"

In the second detail cell I put the expresion: =level("table1_Details_Group") + 1

After running the report I ended up with this:


Library: TestLinks3                                    1
    Problem: A New Problem                             2
        Goal: Self Management                          3
            Objective: Groups                          4
                Intervention: Community Intervention   5
    Problem: Productivity                              2
        Objective: Access                              3
    Problem: Supportive Needs                          2
        Goal: Community                                3
            Objective: Activities                      4
        Goal: Social                                   3

This suppresses too many lines. It suppresses duplicate parents, but it also suppresses duplicated children of different parents.

The following is what the tree would look like if no rows got suppressed:


Library: TestLinks3 
    Problem: A New Problem 
        Goal: Self Management 
            Objective: Groups 
                Intervention: Community Intervention 
Library: TestLinks3 
    Problem: Productivity 
        Objective: Access 
Library: TestLinks3
    Problem: Supportive Needs 
        Goal: Community 
            Objective: Access 
Library: TestLinks3
    Problem: Supportive Needs 
        Goal: Community 
            Objective: Activities 
Library: TestLinks3
    Problem: Supportive Needs 
        Goal: Social 
            Objective: Activities 
Library: TestLinks3 
    Objective: Activities

The rows with the strike thru are the rows that are suppressed and that I want suppressed.
The red are the rows that are suppressed and that I DO NOT want suppressed.

As you can see Objective: Activities appears three times in the table but it has a different parent each time and there fore it should not be suppressed.

How do I make this work?

Thanks,

Laurie

Script to create sample data table (with new columns):


create table #ExpandedTree
 (ItemId       varchar(10),
  ParentId     varchar(10),
  sortId       varchar(300),
  CatagoryType char(1), -- A character representing the catagory:
                        -- (L)ibrary, (P)roblem, (G)oal, (O)bjective, (I)ntervention
  IndentLevel  Int,     -- When displayed in the tree view the indention level of the item
  ItemName     varchar(50), -- for reasons that do not matter called wiley_<category>_id 
                            -- in the <catagory>_data tables.
  ItemText     varchar(5000))

insert into #ExpandedTree
select 'G427', 'P7', 'TestLinks3.1.Supportive Needs.2.Community.3.Access', 
       'G',2,'Community', 'Community Participation Level' UNION ALL
select 'G427', 'P7', 'TestLinks3.1.Supportive Needs.2.Community.3.Activities', 
       'G',2,'Community', 'Community Participation Level' UNION ALL
select 'G428', 'P7', 'TestLinks3.1.Supportive Needs.2.Social.3.Activities', 
       'G',2,'Social', 'Social Interaction Level' UNION ALL
select 'G433', 'P90', 'TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention', 
       'G',2,'Self Management', 'Self Management' UNION ALL
select 'I2473', 'O1264', 'TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention', 
       'I',4,'Community Intervention', 'Community Intervention' UNION ALL
select 'L11', NULL, 'TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention', 
       'L',0,'TestLinks3', NULL UNION ALL
select 'L11', NULL, 'TestLinks3.1.Productivity.3.Access', 
       'L',0,'TestLinks3', NULL UNION ALL
select 'L11', NULL, 'TestLinks3.1.Supportive Needs.2.Community.3.Access', 
       'L',0,'TestLinks3', NULL UNION ALL
select 'L11', NULL, 'TestLinks3.1.Supportive Needs.2.Community.3.Activities', 
       'L',0,'TestLinks3', NULL UNION ALL
select 'L11', NULL, 'TestLinks3.1.Supportive Needs.2.Social.3.Activities', 
       'L',0,'TestLinks3', NULL UNION ALL
select 'L11', NULL, 'TestLinks3.3.Activities', 'L',0,'TestLinks3', NULL UNION ALL
select 'O1264', 'G433', 'TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention', 
       'O',3,'Groups', 'Participation in Social Groups' UNION ALL
select 'O1265', 'G427', 'TestLinks3.1.Supportive Needs.2.Community.3.Access', 
       'O',3,'Access', 'Access to Community Resources' UNION ALL
select 'O1265', 'P6', 'TestLinks3.1.Productivity.3.Access', 
       'O',2,'Access', 'Access to Community Resources' UNION ALL
select 'O1266', 'G427', 'TestLinks3.1.Supportive Needs.2.Community.3.Activities', 
       'O',3,'Activities', 'Participation in Community Activities' UNION ALL
select 'O1266', 'G428', 'TestLinks3.1.Supportive Needs.2.Social.3.Activities', 
       'O',3,'Activities', 'Participation in Community Activities' UNION ALL
select 'O1266', 'L11', 'TestLinks3.3.Activities', 
       'O',1,'Activities', 'Participation in Community Activities' UNION ALL
select 'P6', 'L11', 'TestLinks3.1.Productivity.3.Access', 
       'P',1,'Productivity', 'Productivity' UNION ALL
select 'P7', 'L11', 'TestLinks3.1.Supportive Needs.2.Community.3.Access', 
       'P',1,'Supportive Needs', 'Supportive Needs' UNION ALL
select 'P7', 'L11', 'TestLinks3.1.Supportive Needs.2.Community.3.Activities', 
       'P',1,'Supportive Needs', 'Supportive Needs' UNION ALL
select 'P7', 'L11', 'TestLinks3.1.Supportive Needs.2.Social.3.Activities', 
       'P',1,'Supportive Needs', 'Supportive Needs' UNION ALL
select 'P90', 'L11', 'TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention', 
       'P',1,'A New Problem', 'to test sorting stuff' 

select Itemid,
       ParentId,
       sortId,
       CatagoryType,
       IndentLevel,
       ItemName,
       ItemText
  from #ExpandedTree order by sortid, IndentLevel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources