No Results Found

Suppose you run a query and it comes up blank?  Now, is that because there are no matching records, or is it because you got something wrong in the code?  Obviously at first the answer is (b) - there is always an error in code.  That’s why you test and test again, and then get someone else to have a look because they see immediately the things that you miss. 

But once you have got the code working properly, and you deploy a report to SSRS, you need a way of telling people that there are genuinely no matching records that satisfy the query’s criteria.  Otherwise they might ring up and tell you that the report isn't working, and disturb your meditation, or at least make your tea grow cold as you deal with their problems.  

Here’s how: add a textbox saying “no matching records” or words to that effect.  This is SSRS 2005, but subsequent versions look much the same:

Now edit the visibility property of that textbox. 

=iif(Len(Sum(Fields!bodid.Value, “MIDAS”))>0,True,False)

In this example, Midas is the name of the dataset, and the Bodid is an integer, a unique reference for each person in the table.  (Note that the field names are case sensitive, at least in 2005.  I haven't checked 2008 - so  bodid <> BodID).  

If records are selected, the length of the sum of the Bodids will be greater than zero, and the textbox will be invisible.  On the other hand, if there are no records, the length is zero and the message in the box appears.