SSRS expression that evaluates if any string in group matches parameter

Question

I have a SSRS report that contains the following DataSet.

 Foo | Bar
-----------
  1  |  A
  2  |  A
  2  |  B

I am displaying this data in a Tablix, there is a row grouping on Foo.

 Foo | Quantity
----------------
[Foo]|[Count(Bar)]

The above produces:

 Foo | Quantity
----------------
  1  |    1
  2  |    2

I also have a string parameter bar, when this parameter matches any of the values of Bar found in the row group I would like to highlight the Quantity cell.

i.e.

=IIf(Fields!Bar.Value = Parameters!bar.Value, "Plum", "Orange")

If bar is set to 'A', the above expression will provide the desired behavior for Foo = 1, but not Foo = 2.

I saw a similar question related to booleans, but the solution does not work with strings. Perhaps SSRS has some Any or Contains functionality I'm not familiar with?

score 1 · Accepted Answer · answered Dec 03 '19 at 16:36

If I understood correctly....

I think your issue is that you are trying to evaluate each row to see if Bar matches your parameter but the cell you want to highlight is an aggregate - its value is based on several rows of data potentially.

You should be able to just get a count of matching rows and if that is greater than zero, then highlight the cell. For example

=IIF(
    SUM(IIF(Fields!Bar.Value = Parameters!bar.Value,1,0)) > 0
    , "Plum"
    , "Orange"
    )

This just evaluates each row to either 1 or 0 (match or no match), sums the results and if it's greater than 0 (we found a match) then return "Plum"

score 0 · Answer 2 · answered Dec 03 '19 at 16:38

I think the best solution to your problem may be to modify the dataset query to output a new field Foobar that concatenates the Bar values over the Foo grouping. Using STRING_AGG or FOR XML PATH such as in the answers to this question, you should be able to get data like the following:

 Foo | Bar | Foobar
--------------------
  1  |  A  |   A
  2  |  A  |  A, B
  2  |  B  |  A, B

With the data formatted this way, you can use the .Contains syntax to check each Foobar value for the bar parameter.

= IIF(Fields!Foobar.Value.Contains(Parameters!bar.Value), "Plum", "Orange")

Another option is the InStr function.

= IIF(InStr(Fields!Foobar.Value, Parameters!bar.Value) > 0, "Plum", "Orange")

SSRS expression that evaluates if any string in group matches parameter

2 Answers2