After creating a report in SSRS you may not have data for every day. When this happens the report just shows as blank. For the End User, does that mean the report is broken or is there just no data for that day?
We can easily add a message to alert our users if there is not any data for the criteria they provided.
Viewing the report in SSDT, we provide a date of 1/2/2013 and press view report. It shows the data nicely for that day.
Now if we try running the report for another day, no information shows up. Does that mean the report is not working or what? Sounds like plenty of phone calls and emails to the help desk to me.
We can add a message if a query does not return any results in our dataset. In SSDT or Visual Studio, go to the Design tab of the report and then select the Tablix that you want to provide a message for. You have to make sure it is selected like below. On the right side under Properties, there are controls for No Rows. Look for the item called NoRowsMessage.
Click the drop down box and then click Expression.
This brings up the Expression builder. You can do almost anything with Expressions, but we just want to show a message that there is no data available with that criteria.
Enter this code and then press OK:
="No data is available for this date"
For extra effect, I changed the font color to red and made the font size 12 points under the No Rows control.
Previewing the report in SSDT now shows our message. The user no longer has to wonder if the report is broken or now what is IT doing.
After publishing the report to the report server I run it for a day that has data and the report looks how it should.
I then run the report for a day without data and now a helpful message is returned.
The better you can develop your reports, the less phone calls and help desk tickets you will receive from your customers.