CODEWITHSUNDEEP
reporting-servicesparametersgroup-by
CWelch
CWelch

Reputation: 43

SSRS Group By Parameter

If I have a report parameter (@Fruit) that allows multiple selections how can I use the parameter as a column group in an SSRS Matrix?

Typically, I would use the parameter in my query like the following:

WHERE tbl.fruit In ( @Fruit )

Then I would use the column in the dataset as my group. However, in this case I need the full dataset returned. I can't filter the query, but I still want to only display the groups selected by the parameter. If my query returns Apples, Oranges and Bananas, but the parameter only selects Apples and Oranges my tablix should only have 2 columns.

I've tried setting the Group By expression to =Parameters!Fruit.Value, but then I get this error:

The Group expression used in grouping ‘ColumnGroup’ returned a data type that is not valid. (rsInvalidExpressionDataType)

My parameter type is Text and I've tried it with and without allowing blank values.

Upvotes: 4

Views: 9854

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

So it seems you want to filter a report object based on the selected values in a multi-value parameter, in the case @Fruit?

If, as you say, you can't apply the filter in the Dataset query/stored procedure, you can apply a filter at the tablix level like:

enter image description here

Where the expression is:

=IIf(InStr(Join(Parameters!Fruit.Value, ","), Fields!Fruit.Value)) > 0
  , "INCLUDE"
  , "EXCLUDE")

Basically this is using the JOIN function to get a comma-delimted list of selected values, then checking if the Fruit field in the object dataset is in that list; if it is, include it.

Upvotes: 5

Related Questions