SSRS – Exclude Rows Based on Field Value and Display Message if No Results

ssrs

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?

Best Answer

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.

Related Question