SSRS 2005: how to write an IIF statement to check whether values are in a parameter list

Question

In SSRS 2005, I'm trying to set up an expression to return true or false, according to whether the value of one of the ReportItems is in a list of (possibly multiple) values selected by the user from a list when the report is run.

Here's the expression so far:

=iif(Trim(ReportItems!Category_2.Value) = Trim(Parameters!Category.Value(0)), False, True)

You can see that True and False in the expression are reversed - this is because it's to control the 'Hidden' property of the row, and I want to NOT hide rows where the value is in the list.

The expression works fine when choosing single values for the parameter, but for multiple values, it only displays the results for the very first.

Hope you can help.

Answer 1

Try using

=iif(Trim(ReportItems!Category_2.Value) = Trim(Parameters!Category.Value), False, True)

Answer 2

Many thanks for your help. These multiple values were really bugging me, and I never managed to get Trim to work properly, so I ended up using the following two-stage solution, instead:

(1) In the SQL 'SELECT' statement, I defined a new value called 'InCategory', assigning it a value of 1 or 0 according to whether the Category was one of the inputted Categories or not:

CASE WHEN Category IN (@Category) THEN 1 ELSE 0 END AS 'InCategory', 

(2) Then in SSRS, my expression for the 'Hidden' in the report became simple, hiding the row if the sum of 'InCategory' for that row was 0:

=iif((Sum(Fields!InCategory.Value) = 0), True, False)

Thank you again for your help - I much appreciate it, and will use this site again.