Using Filters in Reporting Services

This is a technical post, but I discovered something very helpful today and I wanted to share it with whomever it may help.  I had a need within Reporting Services 2005 to display a table, and only display a line of text if a particular value populated at least one row of that table.

I didn't want to alter the stored procedure because I don't have permissions for that, and to request it and wait is more trouble than its worth.  So I wanted to handle this within Reporting Services itself.

I tried to use the "Count" function.  However, the Count function only returns the total number of values returned in the dataset.  I wanted to do something like this in the Hidden property of the table row containing my text:
=IIF(Count(Fields!ActionCode.Value=21)>0, False, True)

However, since Count only returns the total about of data it was always returning a false value whether ActionCode equaled 21 or not.

My solution:
I defined another dataset that queried the same stored procedure as my dataset used to populate my table.  However, in this dataset I clicked on the Filters tab.  In that field, I filter the data to only return records where the ActionCode = 21 (In the "Value" field, be sure to specify =21, because simply entering 21 treats it as a string value).

Now, back in the hidden property field I can use the "Count," but I specify the new dataset:
=IIF(Count(Fields!ActionCode,"dsCheckForRemoved")>0, False, True)

Now...since 21 represents a "removed" condition I only get back records in this dataset if that condition exists.

This gives me what I need and I can display that message independent of the other rows in the table.

Hope this helps someone.