SQL Scrapbook

Short and simple SQL Server posts

Posts Tagged ‘SSRS

Custom message when there are no rows in the data source

leave a comment »

Some times when there is no data in the data source the SSRS report created on top of this will show only the header information. There is an inbuilt option to show your own message when this is the case. In this quick post i will demonstrate how to do this.First, i will create a dataset and make sure that this dataset doesn’t return any rows.

SELECT    TOP 0 FirstName, LastName, [State]
FROM dbo.ResultTable

If you take a close look at the above query, i used TOP 0 to make sure that the dataset returns 0 rows. Now that we have the dataset lets go ahead with the report design. I created a table report and the fields are First Name, Last Name and State.

Since there are no rows returned when you run the report we will see only the report headers.

Now to display a custom message when there are no rows there is a property called “NOROWS”. Select the table and in the properties pane find NOROWS.

Click on the expression button and enter any custom text.

Now that you have the NOROWS property configured you will see this message when you run the report and there are no rows returned by the dataset.

Conclusion: NOROWS property in SSRS can be set to display a user friendly message when the data set doesn’t return any data.

Written by sqlscrapbook

July 13, 2010 at 10:06 pm

Posted in SSRS

Tagged with ,