Filter on Grand Total

  • Thread starter Thread starter acss
  • Start date Start date
A

acss

I have a report on country invoices and within the Report Footer section,
there is the control for a grand total

=Sum([SumOfInvAmt])

How do i get the grand total just for one country such as for example
"BRASIL".

Thanks
 
acss said:
I have a report on country invoices and within the Report Footer section,
there is the control for a grand total

=Sum([SumOfInvAmt])

How do i get the grand total just for one country such as for example
"BRASIL".
Click to expand...


That's not very clear. To get the total for Brasil in the
report footer section, you need to use this kind of goofy
expression:

=Sum(IIf(country = "Brasil", SumOfInvAmt, 0)

To get the total for each country in its group footer
section, Use just this:
=Sum(SumOfInvAmt)

To get a summary of all the country totals in the report
footer section, it is best to create a Totals query that
calculates all the country totals and then use a subreport
based on the Totals query.
 
acss said:
I have a report on country invoices and within the Report Footer section,
there is the control for a grand total

=Sum([SumOfInvAmt])

How do i get the grand total just for one country such as for example
"BRASIL".

Thanks
Click to expand...

It's quite easy - you need to track down the "sorting and grouping"
button on the toolbar, and group by Country. Then (as you've no doubt
done for the report footer) add a control whose ControlSource is set to
=Sum([SumOfInvAmt]).

Note that you can get problems if one of the amounts is null, so it's
worth extending that to:

=Sum(NZ([SumOfInvAmt],0))

NZ replaces nulls with 0 (or whatever you choose).

Of course, you could simply adjust the report's underlying query (or
apply a filter in code), so that only records from Brazil are included.

Phil, London
 
Back
Top