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.

The maximum recursion 100 has been exhausted before statement completion

When running the report getting below error while report is trying to execute the SQL script

An error has occurred during report processing. (rsProcessingAborted)

Cannot read the next data row for the dataset .... (rsErrorReadingNextDataRow)

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

max recursion error is also a SQL error. Add below code at the end of the script which will resolve the issue.

Select 

-----------------

From Employee

where

--------------

option (maxrecursion 0)