http://www.sqlservercentral.com/blogs/whatilearntatwork/2011/10/28/displaying-blank-sub-report/

Printed 2014/08/21 02:24PM

Displaying blank sub report

By sureshkumar.d, 2011/10/28

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)



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.