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?

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

Solution 2

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

Comments

[Hugh Allen]

What does Parameters!MultivalueParameter.Value.GetType().ToString() give you?

[Hugh Allen]

How about Parameters!MultivalueParameter.Value(0).GetType().ToString()‌​? (the type of the first array element)

[Registered User]

I tried the first expression and it returns a blank text box. I tried the second expression and it returned a blank text box. I tried experimenting with variations on your suggestion, but it either returned blank values or #Error.

[NakedBrunch]

Getting a blank textbox for the first expression is a good sign in that at least it is not an error. It is an odd result but still a different result that may be used for troubleshooting. The second bit of code had the Length method listed in the wrong place. I've since updated it. I have this working on a test report so there is definitely a working solution. We just need to figure out why it isn't working on your report.

[Registered User]

One step further. The first expression returns a blank. The second expression returns false. I'm going to try using the .Label instead of .Value. .Value consists of values from 1 to 100 (roughly) whereas .Label consists of English words. I'll change value to check in the filter to match one of the English words.

[Registered User]

I didn't have any luck with using .Label instead of .Value. It just returns a blank.

[NakedBrunch]

A blank is indicating that the data is likely null for some reason. If there was a problem with the expression then you'd be seeing the error. Try this just to make sure that data isn't null: =iif(isnothing(Filter(Parameters!MultivalueParameter.Value, " 3 ").Length),"Data is null", "Data is not null")

[Registered User]

I verified that it has values -- 76 separate values, in fact.

[NakedBrunch]

Does Parameters!MultivalueParameter.Value(5) return a value or a blank?

[NakedBrunch]

I've tested again and again and I can't find any reason why this isn't working for you. The last thing I can suggest is to eliminate all other possible reasons for this not to work. Create a brand new report project with nothing but the multivalue parameter and try the above code and see if it still doesn't work.

[Registered User]

I tried building a report from scratch and following your advice and nothing changed. Parameters!MultivalueParameter.Value(5) returns a value as expected.

[NakedBrunch]

Unbelievable. I'm honestly stumped. We're both working in SSRS 2008 and a brand new blank project works perfectly for me. I feel like we've exhausted all troubleshooting options. When you verified that the dataset has values, did you verify simply by checking the data returned from a query or did you run the "IsNothing" code I provided? I have no doubt that your dataset contains 76 values but I'm trying to figure out why Filter(Parameters!MultivalueParameter.Value, " 3 ").Length appears to be null.

[Registered User]

Although I still can't get this to work, I marked your answer as correct simply because you indicated it works on your end and you clearly worked hard to solve this problem. I'll keep experimenting with it. I verified the reports behave consistently between BIDS and Report Manager, so it can't just be an issue with my machine.

[Gabriel Guimarães]

Awesome Alison, one cool Tip is to =CBool(Filter(Parameters!MultivalueParameter.Value, "3").Length) if you need to invert the bool value like the CSharp ! you can use the Not in front of CBool like this =Not CBool(Filter...