No, I am not talking about those big circular black vinyl objects that play music. When I say records, I am referring to the rows of data returned from a source of data. When using Business Intelligence Development Studio to design your reports, you can set a property called NoRowsMessage for any of the data regions. And check this out! No expression is required! It just knows whether or not a result set is returned and displays or doesn’t display the response you want to display. It’s that easy!
I wrote most of this a while ago planned to publish it a couple of weeks from now, but I recently came across a posting regarding returning a message to a report requester stating that no records were returned using an expression to do the work! And though I have seen (and used a long time ago I must admit), now there is a much easier way than just hiding and showing a response based on an expression. Since expressions can take a little bit of processing time at the report layer, I figured now was a great opportunity to enlighten others on this much overlooked property.
In figure 1, you will see an example of how to use this property group. Just select your data region and then hit F4 or navigate to the properties window for the item selected. Scroll to the No Rows section and find all the wonderful properties you can set when no records are returned.
Figure 1: No Rows Property Group
As shown above, I have entered a fixed value for the NoRowsMessage. Alternatively, you could create an expression to return the parameters that you selected. This is shown in figure 3 below. Also, since I wanted to give it a little extra pop, I made the response RED and EXTRA BOLD.
Just so you see that there is nothing up my sleeve and that I am not doing some expression to determine if any records were returned, see the figure 2 below.
Figure 2: Report in Design Mode
Figure 3 is showing you the results if the data region didn’t have any results returned. In my example, I searched for a product name of “Brian K. McDonald”. Since technically speaking, I am not a product in the AdventureWorks.Production.Products table, nothing is returned except our handy NoRowsMessage response that I configured.
Figure 3: No Rows Were Returned
If you entered a valid value such as “Touring”, the results may look something like that shown in figure 4.
Figure 4: Results with Values
In conclusion, the No Rows property group can be found in any of the data region controls, but for this example, I showed to use it in the Tablix data region. This property group can be used to return a beautifully formatted message to the requestor which notifies them of the lack of existence of results.
For your convenience, you can download a copy of this report here. I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.
Thanks for reading,
Brian K. McDonald, MCDBA, MCSDBusiness Intelligence Consultant – Pragmatic Works
Email: bmcdonald@pragmaticworks.com | Blogs: SSRSGeek | SQLServerCentral | BIDN
Twitter: @briankmcdonald
That is a good tip. I did not know about that feature. Is it new to SSRS 2008 R2?
This is funny. Chris Albrektson just did a blog on this topic, but using an alternate method.
http://www.bidn.com/blogs/ChrisAlbrektson/bidn-blog/1144/no-data-available-for-the-selected-parameters
I commented on his blog about this method, then shortly afterward I saw this article. It seems only fair to refer to his alternate method here.
Good article, good blog. Thanks to both.
Patrick, I can't remember if No Rows is available on 2005 and I do not have an instance to check on, but I know that 2008 and 2008 R2.
Daniel, You are correct! Chris did write a post about this and he is who I am referencing. However, I wrote 90% of my blog on 8/14 and like this one, I have many queued up to be published at a later time. When I saw his using the older and less efficient way, I figured now was a great time to post mine as well. Especially considering the benefits. At any rate, Chris did show an alternate way than using the built in properties in case anyone wants to use this method.
Nice Post! BTW No Rows is available on 2005 also.
Thanks Samuel. I still haven't had an opportunity to install an instance of 2005, but I'm glad that you have confirmed that for Patrick!
Updated Post to include zip file for download since RDL doesn't work. You can download it here until the update has been approved.
Nice article! Is it possible set BackGroundColor for that message ? Because here you have highlighted your messgae in YELLOW background. I tried, but couldn't find any luck.
Regretably, BackGroundColor is not a property of the "NoRows" property group. However, there is a trick to doing what you are trying to do! All you'll need to do is create an expression for "BackGroundColor" of your table/tablix to check for the count of records in that result set. If it equals zero, then change the background color.
Have fun!
I like the background color trick, thanks