Adding totals from two subreports

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

Guest

Hi,
Can anyone tell me how to add together two values from two subreports.
I have a Monthyl Report system within which there are two subreports - one
lists CashSales the other lists Invoices.
At the bottom of each subreport there is a total of sales value.
I want to be abale to add together these two totals to produce an overall
total.
I have tried using the expression builder and got this =Sum([CashSales
Query]![SubTotal])+Sum([Invoices Query]![SubTotal]) but that just gives an
Error value.

Any help is appreciated.
 
Colin, there are several steps to this.

Firstly, make sure the subreport has a Report Footer section (View menu in
report design.) Add a text box there, and set these properties:
Control Source: =Sum([Amount])
Format: Currency
Name txtSubTotal
You can set the Visible property of the section to No if you don't want to
see it.

Now you can bring this value back onto the main report:
=[Sub1].[Report].[txtSubTotal]
Replace "Sub1" with the name of your subreport control.

That generates an error if the subreport has no entries, so you need to test
the HasData property. Also recommend using Nz() in case a Null value is
returned, since you want to add it to another value, so you really want:
=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtSubTotal],0), 0)

Once you have that working for both working, you can add them together.
 
Hi,

Thanks for help, worked prefectly

Allen Browne said:
Colin, there are several steps to this.

Firstly, make sure the subreport has a Report Footer section (View menu in
report design.) Add a text box there, and set these properties:
Control Source: =Sum([Amount])
Format: Currency
Name txtSubTotal
You can set the Visible property of the section to No if you don't want to
see it.

Now you can bring this value back onto the main report:
=[Sub1].[Report].[txtSubTotal]
Replace "Sub1" with the name of your subreport control.

That generates an error if the subreport has no entries, so you need to test
the HasData property. Also recommend using Nz() in case a Null value is
returned, since you want to add it to another value, so you really want:
=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtSubTotal],0), 0)

Once you have that working for both working, you can add them together.

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

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

Colin said:
Hi,
Can anyone tell me how to add together two values from two subreports.
I have a Monthyl Report system within which there are two subreports - one
lists CashSales the other lists Invoices.
At the bottom of each subreport there is a total of sales value.
I want to be abale to add together these two totals to produce an overall
total.
I have tried using the expression builder and got this =Sum([CashSales
Query]![SubTotal])+Sum([Invoices Query]![SubTotal]) but that just gives an
Error value.

Any help is appreciated.
Click to expand...
Click to expand...
 
Back
Top