This article demonstrate how to display a message when no row or data found for user selection in SSRS report.
Lets see step by step implementation of how to display a message when no row or data found in SSRS.
As you can see, here we have a sample report that allows user to filter the report data based on parameters or you can say dropdown country, state and city shown below.
Lets see the report preview, you can see report data is filtered for user specified values.
Lets see, in case when data is not found for user specified input values then report display blank row, or you can say a empty report with header columns only as shown below.
You can see, for user input values there is not data to display. So in this case, report display blank row, what you can see on report are column headers only.
It can really makes end users/ report viewer to think either something went wrong or report really does not have any data to show.
Therefore, it is always a good practice to show proper message when there is no data for user selection
To display a message when no data exists in database or no row found for user specified values you can go to report Tablix property.
Select report Tablix, then press F4 to open the property window ( in case if you have not already opened), then find a NoRowsMessage property as shown below.
Now specified you message that you want to display when no data found.
Here we give a message as “No Data Found for you selection !!”.
Once you done with this, lets go to preview page and see it in action.
You can see, it returns a message when no data found.
Lets see what happens when report display data, and in this case you can see message will be shown.
If you want, you can format the message text font, color, and size as well.
Lets format the message text, change font color to Red, and make it bold.
Lets see the message in report preview.
Lets see one more way of displaying message.
first remove all the steps that you made in previously, just remove the message text from NoRowMessage property.
Now will take a text box to display a message, and show and hide the visibility of text box and report Tablix based on expression condition.
Lets see the implementation, first right click on name column then select Tablix Property.
After that, you will see a Tablix Properties window opens.
Now click on Visibility tab then select Show or hide based on an expression check box, and then click on fx icon.
Once you click on expression fx icon, an expression window opens.
Now in Category selection select Fields then double click on Name in Item section.
Now modify the expression as shown below, after that click on OK button.
= IIf(Fields!Name.Value IS NOTHING, True, False)
Once you click on OK button, you get back to Tablix properties window, just click on OK button.
So far we set the Tablix property to show and hide based on expression, means when no data found then report will be hidden else shown.
You can check this, just go to report preview. You can see when no data found for user selection, it hides the report also you may notice that it hides the column headers too.
Lets go back to design page and provides a message.
Once you take the textbox, just write a message and customize the font size, and color for it.
Once you done with this, it would look like as shown below.
Now right click on text box, and select Text Box Properties from context menu.
After that, you will see a Text Box Properties window opens, go to Visibility tab and check the Show or Hide based on expression option.
Then click on expression fx icon.
Now write the expression as shown below.
= IIf(First(Fields!Name.Value, "CustDs") IS NOTHING, False, True)
Once you click on OK, you get back to Text Box Properties window then just click on OK button.
Lets go to the Preview page and see the implementation.
You can see, the message when no data found.
You can also format the textbox alignment as center to show the message at report page center.
Also, you can drag the text box over the report Tablix, and then set textbox layout property as send back.
After dragging the textbox over Tablix, it would look like as shown below.
After that, right click on textbox, and navigate to Layout and select Send to Back form context menu.
Once you done this, you will see now message is send back to tablix. It would only appear when no data found for user specified inputs.
Now you can see, the message is displayed as shown below.
Also Read..
Create a Shared DataSource in SSRS
Create a Shared DataSet in SSRS
Create a SSRS Tabular Report / Detail Report
Keep row header visible while scrolling down, or on each page
Add alternate row background color
Multi – value Parameters in SSRS
Display checkbox for active and inactive values in SSRS report
