Building Subreport in Reporting Services

Today I am going to talk about(or rather write about) building Subrpeorts in SQL Server Reporting Services (SSRS).
First, let's create two simple reports -
1. EmployeeSummary report - Parent report.
2. EmployeeDetails report - Subreport.
NOTE: If you are well familiar with creating reports you can jump right to the Building Subreport section below.
Start Business Intelligence Development Studio, go to file menu, select new and then select Project. The New Projectdialog box appears, select the Report Server Project template and type a relevant name as shown below.

The first step in creating a report is to add a Data Source to connect to the database. Right-click Shared Data Sourcesand select Add New Data Source. Give a relevant name to the Data Source and click the edit button to define a new Connection as shown below. Click OK twice to create the new data source.


Right-click the Reports folder and go to Add menu and select New Item...

The Add New Item dialog box appears, select the Report template and type a proper name(in this case it is EmployeeSummary) for the report as shown below and click ok. This will be a Summary report that shows all the Employees and the number of hours they worked for a given month.

Now click on the Dataset drop down box and select New Dataset as shown below.

The Dataset dialog box appear, type a relevant name for the dataset and type a query to cater the report as shown below.

Now click on the Layout tab, drag Table from the Toolbox and drop it on to the body of the report as shown below.

GO to View menu and click Datasets to get the Datasets window if its not open already. Now drag and drop the required fields from the Dataset into the layout as shown below. Please be sure to drop the fields in the detail section. Also, do some basic formatting such as adjusting the column width, header color etc to make the report look pretty :)

Now click the Preview tab to look at the new report we just created. This will server as our main report or summary report. Now we will create a subreport which will server as a detail report. Please note that this is just sample data created for demo purpose.

Now let's create another report which will be our subreport. Right-click the Reports folder and go to Add menu and select New Item...
The Add New Item dialog box appears, select the Report template and type a proper name(in this case it is EmployeeDetails) for the report. This will be a detail report that shows all the Employees, the Practice Area they belong to, whether permanent employee or a contractor and utilization in percentage. Your screen should look similar to the one shown below.

Similar to how we did for the first report, we will need to create a new Dataset, type the query for the report data as shown below.

Again, as we did for the first report, click on the Layout tab, drag Table from the Toolbox and drop it on to the body of the report, drag and drop the required fields from the Dataset into the layout as shown below.

Click the prview tab to view the data as shown below.

Now let's create a parameter in the subreport on EmployeeNumber. In most of the cases, you will likely have a parameter on subreport and you will pass the parameter from the parent report to the subrpeort. To be more specific, when you place the subreport in the main report, you can select the report parameter and a value to pass from the parent report to the report parameter in the subreport.
Go back to the layout tab, go to Report menu and select Report Parameters as shown below.

The Report Parameters dialog box appears. Click Add and create a parameter as shown below.

Click on the Data tab and add a WHERE clause to the query as shown below. Please note the parameter we just created in the WHERE clause. Now let's get to the meet and potatoes of this post -

Building Subreport
In the main report(parent report), add a column where you want your subreport to be displayed. I will add it right after the EmployeeNumebr Column in this example.

From the Toolbox, drag Subreport and drop it onto the detail section of the new column we just created. Please see the screen shot below.

Righ-click on the cell where you dropped the Subreport, in the Subreport dropdown box, select EmployeeDetails as the subreport as shown below.

Now go to the Visibility tab and select the options as shown below. This will ensure that the subrpeort can be toggled using the EmployeeNumber column.

Go to Parameter tab and set the options below to pass the parameter from the main report to the subreport.

Save the report and click Preview to look at the final report as shown below.