Grand Totals In Report Footer for Separate Groups

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am having trouble getting a Grand Total in the footer of my report to
calculate correctly.

I tried a Dsum expression but it was verrrryyyy sloooowww! It looked like
this (one for each Group):

=DSum(" [ORDER_TOTAL]
","SalesJournal_UnionInfo","[ACTIVITYGROUP]='OPENORDER'")

=DSum(" [ORDER_TOTAL] ","SalesJournal_UnionInfo","[ACTIVITYGROUP]='INVOICED'")

I believe it was working correctly but way too slow. So I'm looking for
alternatives.

My report is based off of the query SalesJournal_UnionInfo. First the
records on the report are grouped by SALESREP, then grouped by ACTIVITYGROUP
which is either "INVOICED" or "OPENORDER". In the ACTIVITYGROUP footer I have
the [ORDER_TOTAL] field set to =Sum(ORDER_TOTAL]) not as a running sum and
it's name is SUBTOTAL_CALC. This gives me the SUBTOTAL calculation per each
activity per each SALESREP.

How do I get the separate GRAND TOTALS of each ACTIVITY SUBTOTAL into the
Report Footer? (without using DSum?!)

Thanks very much for your help!
 
laknight said:
I am having trouble getting a Grand Total in the footer of my report to
calculate correctly.

I tried a Dsum expression but it was verrrryyyy sloooowww! It looked like
this (one for each Group):

=DSum(" [ORDER_TOTAL]
","SalesJournal_UnionInfo","[ACTIVITYGROUP]='OPENORDER'")

=DSum(" [ORDER_TOTAL] ","SalesJournal_UnionInfo","[ACTIVITYGROUP]='INVOICED'")

I believe it was working correctly but way too slow. So I'm looking for
alternatives.

My report is based off of the query SalesJournal_UnionInfo. First the
records on the report are grouped by SALESREP, then grouped by ACTIVITYGROUP
which is either "INVOICED" or "OPENORDER". In the ACTIVITYGROUP footer I have
the [ORDER_TOTAL] field set to =Sum(ORDER_TOTAL]) not as a running sum and
it's name is SUBTOTAL_CALC. This gives me the SUBTOTAL calculation per each
activity per each SALESREP.

How do I get the separate GRAND TOTALS of each ACTIVITY SUBTOTAL into the
Report Footer? (without using DSum?!)
Click to expand...



Create a new query based on the report's query that groups
and calculates the desired totals. Then create a new report
based on the new query and drop it in the footer of your
existing report.
 
I guess I left out one very important piece of info: I have a form setup as
user input for date range to specify a limit to the results of the report. If
I setup a separate query with a second subreport, my grand totals calcs will
not be based off of the filtered info.


Marshall Barton said:
laknight said:
I am having trouble getting a Grand Total in the footer of my report to
calculate correctly.

I tried a Dsum expression but it was verrrryyyy sloooowww! It looked like
this (one for each Group):

=DSum(" [ORDER_TOTAL]
","SalesJournal_UnionInfo","[ACTIVITYGROUP]='OPENORDER'")

=DSum(" [ORDER_TOTAL] ","SalesJournal_UnionInfo","[ACTIVITYGROUP]='INVOICED'")

I believe it was working correctly but way too slow. So I'm looking for
alternatives.

My report is based off of the query SalesJournal_UnionInfo. First the
records on the report are grouped by SALESREP, then grouped by ACTIVITYGROUP
which is either "INVOICED" or "OPENORDER". In the ACTIVITYGROUP footer I have
the [ORDER_TOTAL] field set to =Sum(ORDER_TOTAL]) not as a running sum and
it's name is SUBTOTAL_CALC. This gives me the SUBTOTAL calculation per each
activity per each SALESREP.

How do I get the separate GRAND TOTALS of each ACTIVITY SUBTOTAL into the
Report Footer? (without using DSum?!)
Click to expand...



Create a new query based on the report's query that groups
and calculates the desired totals. Then create a new report
based on the new query and drop it in the footer of your
existing report.
Click to expand...
 
That's why I said to use the report's record source query as
the basis for the summary totals query.

If you were applying the date range filter via the
OpenReport method's WhereCondition argument, you will have
to forgo that approach and use references to the text boxes
directly in the main report query's criteria.
--
Marsh
MVP [MS Access]

I guess I left out one very important piece of info: I have a form setup as
user input for date range to specify a limit to the results of the report. If
I setup a separate query with a second subreport, my grand totals calcs will
not be based off of the filtered info.


Marshall Barton said:
Create a new query based on the report's query that groups
and calculates the desired totals. Then create a new report
based on the new query and drop it in the footer of your
existing report.
Click to expand...
laknight said:
I am having trouble getting a Grand Total in the footer of my report to
calculate correctly.

I tried a Dsum expression but it was verrrryyyy sloooowww! It looked like
this (one for each Group):

=DSum(" [ORDER_TOTAL]
","SalesJournal_UnionInfo","[ACTIVITYGROUP]='OPENORDER'")

=DSum(" [ORDER_TOTAL] ","SalesJournal_UnionInfo","[ACTIVITYGROUP]='INVOICED'")

I believe it was working correctly but way too slow. So I'm looking for
alternatives.

My report is based off of the query SalesJournal_UnionInfo. First the
records on the report are grouped by SALESREP, then grouped by ACTIVITYGROUP
which is either "INVOICED" or "OPENORDER". In the ACTIVITYGROUP footer I have
the [ORDER_TOTAL] field set to =Sum(ORDER_TOTAL]) not as a running sum and
it's name is SUBTOTAL_CALC. This gives me the SUBTOTAL calculation per each
activity per each SALESREP.

How do I get the separate GRAND TOTALS of each ACTIVITY SUBTOTAL into the
Report Footer? (without using DSum?!)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top