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.