Friday, September 20, 2019

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.




2 comments:

  1. This was very useful! If you are converting inline queries to proc executions with parameters this is a good and reliable way to pass values. Make sure the proc parameter is sized correctly to compensate for the padding.

    ReplyDelete
  2. I have searched a lot before and I haven't found this solution. Thanks a lot for solving this problem !!

    ReplyDelete