Displaying blank sub report

Recently I had to convert a stand alone report into a sub-report. That report was very simple… it had two date parameters , a data set displayed in a tablix,  NoRowsMessage was enabled on the tablix to display a message when the data set returns empty row.

This report feature, NoRowsMessage worked fine when it was a stand-alone report. However when I added this report as a sub-report in another report, the dataset’s NoRowsMessage did not appear when the dataset was empty. I thought this might be due to caching and I tried different options, like building the report, deleting the .DATA file , closing the project, closing the BIDS itself. But nothing worked.

I removed the NoRowsMessage on the tablix,  added a text box and added an expression in the Visible property in both tablix and textbox to toggle the visibility based on the record count in dataset. Even with this approach, the Textbox was not visible when the dataset was empty. However when I ran it stand-alone, it worked as expected.

After spending more than couple of hours, I came across this post which says that if all the datasets in the sub-report returns zero rows then the sub-report will not be displayed. The solution to display no row message in this case was very simple 🙂

(1) Create a dummy data set in the sub report to return at least 1 row OR

(2) Set the NoRowsMessage at the sub-report level (in the main report)

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in SSRS. Bookmark the permalink.

4 Responses to Displaying blank sub report

  1. Edyth says:

    The information on this site is handy.

  2. Mark Doubson says:

    You need to se NoRowMessage as propery of subreport in main report properties set

  3. Jason Dawida says:

    This is a great suggested fix. I tried using the NoRowMessage property on my subreports to no avail. Adding the secondary blank dataset was the fix for the issue.

    • Jason Dawida says:

      I will in addition to my previous comment say that I choose the method I did due to the limited formatting options available through the NoRowMessage property.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s