Subreports

A subreport is a report that is embedded into another report. Subreports can take parameters and execute their own datasets. A subreport in SSRS is actually just another. In fact, in SSRS you can execute a subreport on its own.

To add a subreport to a report, you simply drag a subreport control onto the report and tell it which report to display. If the subreport requires parameters, you have to tell the main report which value to pass to the subreport.

Let's add a new report to the project and call it MainReport.rdl. Create a new dataset using the shared datasource and the query:

SELECT EmployeeID, FirstName, LastName

FROM tblEmployee

Switch to the Layout tab. Drag a table on the report detail area. Set up the first column to display the Employee's first name (by dragging that column from the Datasets tab into the Detail row) and set up the second column to display the Employee's last name. Preview the report.

Create another report, and call this one SubReport.rdl. This time, create a dataset that uses the shared data source, and use the following query text:

SELECT Address1, Address2, City, [State], ZIP

FROM tblEmployee

WHERE EmployeeID = @EmployeeID

In the Layout tab, use text boxes to create the address layout, as shown in figure. You can simply drag the text boxes onto the screen by clicking on the field in datasets tab and dragging it onto design surface. You will also note that when you do this, the expression used to set the value property for the textbox uses the First() function. This function will use the value from the first row returned by the dataset. All other rows returned are ignored.



Now preview the report and use '1' for the EmployeeID parameter.

Let's jump back to the MainReport.rdl. To embed the subreport, drag a SubReport control into the detail cell for the column you labeled 'Address'. Right-click on the SubReport control and select Properties. In the Properties dialog choose SubReport from the subreport dropdown.



Next, switch to the Parameters tab. This is where you connect your subreport to the main report. You do this by indicating which value from the main report is to be passed to the subreport to fulfill its parameter requirements.

In the Parameter Name column choose EmployeeID and in the Parameter Value column choose =Fields!EmployeeID.Value. This will wire up the subreport to whichever Employee is being displayed in the row of the table.



Click OK to close the dialog, and then preview the main report.