Multi valued Parameters using Stored Procedures in SSRS Reports

17806

Friends,

Here in this post we are gonna discuss about how to pass multiple values to SP(Stored Procedures) to get data for SSRS Reports. If you wish to define parameters using Sql Queries then you can find the sample here. For this demonstration purpose I Created the following Stored procedure –

Create procedure sp_getColors
as
select Distinct Color as “Colors” from dimproduct

Now to demonstrate the same, I create a tabular report using the following SP –

CREATE PROCEDURE [SP_SALES]
AS
BEGIN
SELECT
DimProduct.Color
,DimProduct.Class
,DimProduct.EnglishProductName
,FactInternetSales.SalesAmount
,FactInternetSales.TaxAmt
,FactInternetSales.OrderQuantity
,FactInternetSales.UnitPrice
FROM DimProduct
INNER JOIN FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey
END

Create a Dataset using the above given SP as shown below.

Now create a tabular report using the Dataset created as shown below.

At this point of time there are no Parameters defined and hence you will get all the data from the database and will be displayed in the report. PFB the steps to be followed to ADD PARAMETER to your report using SPS as source –

  • Right click on Datasource and select “Add Dataset” option.
  • Select the data source and select the option “Stored Procedure” radio button and provide the Stored Procedure name which pulls values for parameter and as I am adding “Color” as parameter I am using the following stored procedure which pulls Colors data.

  • As there is no INPUT parameters for this Stored Procedure we can continue by Clicking OK. When you click on OK , you can see new data set added with ONE columns as shown in below screenshot.
  • Now Right Click on “Parameters” and select “Add Parameters” option. The wizard will get open and Change the default value given for NAME and PROMPT if required.

  • Now select the “Available Values” tab which is on the Left hand side of the wizard and select the option “Get Values From a Query”. Provide the Dataset name,Value Field and Label Field as shown below –
  • Click on OK.
  • Now the report parameter is ready and the same can be seen in the below given screenshot.

  • If you see the above screenshot the  Parameters looks like working fine but if you see the chart area the data doesn’t look correct. This is because we added parameter but not linked this parameter selection to the Main Dataset from which we are getting data and filling the chart. Now I will modify the Main Dataset in order to fetch data by applying parameter values. Edit the main Dataset and update the query as shown below –
First Execute the below give function –
CREATE FUNCTION [dbo].[FnSplit]
(@List nvarchar(2000),@SplitOn nvarchar(5))
RETURNS @RtnValue table
(Id int identity(1,1),Value nvarchar(100))
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
and then execute this SP.

ALTER PROCEDURE [SP_SALES]
(@Color VARCHAR(4000))
AS
BEGIN
SELECT
DimProduct.Color
,DimProduct.Class
,DimProduct.EnglishProductName
,FactInternetSales.SalesAmount
,FactInternetSales.TaxAmt
,FactInternetSales.OrderQuantity
,FactInternetSales.UnitPrice
FROM DimProduct
INNER JOIN FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey
Where Color in (select Value from dbo.FnSplit(@Color,’,’))
END

Now Select Parameters tab and map the parameters for the main Dataset as shown below.

  • Click on OK and your report is ready. Go to preview tab and test you report.

That’s it .. Your Report with multi valued Parameters passed to  dataset which is created using SP is ready .. Hope you understood the concepts .. Happy Coding !!

Regards,
Roopesh Babu V