Linked Reports in SSRS

·         Linked report is a report server item that provides an access to existing report, we can say it is customized short cut to existing report.

·         It derived from existing report and retains original report definition like report layout, data set and data source.

·         security, parameters, location, subscriptions, and schedules can be different from original report.

Example:

I have already created a report and deployed in report server.

Below is the steps to create linked report.

Right click on the report -> Manage -> Create Linked report

Enter Name, Description and Location (if required to change)

Under Advanced tab select appropriate option and then Apply.

You can mark the difference from the below image.

We want to make the change in linked report and wanted to display only red color data by changing in the parameter properties.

Right Click on Linked report -> Manage -> Parameter -> Select red -> Apply

Now run the report.

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)

Create Data bar chart report in SSRS

Follow the below steps to Create Data bar chart report in SSRS

Use the below scripts to create dataset.

SELECT 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

          group by geo.StateProvinceName

·         Right Click table column -> Insert -> Data bar

·         Click on data bar -> Select Values -> Sales Amount

·         Right-click the chart and choose 'Show Data Levels'

·         Save Report and Run the report.