# Calculate Subreport totals in a main report Group footer

T

#### ThickMike

Hi all
Apologies if this has been answered before but I canâ€™t find it.

I have a main Report with a Group called â€œProduct_Categoryâ€ which lists a
number of â€œProductsâ€ in the Detail

I have a Subreport named â€œproduct_costsâ€ which has a record for each date
and Text Boxes named â€œmaterialsâ€ and â€œfuelâ€ (there are more but Iâ€™ll keep it
simple).
The Subreport sums all costs and has Text Boxes named â€œsummaterialsâ€ and
â€œsumfuelâ€ in the footer (all with a height of 0.1cm)
The Subreport is embedded in the Detail of the Categories and linked by
Product_id

In the Detail of the Main Report there are Text Boxes named â€œprodmaterialsâ€
and â€œprodfuelâ€ (all with a height of 0.1cm) which reference â€œsummaterialsâ€
and â€œsumfuelâ€ in the Subreport footer.
Also in the Detail section, there is a Text Box named â€œsumprodcostâ€ whose
Control Source is â€œ=[prodmaterials]+[prodfuel]â€. So far, so good â€“ it all
works fine

I now want â€œCategoryâ€ footer and the Report footer to sum â€œmaterialsâ€,
â€œwagesâ€ and â€œfuelâ€. I would have thought the Control Source should be
â€œ=sum([prodmaterials])â€, etc. but it doesnâ€™t work. Nor does
â€œ=Sum([product_costs].[Report]![Summaterials]).
The Text Boxes are blank whatever I try. I do not get #Error

I have other Text Boxes relating to the â€œProductâ€ in the Detail and summing
those in the Footer do work.

All cost Text Boxes are formatted as Fixed with zero Decimal Places
I suspect the problem might be that the Subreport is in the Detail rather
than Header or Footer. But it takes up less space the way Iâ€™m trying it.

What do I do to make it work?

I know I can do away with the subreport, link costs in the underlying query
and have an extra Group for Product and costs in the Detail. But, I would
rather not do that as it takes up more space on the Report

A

#### Allen Browne

See:
Bring the total from a subreport back onto the main report
at:
http://allenbrowne.com/casu-18.html

It explains how to test the HasData property, and explains the use of the
..Report bit when referring to the report in a subreport.

T

#### ThickMike

Thanks Allen
I did see this before which made me think the problem lay in the fact I have
the Subreport in the Detail. Your tip (very good, by the way) says to put it
in the Footer.

Allen Browne said:
See:
Bring the total from a subreport back onto the main report
at:
http://allenbrowne.com/casu-18.html

It explains how to test the HasData property, and explains the use of the
..Report bit when referring to the report in a subreport.

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

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

ThickMike said:
Hi all
Apologies if this has been answered before but I canâ€™t find it.

I have a main Report with a Group called â€œProduct_Categoryâ€ which lists a
number of â€œProductsâ€ in the Detail

I have a Subreport named â€œproduct_costsâ€ which has a record for each date
and Text Boxes named â€œmaterialsâ€ and â€œfuelâ€ (there are more but Iâ€™ll keep
it
simple).
The Subreport sums all costs and has Text Boxes named â€œsummaterialsâ€ and
â€œsumfuelâ€ in the footer (all with a height of 0.1cm)
The Subreport is embedded in the Detail of the Categories and linked by
Product_id

In the Detail of the Main Report there are Text Boxes named
â€œprodmaterialsâ€
and â€œprodfuelâ€ (all with a height of 0.1cm) which reference â€œsummaterialsâ€
and â€œsumfuelâ€ in the Subreport footer.
Also in the Detail section, there is a Text Box named â€œsumprodcostâ€ whose
Control Source is â€œ=[prodmaterials]+[prodfuel]â€. So far, so good â€“ it all
works fine

I now want â€œCategoryâ€ footer and the Report footer to sum â€œmaterialsâ€,
â€œwagesâ€ and â€œfuelâ€. I would have thought the Control Source should be
â€œ=sum([prodmaterials])â€, etc. but it doesnâ€™t work. Nor does
â€œ=Sum([product_costs].[Report]![Summaterials]).
The Text Boxes are blank whatever I try. I do not get #Error

I have other Text Boxes relating to the â€œProductâ€ in the Detail and
summing
those in the Footer do work.

All cost Text Boxes are formatted as Fixed with zero Decimal Places
I suspect the problem might be that the Subreport is in the Detail rather
than Header or Footer. But it takes up less space the way Iâ€™m trying it.

What do I do to make it work?

I know I can do away with the subreport, link costs in the underlying
query
and have an extra Group for Product and costs in the Detail. But, I would
rather not do that as it takes up more space on the Report
.

A

#### Allen Browne

You can have a subreport in the Detail section, if you want it to appear for
every record.