Subreport visibility

[ReportSmith]

I've seen a post or two regarding this, but not exactly what I need.....

I have a list of employees and their supervisors. I've created a main
report which will show me what I need for the "top level" of employees and
also a subreport for subordinates of the top level employees.

I would like to show the top level and, *if* the top level has subordinates
then display the subreport, otherwise, I don't want to display the sureport -
and even hide the entire footer where the subreport is contained (to save
printed pages and make the whole report cleaner).

I've tried several things and am now experiencing brain meltdown. Any
suggestions are be greatly appreciated.

 

[Marshall Barton]

I've seen a post or two regarding this, but not exactly what I need.....

I have a list of employees and their supervisors. I've created a main
report which will show me what I need for the "top level" of employees and
also a subreport for subordinates of the top level employees.

I would like to show the top level and, *if* the top level has subordinates
then display the subreport, otherwise, I don't want to display the sureport -
and even hide the entire footer where the subreport is contained (to save
printed pages and make the whole report cleaner).

From what you have said, I don't see any reason to use a
subreport, but that may not be significant.

Youcan use a line of code in the foioter section's Format
event procedure:

Cancel = Not Me.subreportcontrol.Report.HasData

 

[ReportSmith]

Thanks - I thought it may be as simple as a line of code. I'll try it out.
Regarding what you said about there not being a need for 2 reports - I think
I know what you mean, but could you expand on what you stated?

 

[Marshall Barton]

Unless you have more going on than you've said so far, the
usual approach is to Join the employee table to the
supervisor table in a query.

Use that query as the record source for a single report and
use Sorting and Grouping (View menu) to group on the
supervisor field. Place the supervisor fields in the group
header section and the employee fields in the detail
section.

If the query uses an INNER JOIN, supervisors with no
employees will not show up in the report. If the query uses
a LEFT JOIN from the supervisor table to the employee table,
then the supervisor data will be in the report along with a
single detail of all Null values (easy enough to hide).

 

[ReportSmith]

Marshall - you got it. I thought the same thing and reformulated the
report.....
I now have a LEFT SELF-JOIN (there is an ID field that shows who the
supervisor is) and am now trying to hide all of the blank detail records.

The thing is that there are up to 4 levels of the hierarchy (so I have 4
left joins) that may break at any level in the chain and I would like to hide
any section that may be blank.

I'm going to try the following (after modifying your original statement):
Cancel = Not Me.CurrentRecord.HasData

Let me know at your convenience and thanks for the assistance.

Unless you have more going on than you've said so far, the
usual approach is to Join the employee table to the
supervisor table in a query.

Use that query as the record source for a single report and
use Sorting and Grouping (View menu) to group on the
supervisor field. Place the supervisor fields in the group
header section and the employee fields in the detail
section.

If the query uses an INNER JOIN, supervisors with no
employees will not show up in the report. If the query uses
a LEFT JOIN from the supervisor table to the employee table,
then the supervisor data will be in the report along with a
single detail of all Null values (easy enough to hide).
--
Marsh
MVP [MS Access]

Thanks - I thought it may be as simple as a line of code. I'll try it out.
Regarding what you said about there not being a need for 2 reports - I think
I know what you mean, but could you expand on what you stated?

 

[ReportSmith]

I guess I should round out my description of what's going on with the report.
It will show employee reviews over the year. I have a crosstab query
(row=name, column=month, value=score) and a very nice-looking report based on
it, but it doesn't go past the first level in the hierarchy.

So, I decided to create another report that will show what's required. I
can't create another crosstab (or use the original one) because of all of the
levels (or can I?)....so I decided to use the 'crosstab report' I currently
have and link it to itself on the supervisor id...but got stuck with the
parent and child link fields...that's why my original question was about
'subreport visibility'.

Again, thanks for the help.

Unless you have more going on than you've said so far, the
usual approach is to Join the employee table to the
supervisor table in a query.

Use that query as the record source for a single report and
use Sorting and Grouping (View menu) to group on the
supervisor field. Place the supervisor fields in the group
header section and the employee fields in the detail
section.

If the query uses an INNER JOIN, supervisors with no
employees will not show up in the report. If the query uses
a LEFT JOIN from the supervisor table to the employee table,
then the supervisor data will be in the report along with a
single detail of all Null values (easy enough to hide).
--
Marsh
MVP [MS Access]

Thanks - I thought it may be as simple as a line of code. I'll try it out.
Regarding what you said about there not being a need for 2 reports - I think
I know what you mean, but could you expand on what you stated?

 

[Marshall Barton]

Crosstabs?? You never said anything about crosstabs. A
crosstab joined to itself sounds pretty hairy. If the one
line of code took care of the subreport issue, I think you
should leave it at that.