I want to exclude rows from an SSRS report based upon a field value and display a message if, as a result of filtering, there are no results

Question

I have a report where I want to exclude most of the rows based upon a field value. That part is not so hard. The value that I am filtering out is "OK". so in the end the report only displays rows that contain warnings. Typically only one or two rows out of 50-60.

I have been using the expression:

=IIF(Fields!ALERT_VALUE.Value = "OK", True, False) for the row.

Now we get to the tricky part. If there are no warnings then I just get an empty report. What I want to do is display a message that indicates that there are no warnings.

So, I created a new row and set this expression in the first column:

=IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,"No Commitment Warnings", nothing)

And set visibility as:

=IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,False,True)

But I get nothing.

I think I read somewhere that I an just hiding the rows and not removing them from the report so I guess that my message row is still seeing the hidden rows and that is whey it does not work.

But I swear I had it working this morning LOL!

So, is there a way to get what I am trying to do? Hide all the no warning rows and if there are no warning rows, show the message?

Answer 1

I'm a little confused on your logic, because the visibility expression =IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,False,True) will hide the row if the count is 0 which is opposite the logic it sounds like you're trying to achieve with =IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,"No Commitment Warnings", nothing) and based on what you said. If so, then all you may need to do is flip False and True around in your visibility expression like this =IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,True,False).

For reference, SSRS's functions are derived from VBA, so you can find the docs on the IIF Function here.

Answer 2

OK, so it looks as if the trick was to get a row count of the rows where Status = "OK" and compare that to the count of all rows. If they are equal then there are no warning messages and we display the message row.

If they are not equal, then there are warnings and we do not display the message row.

And the trick to doing all that was to sum the rows with a status of OK like so:

=iif(countrows()<>sum(iif(Fields!ALERT_VALUE.Value=”OK”,1,0)),true,false)

Thanks to my friend Charles for pointing me in the right direction.