Unable to get Grand Totals onto Main Report from Sub Reports

[TammyT]

I have a report based on a large table, so I have to pull the data in via subreports (can't have more than 255 data fields on a report).

The subreports work fine when opened on their own - I get the correct totals.

However, when I place them into the main report's detail (or any!) section, & then in the Report Footer try to get the Grand Totals, I just get the summary totals from the last record pulled.

I'll try to give an example:

rptReviewSubReport1 has 180 data fields that are getting a sum of all records where the value of the field = 1; then there are 2 other fields that total the 1's, 90 in each field - had to do it in 2 fields because the expression builder wouldn't do all 180 in 1 Control Source!

An example of 1 of the fields is:
fldRR001Y whose Control Source = =Abs(Sum([RR001]="1"))

All the other fields follow suit, just the "001" part changes.

The total fields look like this:

fldSumOfYesSub1 whose control source is =[fldRR010Y]+[fldRR007Y]+[fldRR008Y] (and so on, for 90 of the fields)

The 2nd subreport is just like the first one, except it's totalling the records where the value = 3

When I put a Report Footer in the subreports & add a field for the total 1's or the total 3's, I get the correct total of all the 1's for all of the records, same with the 3's

However: when I put the 2 subreports into the main report (again, no matter what section), & then in the Report Footer of the main report I try to get those same totals as in the subreport report footers, I just get the total 1's & 3's from the last record instead of a grand total.

I hope this explanation makes sense to someone!! I'm really stuck here!

Thanks.

+Tammy

 

[THWatson]

Tammy
Try this approach...
1. in the Report Footer for each subreport, put a text box that pulls the Total you want from that subreport.

2. In the main report, put a text box for each of the subreports - the first referencing that total box from Subreport1, the second referencing that total box from Subreport2, and so on.

3. In case that any subreport has the potential of being empty, you need to test for that using the HasData property.

For example, here's a text box from a main report of mine that references [Text35] on "rptAcctSummarySub". If there is a value it shows; if there is no value a 0 shows.
=IIf([rptAcctSummarySub].[Report].[HasData]=-1,[rptAcctSummarySub].[Report]![Text35],0)

Hope that helps.

Tom

 

[TammyT]

I did all of that, except the empty part (because it would never be). It just gives me the info on the last record. Wierd, huh?

 

[THWatson]

Tammy
Is it possible it's a matter of how you reference the Total text boxes from each of the subreports' Report Footers?

Or...are you referencing the Total text boxes in the Detail section, and then trying to Sum those particular controls in the main Report Footer?

For example...
Let's say the Totals boxes from the subreports are [Subreport1Total] and [Subreport2Total]
Then, in the Detail section of the main report, let's say you have text boxes for those, and they are called [txtSub1] and [txtSub2]

In the main Report Footer, where you want to sum the two in one text box, are you using
= [Subreport1Total] + [Subreport2Total]
or
= [txtSub1] + [txtSub2]

The first should work. The second won't.

If you can get the subreport totals into the Detail section of the main report, then you should be able to sum them.

Tom

 

[TammyT]

Okay, I tried to follow what you wrote.

I made sure that the "grand" total fields in each subreport were place into the subreport's Report Footer.

Then, in the Detail section of the main report, I created 2 fields & made their Control Sources as follows:

txtSubYesTotal (name of field)
=rptSummarySubReport1.Report!fldTotalYes (Control Source)

txtSubNATotal (name of field)
=rptSummarySubReport2.Report!fldTotalNA (Control Source)


Then, in the main report Report Footer - you lost me! I'm not adding those 2 totals; I'm trying to get a total of all Yes answers in all records & a separate total of all NA answers in all records, so wouldn't want to add them.

I just can't get a Grand Total - I only get a total of all Yes answers/NA answers for the last record.

Does this make sense?

 

[THWatson]

Tammy
In your first post you wrote:
The subreports work fine when opened on their own - I get the correct totals. Therefore, I assume that when these subreports are pulled into the main report, their data is accurate.

But maybe I'm missing something. Let's have another look.

Is this the situation?
In Subreport1, there is a text box in its Report Footer that provides the sum of all "Yes." These are the 1's.
In Subreport2, there is a text box in its Report Footer that provides the sum of all "N/A." These are the 3's.

If this is the situation, then the Report Footer in each subreport is giving you the total you need to pull into the main report.

And I assume this to be the situation because of what you said in your first post: When I put a Report Footer in the subreports & add a field for the total 1's or the total 3's, I get the correct total of all the 1's for all of the records, same with the 3's

If these assumptions aren't correct, help me understand what I'm missing.

I'm trying to help...not lose you!

Tom

 

[TammyT]

Your assumption is correct; you are helping - thanks!!

It's very wierd - even when I show the subreport "raw" (detail) data in the main report, the final/Grand total is just a replication of the totals from the last record pulled.

We'll have to pick up on this on Monday - my work day is over & I have a son to get from daycare!

I'll check back in again on 10/3.

Thanks again!

+Tammy