Since there is no way to pass value from subreport to the main report, how to suppress the subreport when it is blank? Thanks.

The subreport reportitem (in the main report) has a NoRows property. If all the dataset queries inside the subreport return 0 zero rows and the NoRows property is set explicitly, it will replace the contents of the subreport with the NoRows message (which could be a blank string). -- Robert

OK, so how do you hide the row that contains the subreport is the subreport is blank? Is there an expression you can enter into the NoRows property? Thanks

Ditto -- What is the expression to suppress the table row that contains the subreport when the subreport has no data? Thanks, Pete

Can you explain your scenario in more detail please? The closest you can get right nowis to have the subreport queries in the main report and then determine the visibility of the table row in the main report based on the CountRows("DataSetName") aggregation result. -- Robert

Hi Robert: Basically I'm testing SSRS 2005 compared to our current report design/production in Crystal Reports XI. One of the features of CR that we use extensively is subreports. When a particular section of a CR report does not have relevant subreport data to display, we dynamically hide the main report section that hosts the subreport so as not to display any info that is in the samemain report section but not in the subreport. I've found a thread that appears similar in nature to my situation: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=423404&SiteID=1 Most discussions I've reviewed about dynamically hiding subreports in SSRS resolve to the conclusion that if a subreport does not have data, then the subreport hides itself which should be "good enough". This feature, while useful sometimes, won't help my situation in which other data from the Main report would also need to hide. I've attempted to nest regions, instead of using subreports,(e.g., a List region that nests a Table region) whereby the List region refers to dataset1 while the Table region refersto dataset2, but I am unable to display the report successfully. I usually receive an error that I am attempting to display an aggregate within a nested data region. By the way, the link I mention above has a comment that "in order to use a nested data region you have to (unfortunately) use the same dataset as the parent dataregion, which means that you will have to combine the datasets for the report and subreport into one dataset/query. If you NEED to use a separate dataset you will have to use a subreport." Is this a correct statement? So far, the solution I've crafted requires me to alter the sql stored proc to support both the main report and subreport, effectively forcing a one-to-one relationship between the main report and subreport. Ideally, I'd like to construct a report where the main report can have linking keys not always found in the subreport (i.e., similar to a sql outer join), and then have the main report dynamically hide/show the hosting data region or table row. Even better would be to avoid using subreports, and figure out how to have a single report use data regions with different datasets such that the same effect is achieved as if I had used subreports. (This last point assumes better query/reporting performace can be achieved.) Lastly, I've attemped to filter results of one data region based on data in another data region without success. Again, usually I've attempted this by nesting data regions unsuccessfully -- possibly because each data region refers to different datasets? Thanks, Pete

Hi Andrea, Did you find the solution to hide the row? i have same problem.. Thanks alot..

No, I did not. Instead I have 'No data available' displayed when there is no data appearing from the subreport.

In one sense I have the opposite problem. My subreport is hiding itself because it has no data. However it contians static text that I need to have showing. Is there a way to force the subreport to stay visible?Thanks,Jonathan

One option is to add a dummy dataset to the subreport that returns at least one row. For example,use a query commandtext similar to this: Code Blockselect 'abc' as dummy -- Robert

This suggestion above from Robert solved the problem I was having as well. It seemed that even when I put a value in the "NoRows" property, the subreport was always suppressed if there was no data in it. Creating a dummy dataset inside of the subreport caused the value in the "NoRows" property to be displayed.

A possible workaround (that I implemented in one of my reports) is to reduce the row height in which the subreport has been integrated to a negligible value. The subreport will be displayed in main report with the properties mentioned in the subreport itself. So, if you reduce the row height, it will not affect the report display if there exist a value. In all other cases, you will not see a blank space over there in the main report. (The row boundaries also could set to 'None' values in main report) Select the row properties -> under 'Layout' section, you could find the 'Height' parameter. Provide value for this parameter as 0.01 cm or something similar

I didn't understand this can you please explain me in detail how to do this. My scenario is i am working on a master report in which i am using many sub-reports , so if any sub report doesn't have data then i can hide the sub report by writing expression in the visibility properties but still the blank pages of it comes out when i preview. Its only hiding the data but still taking up the space of the sub-report and printing it. Is there any kind of suppress function in SSRS like Crystal report had. Please i want to get rid of this blank pages, suggest me something... Thanks, Affu

I didn't understand this can you please explain me in detail how to do this. My scenario is i am working on a master report in which i am using many sub-reports , so if any sub report doesn't have data then i can hide the sub report by writing expression in the visibility properties but still the blank pages of it comes out when i preview. Its only hiding the data but still taking up the space of the sub-report and printing it. Is there any kind of suppress function in SSRS like Crystal report had. Please i want to get rid of this blank pages, suggest me something... Thanks, Affu

OK. For the benefit of anyone struggling with this solution worked for me... (this is using SQL Server Business Intelligence Development Studio 2008 / i.e. Visual Studio 2008) Open the Sub Report .rdl File and make the following changes: In the bottom pane where Row Groups and Column groups are shown there is a black Triangle on the right hand side, click this and select "Advanced Mode". This should then cause various items in grey labelled "(Static)" to appear in the Row Groups and Column Groups sections. Click the top most entry marked "(Static)" in the row groups section to select it. This should cause the Properties pane to show properties for Tablix Member. In the properties change the HideIfNoRows property to True. Save the Sub-Report, then move to the parent report and run it - This should cause the sub-report to collapse as required. Hopefully this might save someone some head scratching...I've wasted a couple of hours on it already!! Cheers, Alex.

OK. For the benefit of anyone struggling with this solution worked for me... (this is using SQL Server Business Intelligence Development Studio 2008 / i.e. Visual Studio 2008) Open the Sub Report .rdl File and make the following changes: In the bottom pane where Row Groups and Column groups are shown there is a black Triangle on the right hand side, click this and select "Advanced Mode". This should then cause various items in grey labelled "(Static)" to appear in the Row Groups and Column Groups sections. Click the top most entry marked "(Static)" in the row groups section to select it. This should cause the Properties pane to show properties for Tablix Member. In the properties change the HideIfNoRows property to True. Save the Sub-Report, then move to the parent report and run it - This should cause the sub-report to collapse as required. Hopefully this might save someone some head scratching...I've wasted a couple of hours on it already!! Cheers, Alex.

OK. For the benefit of anyone struggling with this solution worked for me... (this is using SQL Server Business Intelligence Development Studio 2008 / i.e. Visual Studio 2008) Open the Sub Report .rdl File and make the following changes: In the bottom pane where Row Groups and Column groups are shown there is a black Triangle on the right hand side, click this and select "Advanced Mode". This should then cause various items in grey labelled "(Static)" to appear in the Row Groups and Column Groups sections. Click the top most entry marked "(Static)" in the row groups section to select it. This should cause the Properties pane to show properties for Tablix Member. In the properties change the HideIfNoRows property to True. Save the Sub-Report, then move to the parent report and run it - This should cause the sub-report to collapse as required. Hopefully this might save someone some head scratching...I've wasted a couple of hours on it already!! Cheers, Alex. Alex, I was trying to lacate that black triangle but was not able to find it. Could you pls. elaborate where exactly it that?? is it in data region or on property window or in solution explorer?

Look in the Design mode of the report and in the lower right hand side you will see an upside down triangle on the same pane with Row Groups and Column Groups.

is this feature available in 2005?

In VS2005 Right Click on SubReport -->Format objects-->Select SubReport tab--->select supress supreport when no data checkbox.

I'm sorry but i was not able to find the way you have shown the navigation. while in main report i right clicked subreport but i was not able to see format objects and then i opened the subrpt.rdl but again no luck. cud you pls. elaborate bit more. Thanks.SSRSRpt

Ok. No where in this thread has anyone given direction on how to then USE the fact that the "NoRows" property might display something (like "NO DATA" for example), enabling the table row in which the subreport exists to be hidden??

You may use the hide epression in main report to show or hide the subreport. execute the same SQL in main report to determine if the subreport would be empty, that can cleanly determine the subreport should be show or hidden.

You may use the hide epression in main report to show or hide the subreport. execute the same SQL in main report to determine if the subreport would be empty, that can cleanly determine the subreport should be show or hidden.

This is how I ended up solving the issue. Thanks.

Thanks Alex! That is exactly what I needed!!

Another solution would be to use the subreport inside a rectangle. If you set the visibility property of the rectangle depending on the wheather you have data or not, the subreport would not display white space if the subreport is empty.