SSRS: Create a Sub Reports and Call it in the Main Report

This demo will explain how to create a simple sub report which will be displayed in the main report. In order to make this works both reports, the main and sub report must be deployed to services.

Data source : random sample data

Data set for the Sub report:

SELECT Year([OrderDate]) SaleDate

,[Region]

,[Representant]

,[Category]

,[Total]

FROM [Test].[dbo].[salesCat]

WHERE [Representant] = @RepParam

Dataset for @RegionParam

SELECT distinct Representant FROM salesCat

Dataset for the Main report

SELECT distinct Representant FROM salesCat

Step 1

Create a sub report with a parameter. This parameter will reference a field Representant in the main report

Step 2

Create a query to populate the parameter values.

Step 3

Set the parameter @SELLER to get the values from a query.

Step 4

Build a simple report.

Step 5

Preview the report

Step 6

Prepare the main report, which will execute this sub report.

Dataset for the main report

Step 7

1. Insert a table

2. add the field Representant to that table

3. Right click on the empty cell in details, insert a subreport

Step 8

Sub Report properties

Specify which report should be used as a sub report

Step 9

Sub Report Parameter

Name: @SELLER, as a name specify the parameter from the Sub Report

Value: Representant , this is the column from MainReport dataset, which will reference the parameter from the sub report.

Step 10

Report design preview

Put all report elements side by side so that the resulting report is compact.

Step 11

Report preview