How do I display 'No data available.' when there are no rows to show on the report?

Question

I am building an SSRS 2005 report using BIDS. My report filters on date. When the selected date returns no data rows the report is blank, just the title is displayed, no table or column heading.

How can I change this to display a message like No data available. or Report is empty.?

Solution 1

You could set the property NoRowsMessage available on the report's table control like this:

• Select the Tablix control and press F4 to view the Properties pane.
• Find the NoRowsMessage property and set the value to whatever message you'd like.
• You can also to format the message using the Font and TextAlign properties.
  • 

Here are some examples of what the report will look like under various settings:

Further Reading: Here is a Technet article on how to Set a No Data Message for a Data Region

Solution 2

Avoid NoRowsMessage and build your own conditional empty row

One problem (or feature if that's what you want) with NoRowsMessage is that it'll literally replace the entire table with just a plain old message string.

Which provides a relatively counter-intuitive end user experience in my opinion. Normally when no data is found by a system, we'd like to know something about what data was being looked for and what it would have looked like.

Instead, based off how to keep the structure of the Tablix when there is no data to show, you can do the following:

1. Insert New Header Row, outside the group and above the details record.

2. Right click on the side of the new row and Set Row Visibility

3. Set Visibility to the following expression which will count the rows inside the current Tablix and only set Visibility to True if there is no data.

   =CountRows() > 0
   

   

4. Optionally, merge the cells and add your own message or just display an empty row

Here's a comparison of how the various options will render:
^{(pick whichever look you think best fits your data and use case)}

Solution 3

Right-click on whatever databound element(s) you are using in the report and there should be a property NoDataMessage There are a host of options there but the Caption is the first element I would look at.

Comments

[AbeyMarquez]

+1 This is a gem! Much better than the built-in NoRowsMessage and much more like what I had in mind it should be. Thanks!