Menu
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?
659
If you want to put multiple conditions in filter , you can use && and || operator. Save this answer.
(1.) Select Filter the list, in-place option from the Action section; (2.) Then, select the data range that you want to filter in the List range, and specify the list of multiple values you want to filter based on in the Criteria range; (Note: The header name of the filter column and criteria list must be the same.)
In this, first, pass your dataframe object to the filter function, then in the condition parameter write the column name in which you want to filter multiple values then put the %in% operator, and then pass a vector containing all the string values which you want in the result.
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
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
