Showing Totals from Subreport on Main Report

[Darrell]

I have a main report with 3 subreports and I want to display totals from
each subreport and grand totals in the report header of the main report.
What is the best way to do this?

Allen Browne posted the following response to a user back in 2003
offering a method. But, I can't get the
[rptInvoiceDogs].[Report].[HasData]" (adjusted to my specific
report/control names, of course) to be recognized.

"... No code is needed. At design time, set the Control Source
of the text box to something like this:
=IIf([rptInvoiceDogs].[Report].[HasData],
Nz([rptInvoiceDogs].[Report]![txtSumSalesPrice],0), 0)
+ IIf([rptInvoiceCharges].[Report].[HasData],
Nz([rptInvoiceCharges].[Report]![txtSumTotCharge],0), 0)
+ IIf([InvoiceAdjustments].[Report].[HasData],
Nz([InvoiceAdjustments].[Report]![txtAdjTot],0), 0)"

Any help is greatly appreciated.

Darrell

 

[Allen Browne]

The core ideaa here are:
a) to bring back the totals from the subreports and
b) to accumulate the totals to show at the end of the main report.

For (a), see:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html
It explains the expression you referred to.

Your 3 subreports could be in different sections of the main report? If so,
it' is important to have the subtotal text box on the *same* section as the
subreport itself. Perhaps you could start with 3 text boxes - one showing
the total for each subreport.

Once you are correctly showing the subtotal for each subreport, you can
start working on (b). This requires another text box, with its ControlSource
set to the name or your subtotal text box, and Running Sum set to accumulate
the total. Example:
Control Source =[txtSubTotal]
Running Sum Over All
You should now see the correct subtotal, and the accumulating total
througout the report.

Once you have that working, just place a text box in the Report Footer
section, and sum the values in the 3 accumulated totals. The ControlSource
will be something like this:
=Nz([a],0) + Nz(,0) + Nz([c],0)
where a, b, and c represent the names of the 3 boxes that accumulate your
subtotals.

Once that's working, you can set the Visible property of the other text
boxes to No so you don't see them if you wish.

 

[Darrell]

The core ideaa here are:
a) to bring back the totals from the subreports and
b) to accumulate the totals to show at the end of the main report.

For (a), see:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html
It explains the expression you referred to.

Your 3 subreports could be in different sections of the main report? If
so, it' is important to have the subtotal text box on the *same* section
as the subreport itself. Perhaps you could start with 3 text boxes - one
showing the total for each subreport.

Once you are correctly showing the subtotal for each subreport, you can
start working on (b). This requires another text box, with its
ControlSource set to the name or your subtotal text box, and Running Sum
set to accumulate the total. Example:
Control Source =[txtSubTotal]
Running Sum Over All
You should now see the correct subtotal, and the accumulating total
througout the report.

Once you have that working, just place a text box in the Report Footer
section, and sum the values in the 3 accumulated totals. The
ControlSource will be something like this:
=Nz([a],0) + Nz(,0) + Nz([c],0)
where a, b, and c represent the names of the 3 boxes that accumulate
your subtotals.

Once that's working, you can set the Visible property of the other text
boxes to No so you don't see them if you wish.

Allen,

Thank you very much! I am doing this as contract work on the side, so
won't be able to implement this until this evening, but I assure you I
will work on it then, and will post back.

One point for clarification: all 3 of my subreports are in the detail
section of the main report, so... if I understand correctly, I need to
place the subtotal text box(es) for the 3 subreports in the detail
section (of the main report). Correct?

Then the box for the total of all the subreports would go in the main
report header (in this case, rather than footer), with its control
source set to add each of the boxes in the detail section.

Thank you again for your response. It is greatly appreciated.

Darrell

 

[Allen Browne]

Replies in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

One point for clarification: all 3 of my subreports are in the detail
section of the main report, so... if I understand correctly, I need to
place the subtotal text box(es) for the 3 subreports in the detail section
(of the main report). Correct?
Yes.

Then the box for the total of all the subreports would go in the main
report header (in this case, rather than footer), with its control source
set to add each of the boxes in the detail section.

It will work in the Report Footer. You would need to test in in
Report_Header. There might be a timing issue, where the total is zilch at
the time when the report header is formatted, so you don't get the answer
you need.

Access does do a double-pass, so you may be able to solve that by placing
the total in the report footer in a text box named (say) txtTotal, and then
place a text box in the Report Header that reads the value from the footer,
i.e.:
=[txtTotal]

 

[Darrell]

The core ideaa here are:
a) to bring back the totals from the subreports and
b) to accumulate the totals to show at the end of the main report.

For (a), see:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html
It explains the expression you referred to.

Your 3 subreports could be in different sections of the main report? If
so, it' is important to have the subtotal text box on the *same* section
as the subreport itself. Perhaps you could start with 3 text boxes - one
showing the total for each subreport.

Once you are correctly showing the subtotal for each subreport, you can
start working on (b). This requires another text box, with its
ControlSource set to the name or your subtotal text box, and Running Sum
set to accumulate the total. Example:
Control Source =[txtSubTotal]
Running Sum Over All
You should now see the correct subtotal, and the accumulating total
througout the report.

Once you have that working, just place a text box in the Report Footer
section, and sum the values in the 3 accumulated totals. The
ControlSource will be something like this:
=Nz([a],0) + Nz(,0) + Nz([c],0)
where a, b, and c represent the names of the 3 boxes that accumulate
your subtotals.

Once that's working, you can set the Visible property of the other text
boxes to No so you don't see them if you wish.

Thanks again, Allen. Step 1 is working beautifully. Apparently the Name
AutoCorrect was the problem since I had tried it previously with both
the subreport name and the subreport's control name with the same
result. This time, it worked immediately. I have heard echoes of
problems with this feature before. Your explanation of its origin and
bugs has been extremely helpful.

I've just spent some time on step 2 and am a little confused about what
the Running Sum would be referring to. My efforts have been a bit
cursory so far, so don't spend a lot of time trying to address a vague
confusion. I'll post again with more definitive questions (or a
statement of success) when I've given it fairer attention.

Thank you again for your help.

Darrell

 

[Darrell]

Replies in-line

Thanks Allen. I am continually amazed by the generosity of folks like
you who donate large amounts of time to help amateurs such as myself.
Anyway, I digress.

FYI, the process of adding the contents of the subtotal text boxes in
the report header seems to work just fine. Hallelujah! No indirect route
of a footer text box referenced in turn by the header necessary, apparently.

Now, as indicated in my other post this morning, I'm still a little
unclear on the running sum concept referenced in your first post. I'll
spend some more time on that this evening and post back again.

Thank you again,

Darrell