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


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
Chapter-4 Creating SSRS Matrix Report / Cross Tab Report
Chapter-5 Creating a Sub Report in SSRS
Chapter-6: Creating a Drill Down Report in SSRS
Chapter-7: Working with expressions and custom code in SSRS
Chapter-8-SSRS (Reporting Services) Working with Calculated fields
Chapter-9-Sorting of Column and Custom Paging in SSRS
Chapter-10 : Creating and Deploying Web Services for SSRS
Chapter-11 : Creating SSRS Report by consuming Web Service Data Source
Chapter-12 : Working with Data Bars in SSRS
Chapter-13-Displaying Color in Data Bar Based on Condition
Chapter-14- Displaying Data Labels along with Data Bars in SSRS
Chapter-15-Displaying Indicators in SSRS
Chapter-16-Creating SSRS Linked Report
Chapter-17 Adding or Creating Drop Down Parameter List in SSRS
Chapter-18 Creating Group above or stepped report in SSRS
Chapter-19-Creating Multi Value Parameter Report in SSRS
Chapter-20-Using CSS Stylesheet in SSRS

Leave a Reply

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


five × three =