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.
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
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.
Step 4: Add a SubReport control in the master report as shown in the image.
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”.
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
Step 8: A Customer Master Report with Order sub report is generated successfully.
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
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