How to Use String.Filter with Multi-Value Parameters

Question

In the Expression builder window in SQL Server Reporting Services 2008 R2 under Common Functions -> Text -> Item, there is an expression called Filter. This appears to correspond with the Strings.Filter method in the .NET framework. The description of Filter is as follows:

Returns a zero-based array containing a subset of a String array based on specified filter criteria.

The Example is as follows:

=Filter(Parameters!MultivalueParameter.Value, "3", True, CompareMethod.Binary)

The example and description imply that you can inspect a multi-value parameter to see if at least one of the selected values is equal to the Match parameter. I haven't been able to get this to return anything other than #Error which implies the multi-value parameter is not a one-dimensional array. Parameters!MultivalueParameter.Value.GetType().ToString() returns System.Object[].

Does anyone know how to get this to work? I'm using the following work around to check if values were selected in the multi-value parameter:

=IIF(InStr(" " + JOIN(Parameters!MultivalueParameter.Value, " ") + " ", " 3 ", CompareMethod.Text), false, true)

The above code works, but it is pretty ugly. I would prefer to use the Filter function if it supports this kind of check. Can anyone give an example of code that works?

Answer 1

The problem is occurring because the example from MSDN is somewhat lacking for this discussion. It is true that =Filter(Parameters!MultivalueParameter.Value, "3", True, CompareMethod.Binary) returns an array but, in terms of SSRS, you can't simply output an array to a report. That is part of the reason why you're seeing the error.

Also, SSRS seems to have problems handling the two optional parameters of the Filter function. Leave those out and you'll be good to go.

You can immediately test this out by outputting the length of the array to a textboc.

=Filter(Parameters!MultivalueParameter.Value, "3").Length

The above should result in a textbox with the integer length of the filtered parameter.

So, pullling this all together, you can achieve your desired result with the following:

=IIF(Filter(Parameters!MultivalueParameter.Value, " 3 ").Length > 0, "false", "true")