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

Calvin LeBlanc
Calvin LeBlanc used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Calvin LeBlancReporting Engineer

Author

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 Engineer

Author

Commented:
Not working yet.. :-/
Calvin LeBlancReporting Engineer

Author

Commented:
still working on this,, no solution yet.
Reporting Engineer
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial