SSRS Multiple Value parameter filter based on dataset

Question

Say I have a dataset in SSRS called DataSet1 that looks like this:

CREATE TABLE #data 
(ID int, Value int, UserID varchar(2))
INSERT INTO #data VALUES
(1, 1000, 'AA'), 
(2, 2000, 'AA'), 
(3, 3000, 'BB'), 
(4, 2000, 'BB'), 
(5, 1500, 'BB'), 
(6, 1800, 'BB'), 
(7, 1700, 'CC')

..and that my report just presents this as a table.

Let's then say I want to add a parameter in that report, that let's the user filter the table by UserID. I want it to be a multiple value parameter where they can choose which users to include in their report. In this case I want the list to be AA, BB and CC.

So far I have done it by creating an extra SQL query based dataset like this:

DataSet1:

SELECT ID, Value, UserID
FROM table

DataSet2:

SELECT DISTINCT UserID
FROM table

And then have the parameter get its available values from DataSet2. However the query I have in my particular report is a very long and complex query, that I would prefer to not use in two datasets. If I have to go back and change something in the query, I would have to maintain that in two places, and so on.

In short: Is there a way to have the available values of my parameter by something like this: SELECT DISTINCT UserID FROM DataSet1

Thanks!

Answer 1

I decided to write my comment on your answer, M.Ali, as an answer to my own question, as I found a way to solve this. Maybe I didn't explain my problem precisely enough. I am aware of how the above thing works, and how to pass parameters based on one dataset, down thru the SQL to create another dataset, multiple values allowed or not. I appreciate your answer though!

The problem I have is that the query that would define my list of values for the parameter, and the query for the actual dataset, are the same. And it's a HUGE query. In other words, where it says 'TABLE' in the code sample, I have several hundreds of lines of code. And my goal was to not have this whole query be defining both datasets. If I in the future have to change the query, I would have to do it in more than one place. Here's how I solved it:

I placed the main query in a shared dataset instead of embedding it into my report. And I then added a row_number function to my query like so:

SELECT ID, Value, UserID, rn = ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY UserID)
FROM *my huge query*

This means that there is only one 'rn = 1' row per UserID. Then I went back to my report. My original DataSet1 would then just point to the shared dataset. The DataSet2 (the parameter one) would also point to the shared dataset with the one difference that I added a filter to that dataset saying 'rn = 1'. I then made a parameter with 'allow multiple values' that took its values from DataSet2. And it works like a charm. This way I can just go to the shared dataset when I need to update the query, and both DataSet1 and DataSet2 will be updated accordingly!

Success :)

Again, thanks for your answer!

Answer 2

You will have to have a separate data set for your parameter query and yes you got that part right, for the given data set:

SELECT DISTINCT UserID
FROM TABLE

This query will be used to populate the drop down list of your parameter. (Multiple values allowed or not).

For you Main query:

if you allow Multiple Selection for @UserID parameter you will write you main Data set query with IN operator as below:

SELECT ID, Value, UserID
FROM table
WHERE (UserID IN (@UserID)) 

If you only want users to be able to select only one value at a time the you can write the above query with a where clause something like WHERE UserID = @UserID.

Make sure you map the variables correctly. But you will have to have a separate query for your drop down list.