Sub Report in SSRS

In this article, I am going to demonstrate step by step example of creating Sub Report in SSRS.

Sub Reports are generally used when we need to show detail data with respect to summary data.

Example
Show Detail Data of Sales with respect to its Sales Header Data

• Main Report will display Sales Header Data
• Sub Report will display Sales Detail Data

Prerequisites

• SQL Server 2012 Data Tools with Reporting Services
• AdventureWorks Database

Basic steps to create Sub Report

• Create a Main Report
• Create a Sub report 
• Apply Filter Parameter on Sub Report
• Insert Sub Report into Main Report
• Connect reports using Parameter(s)

Now lets have step by step example

Step 1: Create a Main Report

• Create a connection with SQL Server. We would be using shared data source to fetch the data from the database for the report we created in an earlier article. Create a Shared Data Source

• Right Click Reports>Add New Report, Report Wizard will appear to configure the report. Click Next

• Configure Data Source. Select the Shared Data Source we created in Step 1 and Click Next

• Design Query either thru Query Builder or write your own query and Click Next.

SELECT  SalesOrderID, OrderDate, SOH.CustomerID, P.FirstName+' '+P.LastName as CustomerName
        , SOH.SubTotal, SOH.TaxAmt, SOH.Freight,  SOH.TotalDue
FROM    Sales.SalesOrderHeader SOH
inner join Sales.Customer C on C.CustomerID = SOH.CustomerID
inner join Person.Person P on P.BusinessEntityID = C.PersonID
WHERE    SOH.OrderDate = @OrderDate

• Select Tabular as Report Type and click Next.

• You can configure to display the data based on grouping but as of now just click Finish.

• Here, you can select Table Style. Default Style is Slate. Click Next

• At this stage, we are done with configuration of Main Report. Change the Report Name to DailySalesReport and Click Finish.

• Query Parameter OrderDate that we used in the Query in Step 4. will be created automatically. Change the Data Type to Date/Time

• To manually add Query Parameter, refer Query Parameter in SSRS

Step 2: Create a Sub Report

• Create another report that would be used as Sub Report using the same step we used to create Main Report using below Query. Name the Report to SalesDeails.

SELECT  SOD.SalesOrderID, SalesOrderDetailID, P.Name
        , P.ProductNumber, SOD.UnitPrice, SOD.OrderQty
FROM    Sales.SalesOrderHeader SOH
inner join Sales.SalesOrderDetail SOD on SOH.SalesOrderID = SOD.SalesOrderID
inner join Production.Product P on SOD.ProductID = P.ProductID

Step 3: Add Filter Parameter to Sub Report

• Add Parameter SalesOrderId to Sub Report.
• Set Data type as Integer and Parameter visibility to Hidden as this would be automatically passed from Main Report to Sub Report.

• Right Click on Dataset>Datset Properties under Report Data

• In Dataset Properties Windows select Filters Tab and Add Filter SalesOrderDetailId

Step 4: Insert Sub Report into Main Report

• Add a group to the Data Section

• Merge all the cells

• Right Click on the Group and select Insert>Subreport

• Right Click SubReport Section > Sub Report Properties. In the pop window change the Name of Sub Report to SalesDetails and select DailySalesReport as below.

Step 5: Connect reports using Parameter(s)

• Select Parameters Tab and Add Parameter as below

We are done with creating the Sub Report. Now lets run the report and see the output.