CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008ssrs-2012reportbuilder
himanshu malik
himanshu malik

Reputation: 25

SSRS report giving error for multivalue parameter

I have declared a multi value parameter in my SSRS report. The parameter is "Team" and it has multiple values i text like: "All", "HY", "DFI", "CRS", etc. Now i am using this parameter to show or hide various columns in my report.

For example, or one of the columns , the visibility works as follows:

=IIF(Parameters!Team.Value = "HY", True,False) Or IIF(Parameters!Team.Value = "EQ", True,False) Or IIF(Parameters!Team.Value = "GL", True,False) Or IIF(Parameters!Team.Value = "CRS", True,False)

The report works okay as long as I select one value from the parameter dropdown. But I wanted to select more than one value, so i clicked "Allow multiple values" under the Parameter Properties. Upon doing that and running the report, I get the following error:

The Hidden expression for the tablix ‘Tablix2’ contains an error: Overload resolution failed because no Public '=' can be called with these arguments: 'Public Shared Operator =(a As String, b As String) As Boolean': Argument matching parameter 'a' cannot convert from 'Object()' to 'String'. (rsRuntimeErrorInExpression)

How can I resolve this error?

Update: The error is resolved now. But the displaying of combined columns, which was the purpose behind adding multiple values in the parameter is still not resolved, when I select more than one team in the dropdown, eg. "DFI" and "HY', then instead of displaying columns for both, the report hides columns for both teams.

Thanks in advance.

Upvotes: 1

Views: 7112

Answers (2)

Martin Navarro
Martin Navarro

Reputation: 624

Because your team parameter allows multiple values to be selected, Parameters!Team.Value becomes an array object. This means you cannot make a comparison Parameters!Team.Value = "aString" because they are incomparable types.

In order to achieve the result you want, you would have to change your Parameters!Team.Value = "aString" calls to Join(Parameters!Team.Value,",").contains("aString").

The Join Function returns an Object array delimited by the specified string.

Example: Join(Parameters!Team.Value,",") Can return "HY,EQ,GL"

For each set of columns, you will need to set the visibility for each team where it is set to False where you don't want columns hidden.

For you columns with "DF" or "All" selected:

=IIF(Join(Parameters!Team.Value,",").Contains("DF") OR Join(Parameters!Team.Value,",").Contains("ALL"), False,True)

For columns with "HY" or "All" selected:

=IIF(Join(Parameters!Team.Value,",").Contains("HY") OR Join(Parameters!Team.Value,",").Contains("ALL"), False,True)

For columns with "EQ" or "All" selected:

 =IIF(Join(Parameters!Team.Value,",").Contains("EQ") OR Join(Parameters!Team.Value,",").Contains("ALL"), False,True)

Upvotes: 2

Russell Fox
Russell Fox

Reputation: 5427

Remember the "hidden" property is True when the column is hidden, and False when it's shown. You can use the Array.IndexOf function to look for a specific value selected in the multi-value parameter, and set each team's column like this (this is team HY):

=IIF(Array.IndexOf(Parameters!Team.Value, "HY") > -1, False, True)

In English: "If you find HY as a selected parameter value, do not hide this column, otherwise hide it."

Upvotes: 0

Related Questions