Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

asked on

Filter SSRS report based on multi-select input parameter

I've got a SSRS report which currently filters a dataset on "[ProgramID] = @ProgramID" and the report parameter is set to only allow a single selection.  This is currently done in the dataset which looks something like:

SELECT * FROM myTable WHERE ProgramID = @ProgramID

My client wants to change this to allow the user to select multiple Programs from the Program dropdown.

How do I modify the SQL query in my data set to accommodate this multi-select parameter?  I've tried a number of things and cannot seem to get it to work by modifying the SQL or by removing the criteria from the query and adding a filter to the tablix which uses the dataset.  I've searched and tried a number of "solutions" from here in EE and other web-sites and just cannot get this right.

Thanks
Avatar of OAC Technology
OAC Technology
Flag of United States of America image

If possible, use OR and AND statements. Here is an example.

SELECT * FROM myTable
WHERE ProgramID = @ProgramID OR ProgramID = @ProgramID2
Avatar of Dale Fye

ASKER

There are as many as 15 Program options that the user could select from the multi-select dropdown, don't think that is really an option.  

I'll give this a try, but there has to be a better way.

Dale
Hi Dale,

What about using in?

SELECT * FROM myTable WHERE ProgramID in (
-- this bit is psuedo code ...
select ProgramID
from dropdown
where
selected
)

HTH
  David
SSRS automatically "adjusts" this code to make the IN work properly, so you write the SSRS SQL query like this:

...
WHERE ProgramID IN (@ProgramID)

Yes, I know in standard SQL that doesn't work that way, but in SSRS it should work fine, even for multiple selections in @ProgramID.
Scott, you are talking about doing this in the actual SQL that is the dataset for the report, right?

I could swear I tried this, and it didn't work, but will give it another try.

Dale
Scott, when I make that change and attempt to run the query in the SSRS Query Designer, I get an error :

Incorrect syntax near the keyword 'IN', (Microsoft SQL Server, Error: 156).

And then I get the error:

Count not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct, and when that closes, the dataset has no fields displayed in the SSRS report designer.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I should mention that I'm using Visual Studio 2010.
Unfortunately, that's the only way I've been able to do it properly.