CODEWITHSUNDEEP
sqlsql-serverssmsssrs-2012
Darryl Lobo
Darryl Lobo

Reputation: 165

query works in ssms but not in ssrs

So the problem I am having has got me completely perplexed. I have a dataset that uses a query with 4 parameters. There are the regular choices the users can choose for the different parameters, except for one choice which is ' ALL' which if the user chooses will choose everything.

I run the SSRS report and for some reason when on only one of the parameters the user chooses ' ALL' it returns no records, just a blank table. I try to recreate the same query behind the dataset on SSMS and input the ' ALL' option in that same problem parameter and it returns all the records I expected to see.

Query:

Declare @workingParam1 as varchar(200)
Declare @workingParam2 as varchar(50)
Declare @workingParam3 as varchar(50)
Declare @problemParam as varchar(50) -- this one is giving me problems only 
                                     -- if I choose ' ALL'


SELECT        Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9
FROM            View
WHERE        
(Col1 = @problemParam OR @problemParam = ' ALL') And 
(Col2 = @workingParam1 OR @workingParam1  = ' ALL') And
(Col3 = @workingParam2 OR @workingParam2 = ' ALL') And 
(Col4 = @workingParam3 OR @workingParam3 = ' ALL')
ORDER BY Col1, Col2, Col3

Thanks!

Upvotes: 3

Views: 1418

Answers (1)

M.Ali
M.Ali

Reputation: 69514

I believe you have a multi-select parameter in your SSRS report. When you select ALL from the parameter drop down list in ssrs report, it does not pass your t-sql query a value of literal string All but it passes all the values to your t-sql in a comma delimited list, '1,2,3,4'.

Now you have to make changes to your T-SQL code, so it can handle parameters passed as a comma delimited list.

There are many question on Stack Overflow on that matter.

Alternatively you can create your on Select All value to show in your parameter drop down, only then you can use the logic you have used in your current query, but even then I would use something like... 

WHERE (ColumnName = @Param OR @Param = 'All')

This will get your desired results but again if you want better performance from your t-sql code, use a stored procedure for your data set and use dynamic sql to filter out the Where clause altogether if the user passes All parameter.

A lot of information in this short answer and a lot of googling for you to do :)

Upvotes: 3

Related Questions