Pre-requisites for this article:
- SQL Server 2008 along with SSRS installed
- Business Intelligence Development Studio (BIDS)
- AdventureWorksDW2008R2 database. Click here to install this database from codeplex.
- Familier with SSRS
STEP 1: To begin, create new report in your existing solution. Add new Shared Data Source. If you don't know how to create a shared data source, click Creating Shared Data Source in SSRS 2008.
,[FirstName] + ' ' + ISNULL([MiddleName],'') + ' ' + [LastName] EmployeeName],[Title]
STEP 3: Drag and drop Table control and map with dsMain dataset. Select EmployeeName in first column, Title in second column and =Level() expression in the third column. Set following properties for table control:
- Header Background color = DarkBlue
- Header Color = White
- Font = Verdana, 8pt
STEP 4: Set Group Properties to reference Recursive Parent. Select table control --> click View and check Grouping as shown below:
Open Row Group properties, click on add button and select [EmployeeKey] in Group on box.
Click on Advance tab of Group Properties window and select [ParentEmployeeKey] in Recursive parent box. Click OK and save changes.
1) Enter below expression in FontWeight under Font Property:
=IIF(Count(Fields!EmployeeName.Value, "Details", Recursive) > 1, "Bold", "Normal")
2) Enter below expression in LeftIndent under Indent Property:
=CStr(2 + (Level()*20)) + "pt"
Again Preview the report, report will look like below image: