SubReport in SSRS

SubReport in SSRS

So far, we have placed report items within report items and data regions within data regions. Now in this article, we will put whole report inside another report. This is done using the sub report item. The sub report item is simply a placeholder in a report. Any report can be used as a sub report.

1. First of all open Visual Studio 2010 and open your SSRS project. Then add report named as DemoSubReport in your project.Now create Dataset. In dataset we used following query.

SELECT	DEPARTMENTID ,NAME      
FROM	HUMANRESOURCES.DEPARTMENT
WHERE	DEPARTMENTID = @DepartmentID

@DepartmentID is our parameter.

2. Then right click on report area and goto Insert–>Table.

2-SubReport in SSRS

3. Now select data fields as a column in the table. After that your Report Design will look like below.

3-SubReport in SSRS

4. Similarly add another report named as SubReport. In that report we used following query:

SELECT	    E.BUSINESSENTITYID,FIRSTNAME + ' ' + LASTNAME AS NAME
FROM		HUMANRESOURCES.EMPLOYEE E
INNER JOIN	PERSON.PERSON P ON E.BUSINESSENTITYID = P.BUSINESSENTITYID
INNER JOIN	HUMANRESOURCES.EMPLOYEEDEPARTMENTHISTORY EDH ON 		E.BUSINESSENTITYID = EDH.BUSINESSENTITYID
WHERE	    EDH.DEPARTMENTID = @DepartnentID
ORDER BY	FIRSTNAME

@DepartmentID is our parameter.

also add table as described in Step 2 & 3.

5. Now again goto our first report DemoSubReport. Then right click on report area and goto Insert –> SubReport.

5-SubReport in SSRS

6. Now your report design look like below:

6-SubReport in SSRS

7. Now right click on Sub Report Item and select Subreport Properties.

7-SubReport in SSRS

8. A Subreport Properties window pops up. In the Use this report as a subreport select SubReport which we generated earlier.

8-SubReport in SSRS

9. Then click on Parameters in left pane. Then Click on Add. A new row is added to the parameter grid.

In the Name text box, type the name of a parameter in the subreport. This name must match a report parameter in the subreport. If the subreport is in the same project, you can choose the parameter from the drop-down list.

9-SubReport in SSRS

Then click on OK button.

10. So we have done with Sub Report. To see the preview click on Preview tab. Give the value of DepartmentID and click on View Report Button.

10-SubReport in SSRS

Congratulations! We successfully completed SubReport in SSRS.

12 thoughts on “SubReport in SSRS

  1. Nice But How do i link a subreport thats in another (sub) project (because it does not display in the list of reports i can select in the subreport properties)

Leave a Reply