SQL Server Reporting Services

I have built an SSRS 2008 report that has a subreport. The subreport returns zero or one record. I want to hide the subreport if the number of rows in the subreport is zero. How do I accomplish that?

Apparently this is a very common SSRS problem and I could find several postings on the internet on the exact same problem. Non of of the solutions worked for me. I want to set the visibility of the subreport to an expression that will evaluate to true only if the count of records in the subreport is more than zero.

Since the the subreport cell is in the outer report, all parameters, report fields etc. in the expression builder are from the the outer report.  That prevents me from checking the count of records in the subreport.

I'd like to do this without double querying the data the inner report queries.
LVL 3
shekhar_shashiAsked:
Who is Participating?
 
planoczCommented:
On that row go to the far left and click. then on the properties window goto the visisbliy hidden box and add .... =IIF(CountRows() = 0, True, False)
0
 
planoczCommented:
heres is easist way that I know.
In your sub-report place this code in the Table Visility Hidden property
=CInt(CountRows())=0
The code is at work so I might not have the correct code. I can check in the morning and get back with you.
This will see that there is no data in the table and the sub report will not show.
0
 
planoczCommented:
Here the correct code....
=CountRows() = 0
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
shekhar_shashiAuthor Commented:
Thanks Experts. But this is slightly different from what I am trying to accomplish here. The child report (subreport) resides in a table cell of the parent report. If the count of records is zero in the subreport, the child report will hide itself. That is perfectly fine. But how do I hide (or remove) the row in the parent row if the child report has zero row count? Even if the subreport hides itself, the blank row will still show in my parent report. So that defeats the purpose of the hiding the subreport.

Any thoughts/sugestions?
0
 
planoczCommented:
If you are using a table then right click on the very far left of the row and this will hightlight the row. Then go to the properties window and put in the Visiblity hidden box....
=Trim(MycellName.value)=""
This will hide the whole row
0
 
shekhar_shashiAuthor Commented:
Thanks. Initially I thought this solution would work but I ran into a problem.

My subreport is dragged and dropped into a table cell in the parent report. When I drag an drop the report into the cell, the textbox cell reference is removed from the .rdl file of the parent report. How do I access 'MycellName' in the expression?

I tried to reference the subreport name by its name and got a runtime error that said the control does not exist in the parent report.

So I am not sure how to apply the expression.


0
 
planoczCommented:
Ok is your sub report one column wide or the whole row as one cell?
0
 
shekhar_shashiAuthor Commented:
The cell where the subreport is dropped combines three cells. And that row has one additional cell.
0
 
planoczCommented:
shekhar_shashi  are you still having problems with this report?
0
 
shekhar_shashiAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.