Parent - Child Hierarchy in SQL Server 2008 Reporting Service with Operational Source

Parent >> Child Hierarchy in SQL Server 2008 Reporting Service with Operational Source


I followed the below articles and i could build the Parent child Hierarchy in SSRS 2008 using Relational Database Source.

If you are ok with this look and feel then you can follow this.










http://www.mssqltips.com/tip.asp?tip=1939

http://blogs.msdn.com/b/robertbruckner/archive/2008/10/14/using-analysis-services-parent-child-hierarchies-in-reports.aspx


There are 4 Steps need to be done to do the SSRS 2008 Parent Child Hierarcchy report with ODS Source
Step 1: Create Datasource Connection  and Dataset

Datasource: [AdventureWorksDW]

SELECT DimEmployee.EmployeeKey

,DimEmployee.ParentEmployeeKey

,DimEmployee.FirstName

,ISNULL(SUM(SalesAmountQuota),0) AS SalesAmount

FROM DimEmployee

LEFT JOIN FactSalesQuota ON FactSalesQuota.EmployeeKey = DimEmployee.EmployeeKey

GROUP BY DimEmployee.EmployeeKey

,DimEmployee.ParentEmployeeKey

,FirstName

ORDER BY 4 DESC

Step 2: Configure Recursive Parent

 
1. General Tab  Configure the Prroperties

Name : ParentChildGroup
Then Add Group Expression and Specify the
[Group On] : EmployeeKey

2. Advance Tab
Recursive Parent : ParentEmployeeKey
Document Map : Leave it
 
3: Visiblity Tab
3.a)Check the Hide Check Box
3.b) Enable Display can be Toggle by this Item and
Select EmploeeName (Name of the First column in our report)
  
Step 3: Configure Indent
Right click EmployeeName Test Box and Select "Text Box Property"
 
1. Alignement Tab
 
Padding Option : Left (Expression)
= 20 * level() + 5 & "pt"
 
2. Font Tab
 
Style : Bold (Expression)
=iif(Count(Fields!FirstName.Value , "ParentChildGroup", Recursive) > 1, "Bold", "Normal")

 
Step 4: Set Roll up(Drill Down and Dril Up) for the SalesAmount
 
Set Recursive Sum in the "Sales Amount" Column
 
1. Select Sales Amount column Text Box Property
 
2. General Tab
Value : Expression
 
=sum(Fields!SalesAmount.Value,"ParentChildGroup",Recursive)

Thats it Enjoy SQl 2008 Parent Child Hierarchy Reporting :)