Rephrase: How can I get total for detail subreports in group level header?

[Max Moor]

Hi Again,
(I decided I should rephrase my subject)

I have a report with two subreports in it's detail section.
These each report on sales in their areas. I have a textbox in
the header for the group level above the detail with the following, rather
messy, control source:

=IIf(Reports!rptMain!rsub1.Report.HasData, Reports!rptMain!rsub1!txtSum,0)
+ IIf(Reports!rptMain!rsub2.Report.HasData, Reports!rptMain!rsub2!txtSum,0)

This makes sure each subreport has data, then, if so, gets the
total out of a textbox on each.

This works on every group except the first one on the report.
For that first group, the total in the group header is always 0.
I put a text box in the detail section with the same control source, and it
totals fine, even on the first group. The header just doesn't get it.

I assume this has something to do with when formatting and such
is done on the first group of a report, but I don't know what to
do about it. Can anyone help me make this work?

- Max

 

[Marshall Barton]

Hi Again,
(I decided I should rephrase my subject)

I have a report with two subreports in it's detail section.
These each report on sales in their areas. I have a textbox in
the header for the group level above the detail with the following, rather
messy, control source:

=IIf(Reports!rptMain!rsub1.Report.HasData, Reports!rptMain!rsub1!txtSum,0)
+ IIf(Reports!rptMain!rsub2.Report.HasData, Reports!rptMain!rsub2!txtSum,0)

This makes sure each subreport has data, then, if so, gets the
total out of a textbox on each.

This works on every group except the first one on the report.
For that first group, the total in the group header is always 0.
I put a text box in the detail section with the same control source, and it
totals fine, even on the first group. The header just doesn't get it.

I assume this has something to do with when formatting and such
is done on the first group of a report, but I don't know what to
do about it. Can anyone help me make this work?

I think it's because you're trying to use a value before it
has been calculated. I would expect that the results you
see now are for the previous detail, not the one in the
group's detail.

Do you get the correct values if you put the text box in the
group footer?

Actually, I don't see what good it is to have the text box
calculation in either a group header or footer unless each
group only has one detail (in which case, why have a group).

Maybe you could get more effective help if you explained
more about the report's data source and how you want the
data values to appear in the formatted report.

 

[Max Moor]

I think it's because you're trying to use a value before it
has been calculated. I would expect that the results you
see now are for the previous detail, not the one in the
group's detail.

Do you get the correct values if you put the text box in the
group footer?

Actually, I don't see what good it is to have the text box
calculation in either a group header or footer unless each
group only has one detail (in which case, why have a group).

Maybe you could get more effective help if you explained
more about the report's data source and how you want the
data values to appear in the formatted report.

Hi Marsh,
The group level is all the purchases for a family. On my other
reports, I have the family name and their ID number in the header along
with total counts or value totals shown on the far right. With these I
use a "sum over group" text box. It doesn't seem to matter whether I
put it in the header or footer, I get the right value. This said, I
stuvk it in the header to save space in the report, especially for
summary reports, wherre the name and whatever total is all I want
anyway.

On the report giving me trouble, I'm trying to repeat this same
style. The main difference with this report is that the detail section
contains two subreports, each listing purchases by this family in two
fairly dissimilar areas. Each subreport contains a textbox I can read
out, that gives me the total for it. If I want the total for the two
together in the detail section or the footer, no problem. It's just if
I put it in the header that the total is wrong.

Normally, Id agree that I'm just trying to use it before it's
calulated when it's in the header. What I'm seeing though, is that I do
get the right totals for each family, even witht the calculation done in
the header, for all but the very first group on the report. The family,
"Able," for example, is wrong, but "Browning" all the way through to the
end is correct. I just don't get what could be different about the
first group that make this not work. (I'd love to make it work so all
me reports look the same)

TTFN

 

[Marshall Barton]

Marshall Barton wrote

Hi Marsh,
The group level is all the purchases for a family. On my other
reports, I have the family name and their ID number in the header along
with total counts or value totals shown on the far right. With these I
use a "sum over group" text box. It doesn't seem to matter whether I
put it in the header or footer, I get the right value. This said, I
stuvk it in the header to save space in the report, especially for
summary reports, wherre the name and whatever total is all I want
anyway.

On the report giving me trouble, I'm trying to repeat this same
style. The main difference with this report is that the detail section
contains two subreports, each listing purchases by this family in two
fairly dissimilar areas. Each subreport contains a textbox I can read
out, that gives me the total for it. If I want the total for the two
together in the detail section or the footer, no problem. It's just if
I put it in the header that the total is wrong.

Normally, Id agree that I'm just trying to use it before it's
calulated when it's in the header. What I'm seeing though, is that I do
get the right totals for each family, even witht the calculation done in
the header, for all but the very first group on the report. The family,
"Able," for example, is wrong, but "Browning" all the way through to the
end is correct. I just don't get what could be different about the
first group that make this not work.

I still don't get it. The expressions you said you have in
the group header makes sense if there is only a single
detail in each group. If there's more than one, the
expression will only retrieve the subreport values in first
detail. Maybe this is the cause of your trouble??

Since your expressions can only work with a single detail in
each group, the question remains - Why use grouping? You
say it works if the total text box is in the detail section,
so, if all else fails, get rid of the grouping and do it all
in the detail. You should be able to make it look the same.

I think I would try to double check the expressions by
making the subreports' footer section visible so you can see
the value that the expressions are trying to use.

If none of that provides any insight, I might have to cop
out on your question and cry bug.

 

[Max Moor]

If none of that provides any insight, I might have to cop
out on your question and cry bug.

Nope... I think you were right the first time. I've been meesing around
some more, and I think it doesn't work because I'm trying, as you said, to
use a value that's not yet calculated.

In my other reports, I have multiple detail records, but have found that if
I use a RunningSum-OverGroup, I can have to textbox in the header or
footer, and it works fine. With the sub-reports, since I'm doing the
running sum by hand, it MUST be in the footer, or it isn't correct.

I guess I'm still not sure what magic exists in a RunningSum type of text
box to make it work even in the group header, but this sure seems to fit
what I'm seeing. Does this sound sensical at all?

 

[Marshall Barton]

Nope... I think you were right the first time. I've been meesing around
some more, and I think it doesn't work because I'm trying, as you said, to
use a value that's not yet calculated.

In my other reports, I have multiple detail records, but have found that if
I use a RunningSum-OverGroup, I can have to textbox in the header or
footer, and it works fine. With the sub-reports, since I'm doing the
running sum by hand, it MUST be in the footer, or it isn't correct.

I guess I'm still not sure what magic exists in a RunningSum type of text
box to make it work even in the group header, but this sure seems to fit
what I'm seeing. Does this sound sensical at all?

Maybe MS add something to a later version of Access that
somehow can get a footer value into a header control, but
that would be news to me. I've always had to use a
multipass formatting trick to get that kind of thing to work
in the past.