Cascading Parameters in SSRS

Question

I have a multi select drop down parent parameter (which populates from various other parameters on the report) that I need to get the multiple values of in order to run my actual query to get the report data set.

Whenever I select multiple values from the parent drop down list, it only returns the first row. Also, when I uncheck or check more values, or change the value of the drop down, the result set never changes.

Does anyone know what can be causing this and if it can be fixed? Is this an issue with cascading parameters in SSRS?

Thank you.

Answer 1

The sort order of your Available Values must match the sort order of your Default Values. So if the Available Values Query (or Specified Values) for your Parameter are something like:

Value Label
----- -----
1     John
2     Jacob
3     Jingleheimer
4     Schmidt

And the Default Values for your Parameter passed are:

Value
-----
1
4
3

Only 1 will be selected in the drop down. It has to be:

Value
-----
1
3
4

To accomplish this, I suggest using Queries for both Available and Default Values, and sorting the Queries on the same Column. Example:

-- Available Values
Select UserID Value, UserName Label From YourTable Order By UserID

-- Default Values
Select UserID Value, UserName Label From YourTable
Where YourColumn = @YourParameter Order By UserID

Answer 2

Use Join(Parameters!ParentParameterName.Value,",") in the child(dependent) parameter for passing in the parent parameter, with an IN clause in the parameter's SQL query(if present).