none
How to suppress the subreport when there is no data in it?

    Question

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

     

    Thanks.

    Monday, January 30, 2006 4:50 PM

Answers

  • 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

    Tuesday, January 31, 2006 7:58 AM
    Owner
  • No, I did not. Instead I have 'No data available' displayed when there is no data appearing from the subreport.
    Tuesday, January 23, 2007 3:38 PM
  • 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 Block
    select 'abc' as dummy

     

    -- Robert

     

     

    Monday, October 08, 2007 12:20 AM
    Owner

All replies

  • 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

    Tuesday, January 31, 2006 7:58 AM
    Owner
  • 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

    Wednesday, February 15, 2006 3:21 PM
  • Ditto -- What is the expression to suppress the table row that contains the subreport when the subreport has no data?

    Thanks,

    Pete

    Tuesday, June 20, 2006 8:06 PM
  • Can you explain your scenario in more detail please?

    The closest you can get right now is 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

    Thursday, June 22, 2006 12:49 AM
    Owner
  • 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 same main 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 refers to 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 data region, 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

    Thursday, June 22, 2006 4:44 PM
  • Hi Andrea,

    Did you find the solution to hide the row?

    i have same problem..

    Thanks alot..

    Friday, January 12, 2007 1:44 PM
  • No, I did not. Instead I have 'No data available' displayed when there is no data appearing from the subreport.
    Tuesday, January 23, 2007 3:38 PM
  • 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
    Friday, October 05, 2007 9:56 PM
  • 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 Block
    select 'abc' as dummy

     

    -- Robert

     

     

    Monday, October 08, 2007 12:20 AM
    Owner
  • 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.

    Wednesday, May 21, 2008 5:59 PM
  • 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

     

    • Proposed as answer by Muhidul Tuesday, March 26, 2013 10:50 PM
    Thursday, July 08, 2010 10:20 AM
  • 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

    Thursday, July 22, 2010 8:34 PM
  • 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

    Thursday, July 22, 2010 8:35 PM
  • 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.

    • Proposed as answer by Think Bees Friday, October 28, 2011 4:48 PM
    Thursday, October 21, 2010 3:50 PM
  • 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?

     

    Thursday, October 21, 2010 6:06 PM
  • 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.
    Thursday, October 21, 2010 7:16 PM
  • is this feature available in 2005?

     

    Friday, October 22, 2010 1:28 PM
  • In VS2005

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

     

    Wednesday, December 01, 2010 5:43 PM
  • 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
    Friday, December 03, 2010 9:45 PM
  • 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??
    Friday, September 30, 2011 7:42 PM
  • 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.
    • Proposed as answer by Xp8013 Monday, October 17, 2011 3:50 PM
    • Unproposed as answer by Xp8013 Monday, October 17, 2011 3:50 PM
    • Proposed as answer by Xp8013 Monday, October 17, 2011 3:51 PM
    Thursday, October 06, 2011 7:00 PM
  • This is how I ended up solving the issue.  Thanks.
    Monday, October 17, 2011 3:52 PM
  • Thanks Alex! That is exactly what I needed!! 
    Friday, October 28, 2011 4:54 PM
  • 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.

    Thursday, May 17, 2012 9:40 AM
  • thank you for tricks. it worked for my report.
    Wednesday, November 07, 2012 7:05 PM