supress record if subreport is null

Thanks_for_your_help used Ask the Experts™
I am running a simple report where the sub-report can have null values.  I want to suppress the whole record if not data exist in the sub-report and only report the records with data in the sub-report.

Is there a formula that can be used that will tell the record to suppress if no data is transferred from the sub-report.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

If you use an inner join to the table used in the subreport that would be null, records will automatically be excluded.  That's what inner joins do.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Where is the subreport?

Cann you include th subreport tble in the main report?

The sub report is attached to the main report.  The sub report when nill does not show up.  No issue there.  The issue is that the main report always has data and always shows up.  I want the main report record to be excluded if there is no sub report data.

C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Top Expert 2010

I repeat my original answer.

Your subreport selects data based on a field in the main report, correct?  If so, then if you join the subreport table to your main report with an inner join to that field, then when there are no occurrences of that table, nothing will be selected.    And in large datasets, this design is likely to be vastly more efficient, because it would let the database server optimize the query.
Top Expert 2009

The problem with this is that it would potentially increase the number of lines returned from the database where there is more than one row in the sub-report.

This messes with totals, and requires one to start playing with running totals etc to get them to work properly. Also you have to suppress the detail section and start using group footers to do the job.

However, I have generally found that 'detail' sub-reports can be avoided altogether, in these circumstances, with some creative formula and total use.

"Thanks_for_your_help", could you post a few more details about the data structure, and why you went for a sub-report in the first place?
The sub report is needed because the data is from a different data source than the main report.  The main report shows all instances of a particular occurrence.  The sub-report shows which are current instances of this occurrence.   Let's say this is report 1 and it works great and have no issues.

Report 2 needs to show only the occurrence that are current.  

Main report pulls data from data source 1
sub-report pulls data from data source 2

I was thinking last night is to try to reverse the report.  Make the report based on the data source 2 and do a sub-report on data source 1.

I let you know what I find.
Top Expert 2010

And I repeat my answer a third time.  Just because they are two data sources doesn't change my answer.  Isn't there a common field you can join them with?
No there isn't.  The way the main report and the sub-report are joined is that there is 1 field in each data source that match.  The report looks for this match and connects to the main report and sub-report.
Top Expert 2010
"there is 1 field in each data source that match".  Perfect!  Then you can join them in the main report, and eliminate the subreport entirely.
I tried that and couldn't get it to work. I have attached the file.
Top Expert 2010

You need to put the GS_Action table in the main report.
I would like to thank all for your help.  After playing with it until 2am, I finally got it to work.  I did give bad information, I was incorrect in that there is not any fields that match between the 2 databases.  I forgot that in one database you had time/date field.  Database 2 only had a number in the time-date field that needed to be converted into a time/date before it could be compared to database 1.  That was the rub because there was not any fields that matched as is.  

I rewrote from scratch and somehow it actually work without a subreport and got only the data I needed.
Your answer got me thinking and I totally rewrote the report.

1) I was mistaken about sharing data.  In one database the data is classified as numbers and the other as a string.  Once I did the conversion I was able to work around this and get the report working.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial