Display Row and Column Headers on Every Pages For Row Groups in SSRS

Below are steps to show Row headers in every page of a report when using a Row groups.

Generally when there is a static report if we make RepeatOnNewPage property to True. Which will solve the purpose.

But when Row groups or column groups are there we need o set few more property  to make header visible on each page.

Click on the Tablix properties and  Click on Repeat header rows in each page and Repeat Header Columns on each page to true.

Click on Advanced tab on the Row Group pane.

Go to Row Group-> Static-> Properties-> RepeatonNewPage->True

Go to Row Group-> Static-> Properties-> KeepwithGroup->After

Now Run the Report. You can view the header in every page.

For details video visit here:

How to Create Drillthrough report in SSRS

A drillthrough report is a report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. The data in the drillthrough report is not retrieved until user click on the link in the main report.

Step by Step process to create Drill through report.

Two reports are required to create, Summery and Details report

Step 1:

First Create the Details Report.

 I have used the example from AdventureWorksDW2017 database.

Create the Parameter: Product Name . This will accept the value passed from the summery report. Make the visibility property to Hidden.

Create the data set which contains details of Product, Sales Order, Sales amount and sales country details.

SELECT Prod.EnglishProductName AS ProductName,Fact.SalesOrderNumber, Fact.SalesAmount,

              Fact.TotalProductCost TotalProductCost,Fact.TaxAmt,Sales.SalesTerritoryCountry

       FROM FactInternetSales AS Fact

              INNER JOIN DimProduct AS Prod ON Prod.ProductKey = Fact.ProductKey

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

              Where Prod.EnglishProductName in (@ProductName)

Insert table into report and format it as below. I have created a row group on product name.

 Step 2:

Create the Product Summery report.

Create the data set which contains the product and sum of sales amount and product cost.

SELECT Prod.EnglishProductName AS ProductName,sum(Fact.SalesAmount) As SalesAmount,

              sum(Fact.TotalProductCost) TotalProductCost,sum(Fact.TaxAmt) TaxAmt

       FROM DimProduct AS Prod

              INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey

              group by Prod.EnglishProductName

Insert table into the report and format it as below.

Go the text box properties of Product Name highlighted above

Go to Action->Go to Report->Select Details report

Finally configure the parameter as mentioned below.

Name : Parameter name of the details report

Value : Product name from summery report.

You can change the product Name text box to hyperlink format so that easily noticed during run time.

Run the Summery report::

Click on an of the product name which will Go to the details report.

Find Video below:

SSRS : How to pass multi-value Parameter to a Stored Procedure

We will see How to pass multi-value Parameter to a Stored Procedure in report with example.

I have used the example from AdventureWorksDW2017 database.

 Steps For Reports:

Add new report and setup the connection string to AdventureWorksDW2017

Create Report parameter @Color, Check Allow multiple value like below screen shot.

 Now Add new data set to select the All the Colors and map to the Report parameter list so that it can be fetched at run time.

 Modify the Report Parameter Available Value property.

Add new report and setup the connection string to AdventureWorksDW2017

Add new dataset and select the query type as Stored Procure.

Select the store procedure name from drop down list. Here we need to select

Sp_Product.

Navigate to Parameters tab and make sure that Report parameter is selected

Since it is a multi value parameter, in Report parameter tab Click on fx (Expression button) and modify the expression value as below.

 =join(Parameters!Color.Value,",")

After change the expression click OK.

The above parameter value will be passed as comma separated value like (a,b,c)

which can't be used using SQL IN operator. So need to needs to split the comma separated values.

We can do this by using comma separated function.

Either we can write UDFs or can use system function string_split()

Create Stored Procedure. We need to fetch the value based on Product Colors. So Color will be used as Stored procedure parameter.

IF OBJECT_ID ( 'SP_Product', 'P' ) IS NOT NULL 

    DROP PROCEDURE SP_Product;

GO

Create Procedure dbo.SP_Product

@Color as varchar(255)

As

Begin

SELECT Prod.Color,

       Prod.EnglishProductName AS ProductName,

       Fact.SalesAmount,

          Fact.OrderQuantity,

       Fact.TotalProductCost,

       Fact.TaxAmt

      FROM DimProduct AS Prod

         INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey

       Where prod.Color in (Select value from string_split(@Color,',') where RTRIM(value)<>'')

End

Go

Add a table and map all the fields with table fields. Do the formatting well.

Run the report, Select Parameter value from drop down list, then View report.

SSRS : How to pass report parameters to stored procedure in report

We will see How to pass report parameters to stored procedure in report with example.

I have used the example from AdventureWorksDW2017 database.

Create Stored Procedure. We need to fetch the value based on Product Colors. So Color will be used as Stored procedure parameter.

Create Procedure dbo.SP_Product

@Color as varchar(255)

As

Begin

SELECT Prod.Color,

       Prod.EnglishProductName AS ProductName,

       Fact.SalesAmount,

          Fact.OrderQuantity,

       Fact.TotalProductCost,

       Fact.TaxAmt

      FROM DimProduct AS Prod

         INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey

      Where prod.Color in (@Color)

End

Go

Execute the Stored Procedure:

Execute dbo.SP_Product 'Red'

Steps For Reports:

Add new report and setup the connection string to AdventureWorksDW2017

Create Report parameter @Color Make it as a single value parameter.

Now Add new data set to select the All the Colors and map to the Report parameter list so that it can be fetched at run time.

Modify the Report Parameter Available Value property.

Add new report and setup the connection string to AdventureWorksDW2017

Add new dataset and select the query type as Stored Procure.

Select the store procedure name from drop down list. Here we need to select Sp_Product.

Navigate to Parameters tab and make sure that Report parameter is selected

Add a table and map all the fields with table fields. Do the formatting well.

Run the report, Select Parameter value from drop down list, then View report.

Create SSRS Report using Stored Procedure

We will see how to create SSRS reports using Stored Procedure with example.

I have used the example from AdventureWorksDW2017 database.

Create Stored Procedure.

use AdventureWorksDW2017;

Go

IF OBJECT_ID ( 'SP_Product', 'P' ) IS NOT NULL  

    DROP PROCEDURE SP_Product; 

GO

Create Procedure dbo.SP_Product

As

Begin

SELECT Prod.Color,

       Prod.EnglishProductName AS ProductName,

       Fact.SalesAmount,

          Fact.OrderQuantity,

       Fact.TotalProductCost,

       Fact.TaxAmt

      FROM DimProduct AS Prod

         INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey

End

Go

Execute the Stored Procedure:

Execute dbo.SP_Product

Create SSRS Report using Stored procedure.

Add new report and setup the connection string to AdventureWorksDW2017

Add new dataset and select the query type as Stored Procure.

Select the store procedure name from drop down list. Here we need to select Sp_Product.

Navigate to Fields tab. You can see all the fields from Stored procedure are populating correctly.

Add a table and map all the fields with table fields. Do the formatting well.

Run the report, You can see the  output with desired columns.

Watch the video here: