SSRS Subreports

·         Subreports allow to add one or more related reports to the Data region.

·         It also allow to Nest or Insert One report inside the other.

·         It will allow to pass parameters from the main report to the subreport

Create Details Report:

Create Hidden parameter @Country which will accept the value passed from the main report.

 Create the dataset with below script

SELECT geo.EnglishCountryRegionName,geo.StateProvinceName, sum(Fact.SalesAmount) as SalesAmount

       FROM FactInternetSales AS Fact

              INNER JOIN DimSalesTerritory AS Sales on fact.SalesTerritoryKey=Sales.SalesTerritoryKey

                                                  Inner join DimGeography as geo on geo.SalesTerritoryKey=Sales.SalesTerritoryKey

                where geo.EnglishCountryRegionName in (@Country)

                group by geo.EnglishCountryRegionName,geo.StateProvinceName

Design the report layout, Insert the table and use the columns like below.

You can see how to create databar report by clicking here

Create Main Report

Create dataset with below script

SELECT geo.EnglishCountryRegionName, sum(Fact.SalesAmount) as SalesAmount

    FROM FactInternetSales AS Fact

            INNER JOIN DimSalesTerritory AS Sales on fact.SalesTerritoryKey=Sales.SalesTerritoryKey

                                                Inner join DimGeography as geo on geo.SalesTerritoryKey=Sales.SalesTerritoryKey

group by geo.EnglishCountryRegionName

Design the report layout, Insert the table.

Click Tablix -> Insert Row -> Inside Group - Below

Then Right Click on Textbox -> Insert -> Subreport

Go to subreport properties -> General tab -> Select Subreport Detail report

next parameters tab -> Select parameter (@Country) and select the value.

Run the Report.