Select All parameter doesn't work in SSRS

Question

I have a report which creates a list of Events for a specified date range and event type.

The date range and event type are parameters defined in the report. The date parameters (@DateFrom and @DateTo) work as they should.

The @EventType parameter however, which is defined as a list of values provided by a DataSet (with 'Allow Multiple values' checked), does not provide the expected behaviour when using the {Select All} check box. If I select one or more Event Types by checking several boxes on the list, the report will show the Events which match the specified Event Types correctly.

However, if I click the {Select All} box (which then highlights all of the other possible values), the report does not show the Events for all of these Event Type values. It seems to miss out several of the values which are selected by the {Select All} box. If I run the report specifically for those missing values, the report returns events matching those types. This indicates to me that there is not a lack of data for these types.

And for that reason, it looks to me like the {Select All} is bugged...or perhaps cached somewhere? I've tried deleting the report/parameter dataset and redeploying to no avail. It's worth noting that this behaviour happens locally before deploying it, too.

Has anyone seen this before, or does anyone have any suggestions?

EDIT - I should also mention that the parameter in question (@EventType) has no default value assigned.

Answer 1

How are you declaring your predicate for the variable? It should be be like:

where thing in (@Variable)

Where @Variable is a multi value parameter.

You could attempt to see if the values of the multi valued parameters are junked up somewhere as well by defining them. Generally the collection method of multi valued parameters can cause issues if there data types are different.

Also you may try to design your data set at runtime to build instead of being a static query. EG: Set up an expression for your dataset like:

="Select * from table where thing in (" & Parameters!Variable.Value & ")"

This would cause the parameter to build as part of a string and then evaluate at run time instead of from a traditional query.

Answer 2

Can't quite believe that this was the case, but the parameter which was passed to the SQL Server procedure was too small. It was defined as a VARCHAR(500) and needed to be bigger to deal with a large list of comma separated values. I changed it to VARCHAR(4000) and it's now functioning as expected.

Thanks to Djangojazz for pointing me to the direction of the parameter.