[Last Call] Learn how to a build a cloud-first strategyRegister Now


SSRS 2008 Multi-Value Parameters

Posted on 2011-05-09
Medium Priority
Last Modified: 2013-11-05
I am trying to create a SSRS 2008 report with multi-value parameters.  
• One multi-value parameter is “PROJECT” (drop down list sourced from a query).  This parameter is for the user to view people who have participated in one or more of the selected projects (i.e. project A, B, and C).
• Another multi-value parameter is “PROJECT_NOT” (drop down list sourced from the same query).   This parameter is for the user to view people who have participated in the above stated project(s) (i.e.  A, B, and C), but have not participated in one or more of the selected “PROJECT_NOT” project(s) (i.e. D or F).  

                                 FROM GPDDETAIL GPD001
                                 WHERE GPD001.PERSON_ID = PPL.PERSON_ID
                                 AND GPD001.PROJECT_ID IN (@PROJECT_NOT)))

My question is how to make this one report work both ways:
• The user wants to view people who have participated in project A, B, and C, but have not participated in project D or F.
• The user only wants to view people who have participated in project A, B, and C, but is not concerned with any projects they have not participated in.
Question by:nulad
LVL 27

Expert Comment

ID: 35721581
I think you are going to have to work the Stored Procedure side on this.
Example: like if the parameters for the second parmeter are null  coming back from the report then, just give me the info on the first pamameter to show the the dataset for the report.
LVL 22

Accepted Solution

Nico Bontenbal earned 2000 total points
ID: 35722673
You could use a stored procedure with ifs, or use the T/SQL statements in your query directly. But you could also use the technique demonstrated in the attached report. Rename the .xml to to .rdl (EE doesn't allow rdl). Load the report and change the data source.

The first thing is you can't use null values in multi value parameters. Therefor I add a 'project' with ID 0 and name '<indifferent>' to the Projects dataset. I also set 0 to be the default. Now both lists with project have the indifferent option, and it is selected by default.

In the where statement for the main dataset (Person) I use this where clause:
    (personid in (select PersonID from @GPDDetail where ProjectID in (@Project)) or 0 in (@Project))
    and (PersonID not in (select PersonID from @GPDDetail where ProjectID in (@Projects_Not)) or 0 in (@Projects_Not))

Open in new window

The 'or 0 in (@Project)' and 'or 0 in (@Projects_Not)' are used to 'switch' the other criteria on and off.
You should test it a bit more, but it seems to work.

One problem is when you select both the 'indifferent' option and 1 or more real projects. This gives same result as selecting only 'indifferent'. This might be confusing for the user. But you could test for this condition and than hide your tablix and display an error message instead.
LVL 101

Expert Comment

ID: 36235391
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question