Archive

Archive for the ‘SSRS’ Category

SSRS : Multivalued Parameters

I am creating a simple SSRS report to demonstrate the workarounds available to pass multivalue parameter to Stored Procedures. Stored procedures are capable of receiving scalar values as input for its parameters, and it cannot receive a row set as an input parameter. We don’t have any explicit functionality for passing multivalue parameters to a stored procedure in SSRS, and it has to be achieved only using workarounds

Following is the sample table, through which i have created the sample report in SSRS,

DimProduct

it contains only few fields such as ID, Code, Name and Size. Following is the SSRS report that is created with the sample table

report

this report has been created with one parameter as shown below,

parameters

the parameter displays name [product’s name] and take values as codes [product codes].

selection

The procedure is created as follows

alter Procedure GetProducts
(
@Product nvarchar(50)
)
as
select code, Name, Size
from Dimproduct
where Code in (@Product)

Most of us will first try to pass that mutlivalued parameter to the stored procedure using IN operator and it will not result any data. Why IN operator doesn’t work as expected ?

I further checked the SQL Profiler to see how SSRS gets data using the Stored Procedure, and found it has been executing the procedure as

exec GetProducts @Product=N'C001,D001'

This clearly show that the report is concatenating the two selected products into one, to make it as scalar value. So, a procedure can receive only scalar value and since we don’t have any values in table that matches with the concatenated string, procedure doesn’t return any value

To make it more clear, the concatenated string value [‘C001,D001’] is not same as passing values in IN operator [in (‘C001′,’D001’)]. They are completely different.

This post may not look like new to you all, because you all can search and find many similar posts in many blogs, forum etc. And most of us know this can be resolved using a user defined split function in stored procedure which will split the values from the concatenated string and can provide individual values/row set for further operations.

Please refer, https://rdineshkumar.wordpress.com/2012/09/06/split-comma-separated-values/

I am trying to pass on some of the other techniques that i have learnt to solve this, this particular problem can also be solved without using a user defined function. Let’s discuss those methods

Using Charindex function:

We can make use several other inbuilt functions such as Charindex() to resolve these type of issues, and below is the modified procedure that uses charindex() to check for the matches in the tables

alter Procedure GetProducts
(
@Product nvarchar(50)
)
as
select code, Name, Size
from Dimproduct
where charindex(code,@product)>0

the most important thing required for this method to work is the uniqueness of the code. Instead of sending the product names i have chosen the product codes as they are unique. Most of the cases all codes will be unique as they are the business keys and i have always inclined towards it.

what will happen if pass product names to check with Charindex function ? take a look at our sample table it contains names with characters that gets repeated in others

for ex:

Let’s say, we are selecting ABC as the input parameter.

so when using charindex() function, the input value ABC can be found in ABC and as well as in other product names that starts with ABC [ABCD, ABCDE, ABCDEF]. This could result into erroneous solutions. So always prefer to use this method only when you have a unique fields in the table

Following query would also be useful, if you don’t like creating an additional object [UDF for split functionality]. Try to replicate the operations in Stored procedures that really happens in UDFs

alter Procedure GetProducts
(
@Product nvarchar(50)
)
as
Declare @ProductList table (ProductID nvarchar(max))

while LEN(@Product)>0
begin
if CHARINDEX(',',@Product,1)>0
begin
insert into @ProductList
select SUBSTRING(@Product,1,CHARINDEX(',',@Product,1)-1)
set @Product=SUBSTRING(@Product,CHARINDEX(',',@Product,1)+1,LEN(@Product))
end
else
begin
insert into @ProductList
select @Product
break
end
end

select code, Name, Size
from Dimproduct prd
join @ProductList lst on prd.Code = lst.ProductID

This would ideally produce the same results as expected, i will try to post some other methods if i get any. Please feel free to comment if you have any suggestions and queries. Thank u all.

Happy Reporting 😉

Advertisements
%d bloggers like this: