none
Accessing SubReport information using ReportItems possible?

    Question

  • I am attempting to dynamically hide a report row in reporting services if the subreport contains no data however I see no way of doing it in SQL 2005 Reporting Services (with SP2).

     

    Right now I have a table that has the following attributes:

     

    1. Header Column with my column titles

    2. One detail row, that has the =Field!fieldname.value underneath all of my column titles

    3. An additional detail row that has merged all the cells of the columns so that it is one row without breaks.  Inside this row is a single subreport that works fine.

     

    My question is, I have set the NoRows property to display "No data found...", however that is wasting a lot of space on the report, therefore I would like to just hide the row alltogether when there is no data from the subreport found.

     

    I can not find a way to do this.  In fact in SQL 2005 when I click on a cell and goto expressions, and enter "

    =ReportItems!", I would expect to see my subreport show up in the intellisense listing, however it does not.  Why doesn't it show up so I can see what types of attributes I can examine to see if one or more rows were generated with the subreport?  This way if no rows were returned I can set the visibility of the 2nd detail row to be hidden instead of visible.

    Tuesday, June 03, 2008 2:50 PM

Answers

  • Assuming that all of the data is coming from a single data source, the easiest thing may be to create a new column in the Parent dataset that will tell you if it has Children.  If you use a LEFT JOIN to the child records and return a count, you can determine if the row should be visible based on that field.

    Wednesday, June 04, 2008 2:24 PM

All replies

  •  

    One way of doing this is using a report expression. I'm not sure whether its the best way though.

    Recently I have used this to hide rows that I dont want to show in one of my reports.

     

    Its something like this ;

     

    iif(ISNOTHING(Fields!FieldsName.Value), " " , Fields!FieldsName.Value)

     

    Tuesday, June 03, 2008 3:19 PM
  • Doing this with Fields is easy.  However my question is about checking if a subreport contains data from the parent report, that is a completely different situation as it doesn't appear you can tell if a subreport returned data from the parent report.

     

    For instance if I could check if the subreport is displaying "No Rows Found..." which is the text I have set to display if the subreport contains no data, then I could check for that, however the following does not work:

     

    1.  =ReportItems!subreportname.value

     

    I am attempting to see if I can atleast attempt to access the subreport value.

     

    This gives an error of expression referenced a non-existing reportitem in the reportitems collection.  For some reason it can't find subreportname, even though it clearly exists inside of my table.

     

    2.  =ReportItems!subreportname.NoRows and I also tried: =ReportItems!subreportname.NoRows.ToString()

     

    I am attempting to access the NoRows property of the subreport to see what it is set to.

     

    This gives an error of "NoRows" is not a member of 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem)

     

    3. =ReportItems("subreportname").ToString

     

    I am attempting to see if I can atleast attempt to access the subreport value.

     

    This gives an error of expression referenced a non-existing reportitem in the reportitems collection.  For some reason it can't find subreportname, even though it clearly exists inside of my table.

     

    I give up, how do you reference subreport values from the parent report so I can inspect it to see if it retrieved data?

    Wednesday, June 04, 2008 2:04 PM
  • Assuming that all of the data is coming from a single data source, the easiest thing may be to create a new column in the Parent dataset that will tell you if it has Children.  If you use a LEFT JOIN to the child records and return a count, you can determine if the row should be visible based on that field.

    Wednesday, June 04, 2008 2:24 PM
  • Very good point, thats how I'll do it.  I completely forgot about that way of thinking.  I did something like that well over a year ago and didn't remember it until now.  Thanks!

     

    Wednesday, June 04, 2008 7:39 PM
  • That is not a bad solution, although you have to make another call to the database. So if performance is an issue, you want to have as little calls to the database as possible.

    I just wish I could just do rowCount on the items from subreport.

    Wednesday, November 23, 2011 4:18 PM
  • My subreport data is coming from a different datasource. What is the solution in this case?

    Thanks.

    Monday, August 19, 2013 5:57 AM
  • I also have this issue, subreport data is comming from diferent datasource... please help

    Zubair Afridi | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    Saturday, August 31, 2013 10:39 AM
  • Hi Zubair,

    I could not find a solution for this issue. There was no way that I could access my subreport value in main report as it was coming from different datasource. Hope Microsoft addresses this issue in newer versions of SSRS.


    Rohit Pitre

    Sunday, September 01, 2013 3:06 PM