How to create master detail report using nested data regions?
This article describes the creation of Master Detail report using nested data region. In order to create Master Detail report using nested data region, you must join the master and detail rows into a single row. The following steps guides you to create the stepped report with parent and child groups that represents (1- N relationship).
- Create the embedded datasource and dataset for the report. Please find the below sample dataset query
Note:
AdventureWorks database is used to design the report.
SELECT HumanResources.EmployeeDepartmentHistory.EmployeeID ,HumanResources.EmployeeDepartmentHistory.DepartmentID AS [EmployeeDepartmentHistory DepartmentID] ,HumanResources.EmployeeDepartmentHistory.ShiftID ,HumanResources.EmployeeDepartmentHistory.StartDate ,HumanResources.EmployeeDepartmentHistory.EndDate ,HumanResources.EmployeeDepartmentHistory.ModifiedDate AS [EmployeeDepartmentHistory ModifiedDate] ,HumanResources.Department.DepartmentID AS [Department DepartmentID] ,HumanResources.Department.Name ,HumanResources.Department.GroupName ,HumanResources.Department.ModifiedDate AS [Department ModifiedDate] FROM HumanResources.Department INNER JOIN HumanResources.EmployeeDepartmentHistory ON HumanResources.Department.DepartmentID = HumanResources.EmployeeDepartmentHistory.DepartmentID
- Now, drag and drop the List report item to the design area.
- Select the list report item and choose the Group Properties from Grouping Panel to add the group expression.
- Select the DepartmentID field as parent group to group the data based on Department ID in the Tablix Group Wizard.
- To create stepped report, add the tablix data region item with default header and detail rows as a child to List report item. Drag and drop the required dataset field values to the tablix cell data.
- Preview the Employee master detail report, output with grouped data is rendered like below.
Sample Report
