How to Use String.Filter with Multi-Value Parameters

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!Multiv开发者_开发问答alueParameter.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?

---

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")

---

I used a multivalue parameter in SSRS 2008 to show/hide columns in a tablix.

However I was never able to take advantage of the actual substring returned, and could only make use of the result via length.

=IIF(Filter(Parameters!MultiOption.Value,"3",true).Length>0,"T","F")

So for each column, (1st 2nd 3rd and 4th column) I would use this code (with true/falsepart not as strings as shown above).

Other attempted idea's were:

=Filter(Parameters!MultiOption.Value,"3",true).GetValue(0).ToString

Which can output 3, however in a false condition, GetValue(0) is out of bounds.

=IIF((Filter(Parameters!MultiOption.Value,"3",true).Length>0),
Filter(Parameters!MultiOption.Value,"3",true).GetValue(0).ToString,
"",)

Which effectively should only return the substring value if one exists. It did not work out like that though.

Dallas