Excluding parent report data/tuple if subreport has no data.

Hi, I have a question that bears a resemblance to this previous thread

http://groups.google.com.au/group/mi...97047fa63029ec

My scenario is this:

I have a report which lists a series of items in catergories, each of
these items can have several subreports which are bound to some strict
data constraints (date filters, $ value filters - also the subreports
data sources are not all the same). For instance, the setup is like
this

Category 1
---- Subreport 1
-----Subreport 2
-----Subreport 3
-----Subreport 4
Category 3
---- Subreport 1
-----Subreport 2
-----Subreport 3
-----Subreport 4

... and so on throughout numerous categories.

What I want to do is hide, or make the category disappear from the
resultant report, if there is no data in the ANY of the subreports
under that category. ie. if the category has no useful subreport data
then we want to ignore it.

Been racking my brains for a few hours on how to do this in a simple
way. Any suggestions?

Regards
Darragh

Subreports are almost always linked by child/master properties. You should be
able to create totals queries similar to your subreport record sources that
Group By the linking field and count the number of returned records that
might show in your subreport. Add these totals queries to the record source
of your main report. This will allow your main report to understand the
number of records (or lack of) that would display in your subreports. Use
this to filter your main report.
--
Duane Hookom
Microsoft Access MVP


"Daz" wrote:

Hi, I have a question that bears a resemblance to this previous thread

http://groups.google.com.au/group/mi...97047fa63029ec

My scenario is this:

I have a report which lists a series of items in catergories, each of
these items can have several subreports which are bound to some strict
data constraints (date filters, $ value filters - also the subreports
data sources are not all the same). For instance, the setup is like
this

Category 1
---- Subreport 1
-----Subreport 2
-----Subreport 3
-----Subreport 4
Category 3
---- Subreport 1
-----Subreport 2
-----Subreport 3
-----Subreport 4

... and so on throughout numerous categories.

What I want to do is hide, or make the category disappear from the
resultant report, if there is no data in the ANY of the subreports
under that category. ie. if the category has no useful subreport data
then we want to ignore it.

Been racking my brains for a few hours on how to do this in a simple
way. Any suggestions?

Regards
Darragh

Duane

Thanks for your response.

I utilised a variation of what you suggest. For those interested I did
the following.

Created count queries for the recordsources of the subreport.

Gathered the number of instances a parent property occured in the
count of the subreport recordsource. If count greater than 0, then
include that parent property constraint in the parent reports
recordsource.

A poor explanation I know, I will endevour to write up a better one
and post to the access section of my personal blog - http://www.darraghmurray.com

Regards
Darragh

On Mar 12, 4:04 am, Duane Hookom
wrote:
Subreports are almost always linked by child/master properties. You should be
able to create totals queries similar to your subreport record sources that
Group By the linking field and count the number of returned records that
might show in your subreport. Add these totals queries to the record source
of your main report. This will allow your main report to understand the
number of records (or lack of) that would display in your subreports. Use
this to filter your main report.
--
Duane Hookom
Microsoft Access MVP

"Daz" wrote:
Hi, I have a question that bears a resemblance to this previous thread

http://groups.google.com.au/group/mi...ss.reports/bro...

My scenario is this:

I have a report which lists a series of items in catergories, each of
these items can have several subreports which are bound to some strict
data constraints (date filters, $ value filters - also the subreports
data sources are not all the same). For instance, the setup is like
this

Category 1
---- Subreport 1
-----Subreport 2
-----Subreport 3
-----Subreport 4
Category 3
---- Subreport 1
-----Subreport 2
-----Subreport 3
-----Subreport 4

... and so on throughout numerous categories.

What I want to do is hide, or make the category disappear from the
resultant report, if there is no data in the ANY of the subreports
under that category. ie. if the category has no useful subreport data
then we want to ignore it.

Been racking my brains for a few hours on how to do this in a simple
way. Any suggestions?

Regards
Darragh