SSRS Cascading Multi Value Parameters not passing second Parameter values to report

Hi there, I'm working with SSRS Report Builder 3.0 and have two cascading parameters that appear to be working fine.

I have a dataset set up for Param1 and another dataset configured for Param2.
The Param1 dropdown list works fine, and passes the correct Param1 values to the Param2 dropdown list which shows the correct sublist of items available for multiple selections.  

I have the main dataset edited in the 'Where' clause to filter to the items selected in Param1,, and also to filter to the items selected in Param2.


The problems:
1) When i have both filters applied in the where clause, no records are returned although the cascading parameters appear to be working fine.

2) When i remove the Param2 filter from the Where clause, all of Param1 matches are returned, but Param2 selections are not applied.

Not sure how to confirm this but my guess is that Param2's values are not in a format that the report can use, and I'm not sure about how to resolve this issue. I've found multiple articles on cascading parameter configurations, but I'm apparently missing something around the sub-list and how it passes values to the report to use.  Any assistance would be appreciated

Also the parameters used in the where clause are in parentheses () and i'm stating to look 'IN' the parameter value, and not using '=' equal to.
Calvin LeBlancReporting EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Calvin LeBlancReporting EngineerAuthor Commented:
hmm.. still trying to work this out. it's odd that i can use one multi option dropdown list and it works fine, but when i use two, although the second dropdown parameter list appears to be working fine, the information is not passed to the report. Any insight would be appreciated.

Calvin
Calvin LeBlancReporting EngineerAuthor Commented:
Calvin LeBlancReporting EngineerAuthor Commented:
Not working yet.. :-/
Calvin LeBlancReporting EngineerAuthor Commented:
still working on this,, no solution yet.
Calvin LeBlancReporting EngineerAuthor Commented:
Okay,,, here's a extremely simple configuration that works for understanding the concept and moving pieces:

Cascading Parameters Setup on Microsoft SQL Server 2012 / Report Builder 3.0

Create a table using the data shown below:
Group      Members      Data
A      A1      Something A1
A      A2      Something A2
A      A3      Something A3
A      A4      Something A4
A      A5      Something A5
B      B1      Something B1
B      B2      Something B2
B      B3      Something B3
B      B4      Something B4
B      B5      Something B5
C      C1      Something C1
C      C2      Something C2
C      C3      Something C3
C      C4      Something C4
C      C5      Something C5

DataSets
Create the Main data set
SELECT
  TestDatatbl1.[Group]
  ,TestDatatbl1.Members
  ,TestDatatbl1.[Data]
FROM
  TestDatatbl1
Where (TestDatatbl1.[Group] IN (@PGroup)
AND TestDatatbl1.Members IN (@PMembers))
Order By TestDatatbl1.Members ASC


Create a data set for each parameter
ds_GroupLst
SELECT
DISTINCT
[Group]
FROM [Testdb].[dbo].[TestDatatbl1]

ds_Members
SELECT
DISTINCT
[TestDatatbl1].[Group]
,[TestDatatbl1].[Members]
FROM [Testdb].[dbo].[TestDatatbl1]
Where [TestDatatbl1].[Group] IN (@PGroup) << add this line after the parameters are created and set up

Configure Parameters
PGroup settings:
1-General > Allow multiple values
2-Available Values > Dataset > ds_GroupLst
2-Available Values > Value Field > Group
2-Available Values > Label Field > Group
3-Default Values > Dataset > ds_GroupLst
3-Default Values > Value Field > Group

PMembers settings:
1-General > Allow multiple values
2-Available Values > Dataset > ds_Members
2-Available Values > Value Field > Members
2-Available Values > Label Field > Members
 (default value not set)

NOTE:  (THIS IS A KEY STEP) Go into the ‘Main dataset > Parameters > and for the members parameter value, create an expression using the split and join functions
  =split(join(Parameters!PMembers.Value,","),",")

Test the configuration by inserting a table to your project and selecting items from the multi select dropdown lists.

Seems like i had to simplify it, to 1) understand it, and 2) to get it to work.
Now I'll apply it to my original project :-)

Hopefully this will help someone..

Calvin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.