CODEWITHSUNDEEP
t-sqlreporting-servicesssrs-2008
NeilMCSE
NeilMCSE

Reputation: 13

Report Builder 3.0 Multivalue parameter issue

Have an issue with creating multivalue parameter in RB3 with SSRS 2008R2. I want to return all the records and then use the parameters to filter down. I have done this through setting the parameter value set as follows:

SELECT table.col1 ,table.col2 FROM table UNION Select 'Select all', NULL

and then in the main dataset using Field1 = @prmParameter OR @prmParameter IS NULL after the WHERE clause. After selecting the "Allow NULLS" in the parameter, this will return all the rows and then have the parameter drop down to select other table.col1 items and 'Select all'.

A change that is required is to have a Multivalue parameter, but of course the Allow NULLS and Allow Multivalue are incompatible. I have seen a few workarounds, but when I attempt these, I get an error that something is up with my syntax.

What am I missing?

Upvotes: 1

Views: 127

Answers (1)

Bactos
Bactos

Reputation: 1243

If I could see your query and the error I might have a little more to test with. However, to use a multi value param you have to do something like:

Make sure your report Parameter is selected to allow blank values and allow multiple values (the check boxes in the Parameter).

QUERY

Select 
 table.col1 ,table.col2 
FROM table
Where ISNULL(table.col1,") IN (@prmParameter)

There is a good article Here

Hope that helps!

Upvotes: 1

Related Questions