recursive hierarchy, would you please try this example
-
I am trying to create a drill down report on a hierarchical data. This can be especially challening if you have a jagged hierarchy. That is, you don't know how deep each branch will go.
After going thru some examples I found online, I came up with my own example to help me learn this. However, I'm stuck on few things and I would like to ask you guys to follow this and help me.
The example is rather simple and should be quick for you to create on your computer.
First, here is the sample data. Run the following create table and insert statement so you can create the same data I am working with.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Hierarchy](
[child] [varchar](50) NULL,
[parent] [varchar](50) NULL,
[somenumber] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Queens', 'NY', 10)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Brooklyn', 'NY', 20)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Bronx', 'NY', 30)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Fort Lee', 'NJ', 5)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Princeton', 'NJ', 15)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Stamford', 'CT', 25)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Hartford', 'CT', 35)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('NewHaven', 'CT', 45)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('NY', 'USA', 0)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('NJ', 'USA', 0)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('CT', 'USA', 0)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('USA', 'North America', 0)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Toronto', 'Ontario', 100)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Ontario', 'Canada', 0)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Canada', 'North America', 0)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('Brazil', 'South America', 200)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('North America', 'World', 0)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('South America', 'World', 0)
INSERT INTO Hierarchy (child,parent,somenumber) VALUES('World', NULL, 0)
SELECT * FROM Hierarchy
If you examine the data, you can see that it's geographical rollup using a parent child relationship. I didn't bother creating any keys because the data is rather simple and I made sure the data doesn't have any bad relationships.
Next, Follow these steps in SSRS to recreate the report I have.
(1) Create a dataset with the following simple select statement.
SELECT
child,
parent,
somenumber
FROM Hierarchy
(2) Add a table in Layout that looks like this.
(3) Right click on the second line (I believe this is call the detail section) and choose "Edit Group".
(4) In the General tab, set the following.
Name: "table1_Detail_Group" (I left this as default.)
Expression: =Fields!child.Value
Parent group: =Fields!parent.Value
(5) In the Visibility tab, set the following.
Choose "Hidden" radio button.
Check the checkbox for the toggle and choose the report item that is located at the left bottom corner in the picture above. For me that report item is called "child".
That should be it.
Now, preview the report, and look for the following.
1. You should be able to drill up and down the regions.
2. But do the region names indent for new levels? Mine doesn't and I don't expect it to yet because I didn't do anything to tell the report to do that. So QUESTION 1: How to make the regions indent?
3. Do the numbers add up? For me, the numbers do show up at the lowest levels, but they are not summing at the higher levels. I thought the expression =sum(fields!somenumber.value) would do this. So QUESTION 2: How to aggregate the number?
-
I figured it out from another forum, but wanted to post back and it will hopefully help someone else.
For the indent, put in the following expression in the left padding property.
= 20 * Level() & "pt"
For the summing, use the following expression.
= sum(fields!somenumber.Value, "table1_Details_Group", recursive)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply