Reporting a Tree

Question

Post reply

Reporting a Tree

Ramón Ontiveros

SSC Veteran

Points: 289
More actions
June 17, 2009 at 10:37 am

#384793

Hi to all, I’m working right now in some data whit a tree structure of n levels. And I was wondering if is possible to report the tree in a report of reporting services. The principal reason to do this is for use the groping (hide/show) feature of a report table. I have been thinking of some way to do it but I haven’t found a way. I don’t know if someone has made a work around this problem.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WilliamBendall SSCrazy Points: 2591 More actions · Answer 1

can you be a bit more specific around the structure of your table and the data it contains?

e.g.:

unqKey parentKey value1

1 1 topoftree

2 1 undertopoftree

3 3 nextintree

4 3 etc.

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

WilliamBendall SSCrazy Points: 2591 More actions · Answer 2

Actually Ramon, I'm pretty new to this forum. Perhaps we should stick to the guidelines in Jeff Moden's article.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

regards

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

Ramón Ontiveros SSC Veteran Points: 289 More actions · Answer 3

Hi, the structure and data of the tree is like this:

create table #tree

(

id int PRIMARY KEY CLUSTERED identity,

parent_id int,

node_name varchar(225)

)

SET IDENTITY_INSERT #tree ON

-- Root nodes

insert into #tree (id,parent_id,node_name)

select 1,0,'Root node 1' union all

select 2,0,'Root node 2' union all

select 3,0,'Root node 3'

-- Childs of Root node 1

insert into #tree (id,parent_id,node_name)

select 4,1,'Child of node ' union all

select 5,4,'Child of a child' union all

select 6,1,'Child of node 1' union all

select 7,6,'Child of a child' union all

select 8,1,'Child of node 1' union all

select 9,8,'Child of a child'

-- Childs of Root node 2

insert into #tree (id,parent_id,node_name)

select 10,2,'Child of node 2' union all

select 11,10,'Child of a child' union all

select 12,2,'Child of node 2' union all

select 13,12,'Child of a child' union all

select 14,2,'Child of node 2' union all

select 15,14,'Child of a child'

-- Childs of Root node 3

insert into #tree (id,parent_id,node_name)

select 16,3,'Child of node 3' union all

select 17,16,'Child of a child' union all

select 18,3,'Child of node 3' union all

select 19,18,'Child of a child' union all

select 20,3,'Child of node 3' union all

select 21,20,'Child of a child'

SET IDENTITY_INSERT #tree OFF

And generate a tree like this:

Root node 1

|

|------ Child of node 1

|

|-----------Child of a child

|------ Child of node 1

|

|-----------Child of a child

|------ Child of node 1

|

|-----------Child of a child

Root node 2

|

|------ Child of node 2

|

|-----------Child of a child

|------ Child of node 2

|

|-----------Child of a child

|------ Child of node 2

|

|-----------Child of a child

Root node 3

|

|------ Child of node 3

|

|-----------Child of a child

|------ Child of node 3

|

|-----------Child of a child

|------ Child of node 3

|

|-----------Child of a child

The thing is that the tree can have n levels. So I don't know if is there a way of specify this kind of grouping in a reporting service.

Josh Turner-440822 SSCrazy Points: 2048 More actions · Answer 4

As long as you're using SSRS 2008 and you know the parent id this should be possible. In your report, go to the group properties for the Detail level of the group. To do this, right click the area around the report and select View->Grouping (if it's not already visible) then right click the Details portion of the Row Groups section and go to Group Properties. Here, group on the primary id in your dataset and then go to the Advanced selection in the Group Properties. Here set the Recursive Parent to the parent id in your dataset. This should group everything based on the parent id no matter how many levels there are. An easy work-around to get the data to display in a stair-step fashion is to go to the properties of the text box that holds the data and in the Padding property set the padding for the Left to "=CStr(2 + (Level()*14)) + "pt". This uses the built-in function "Level" to determine what level the row is in the hierarchy. You can play around with the numbers in the expression to achieve the amount of padding you want.

Ramón Ontiveros SSC Veteran Points: 289 More actions · Answer 5

Woo, SSRS Rock's. Thank you very much, I thought it wasn't possible. The table shows excellent. I post an Image of the report.