SSRS group by parameter

Question

If I have a report parameter ( @Fruit ) that allows several choices, how can I use this parameter as a group of columns in the SSRS matrix?

Typically, I would use a parameter in my query, as shown below:

 WHERE tbl.fruit In ( @Fruit ) 

Then I would use the column in the dataset as my group. However, in this case I need a complete dataset. I cannot filter the query, but I still want to display only the groups selected by the parameter. If my query returns Apples, Oranges and Bananas, but the parameter selects only Apples and Oranges, there will be only 2 columns in my scoreboard.

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

The group expression used when grouping 'ColumnGroup returned data type is not valid. (RsInvalidExpressionDataType)

My parameter type is Text , and I tried it with and without an empty value.

Answer 1

So, do you want to filter the report object based on the selected values ​​in a multi-valued parameter in the case of @Fruit ?

If, as you say, you cannot apply a filter in a Dataset request / stored procedure, you can apply a filter at the tablix level, for example:

Where is the expression:

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

It basically uses the JOIN function to get a list of selected comma-separated values, and then check to see if the Fruit field in the object's dataset is in this list; if so, turn it on.