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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
planoczCommented:
shekhar_shashi  are you still having problems with this report?
0
shekhar_shashiAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.