Wednesday, October 30, 2019

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.


Tuesday, October 29, 2019

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.