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