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, MCSD
Business Intelligence Consultant – Pragmatic Works
Email: firstname.lastname@example.org | Blogs: SSRSGeek | SQLServerCentral | BIDN