SSRS – Create no rows retrieved message

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.

sss01

 

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.

sss02

 

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.

sss03

 

Click the drop down box and then click Expression.

sss04

 

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"

sss05

 

For extra effect, I changed the font color to red and made the font size 12 points under the No Rows control.

sss06

 

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.

sss07

 

After publishing the report to the report server I run it for a day that has data and the report looks how it should.

sss09

 

I then run the report for a day without data and now a helpful message is returned.

sss08

The better you can develop your reports, the less phone calls and help desk tickets you will receive from your customers.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s