Chapter-5: Creating a Sub-Report in SSRS

A sub report is a report within another report i.e there will be two reports one the Master and the other the child where the master will invoke the child report based on certain parameters. The child report or sub report can accept parameters from the master report and will execute its work.

Download Full Tutorial for Chapter-5-Creating-a-SubReport-in-SSRS in PDF format

Download Source Code for Creating SSRS Sub Report

Step 1: The Master report will execute the query from customer master table as given below

SELECT [CustomerID],[ContactName],[Address],[Country]  FROM [Northwind].[dbo].[Customers]

And for the Subreport we will execute the below query form orders table

SELECT [OrderID],[CustomerID],[OrderDate],[ShippedDate],[ShipName],[ShipCountry] FROM [Northwind].[dbo].[Orders] where ShipCountry=@country

We are passing the @Country as parameter. So at runtime based on the parameter value passed, the sub report will be generated. The report will have two parts.



//

Step 2: Create a Master Report as it was already explained in Chapter-1 of SSRS Reporting

Preview of Master Report
Preview of Master Report

Step 3: Create a Order SubReport as it was already explained in Chapter-1 of SSRS Reporting with parameter Country as it was given in query.

Sub Report
Sub Report

Step 4: Add a SubReport control in the master report as shown in the image.

Customer Master Report
Customer Master Report

Step 5: Right Click on the Subreport -> Subreport Properties

image007

Step 6: From the General section of Subreport Properties window, select the subreport name as “OrderSubReport”.

Select the sub report name
Select the sub report name

We have to add Parameters from Parameter tab. After clicking on the Add button, enter the parameter as CustomerID.

Step-7: Let us enter the Parameter name as “CountryName” and the value as ” =First(Fields!ShipCountry.Value, “DataSet1″)”. click on OK button

Enter the Parameter name
Enter the Parameter name



//

Step 8: A Customer Master Report with Order sub report is generated successfully.

Preview of Master and Sub Report
Preview of Master and Sub Report

Previous Related Post:

Chapter-1: Creating a basic Tabular SSRS Report using Report Wizard
Chapter-2 : Report Deployment on SQL Server 2012 Reporting Server
Chapter-3:Opening/Calling the deployed SQL Server Reporting Server report from Asp.net
Chapter-4 Creating SSRS Matrix Report / Cross Tab Report



//

Leave a Reply

Your email address will not be published. Required fields are marked *

*

2 × 5 =