Problems totalling subreport data on main report (A-2002)

  • Thread starter Thread starter Lewis Moseley
  • Start date Start date
L

Lewis Moseley

I have not been able to find any decent source for info on using
subreports and especially summing subreport data on the mail report.
This is giving me fits! Maybe someone can help.

Tables:

-Employees
-Clients (includes first and last date of that clients business year,
also names Employee responsible for the account)
-Client line item information


Report:

Employee (group by)
Client
Client details on a subreport (by client's business year)
Count and total by client (taken from subreport)
Count and total by Employee (DOES NOT WORK)
Count and total for report (DOES NOT WORK)

To explain a little more: The client details are on a subreport
because the "business year" of each client is different and thus the
date range of the detail items varies by client.

The main report is for ALL Clients, grouped by Employee. The Detail
section includes the Subreport, which shows the activity of that
client within its own current business year. The Subreport footer
counts the line entries and totals a numeric field. The Subreport can
contain from ZERO line items to about 50 items.

I transferred the Count and Sum to the main report. I first tried
just using a fully qualified reference to the count and sum fields on
the subreport. The problem is that the Subreport may contain ZERO
line items and this causes the Count and Sum fields I transferred to
the main report to #ERROR.

I solved this, using a technique from the MS Knowledgebase:

=IIf([New Report #1 SubReport].[Report].[HasData]=True,[Report]![New
Report #1 SubReport]![ItemCount],0)

So now, the per-client count field and the total field on the main
report work correctly, even if the Subreport has no data. Everything
works to this point now.

But I also need to process the Cound and Total of all Clients by
Employee (the grouping level) and also as a Grand Total and Count for
the entire report.

I can't do the usual thing of just summing up the underlying data, as
it is on a Subreport and, again, because it #ERRORs out if the
Subreport is empty.

And I can't sum up the calculated field that I placed on the main
report (using the expression above).

QUESTION:

How do I get my Group Totals and Grand Totals in this situation?

All help appreciated.
 
Lewis said:
I have not been able to find any decent source for info on using
subreports and especially summing subreport data on the mail report.
This is giving me fits! Maybe someone can help.

Tables:

-Employees
-Clients (includes first and last date of that clients business year,
also names Employee responsible for the account)
-Client line item information


Report:

Employee (group by)
Client
Client details on a subreport (by client's business year)
Count and total by client (taken from subreport)
Count and total by Employee (DOES NOT WORK)
Count and total for report (DOES NOT WORK)

To explain a little more: The client details are on a subreport
because the "business year" of each client is different and thus the
date range of the detail items varies by client.

The main report is for ALL Clients, grouped by Employee. The Detail
section includes the Subreport, which shows the activity of that
client within its own current business year. The Subreport footer
counts the line entries and totals a numeric field. The Subreport can
contain from ZERO line items to about 50 items.

I transferred the Count and Sum to the main report. I first tried
just using a fully qualified reference to the count and sum fields on
the subreport. The problem is that the Subreport may contain ZERO
line items and this causes the Count and Sum fields I transferred to
the main report to #ERROR.

I solved this, using a technique from the MS Knowledgebase:

=IIf([New Report #1 SubReport].[Report].[HasData]=True,[Report]![New
Report #1 SubReport]![ItemCount],0)

So now, the per-client count field and the total field on the main
report work correctly, even if the Subreport has no data. Everything
works to this point now.

But I also need to process the Cound and Total of all Clients by
Employee (the grouping level) and also as a Grand Total and Count for
the entire report.

I can't do the usual thing of just summing up the underlying data, as
it is on a Subreport and, again, because it #ERRORs out if the
Subreport is empty.

And I can't sum up the calculated field that I placed on the main
report (using the expression above).

QUESTION:

How do I get my Group Totals and Grand Totals in this situation?
Click to expand...


Add an invisible(?) text box named txtRunGrpTotal to the
section with the subreport and set its controls source
expression to the text box with the subreport total. Then
set it's RunningSum property to Over Group.

The group footer can display the total by using the
expression =txtRunGrpTotal

You can use a similar arrangement to show a grand total in
the report footer.
 
Marshall, THANK YOU very much! It works!

I have been doing Access work for YEARS and never ran across that
capability. Report creation, especially when it comes to the more
esoteric features, is one of the most poorly documented aspects of
Access.



Lewis said:
I have not been able to find any decent source for info on using
subreports and especially summing subreport data on the mail report.
This is giving me fits! Maybe someone can help.

Tables:

-Employees
-Clients (includes first and last date of that clients business year,
also names Employee responsible for the account)
-Client line item information


Report:

Employee (group by)
Client
Client details on a subreport (by client's business year)
Count and total by client (taken from subreport)
Count and total by Employee (DOES NOT WORK)
Count and total for report (DOES NOT WORK)

To explain a little more: The client details are on a subreport
because the "business year" of each client is different and thus the
date range of the detail items varies by client.

The main report is for ALL Clients, grouped by Employee. The Detail
section includes the Subreport, which shows the activity of that
client within its own current business year. The Subreport footer
counts the line entries and totals a numeric field. The Subreport can
contain from ZERO line items to about 50 items.

I transferred the Count and Sum to the main report. I first tried
just using a fully qualified reference to the count and sum fields on
the subreport. The problem is that the Subreport may contain ZERO
line items and this causes the Count and Sum fields I transferred to
the main report to #ERROR.

I solved this, using a technique from the MS Knowledgebase:

=IIf([New Report #1 SubReport].[Report].[HasData]=True,[Report]![New
Report #1 SubReport]![ItemCount],0)

So now, the per-client count field and the total field on the main
report work correctly, even if the Subreport has no data. Everything
works to this point now.

But I also need to process the Cound and Total of all Clients by
Employee (the grouping level) and also as a Grand Total and Count for
the entire report.

I can't do the usual thing of just summing up the underlying data, as
it is on a Subreport and, again, because it #ERRORs out if the
Subreport is empty.

And I can't sum up the calculated field that I placed on the main
report (using the expression above).

QUESTION:

How do I get my Group Totals and Grand Totals in this situation?
Click to expand...


Add an invisible(?) text box named txtRunGrpTotal to the
section with the subreport and set its controls source
expression to the text box with the subreport total. Then
set it's RunningSum property to Over Group.

The group footer can display the total by using the
expression =txtRunGrpTotal

You can use a similar arrangement to show a grand total in
the report footer.
Click to expand...
 
Back
Top