Summing group footer data

[bradlee27514]

My data looks like this:

GH1 Invoice | Amount
D 1 | $10
GF1 1 | $10
D 2 | $5
D 2 | $5
GF2 2 | $5

where GF1 is group footer 1, D is details, and GF2 is group footer 2. I want to sum the Amount in the GF2 field for the whole data set. However when I do I get $20, when I should get $15. How can I make this sum work? I tried a formula where I wrote:

if
{#count of items on invoice} > 1
then
{@amount sum} / {#count of items on invoice}
else
{@amount sum}

However when I tried to sum this field I got an error message that the field could not be summarized.

The data has to be grouped like this because of how the report is designed (I can elaborate on this if needed).

 

[lbass]

What is the content of {@amount sum}?

-LB

 

[bradlee27514]

It is this field
{Command.totalapplyamount}

from my query:
select
*
from
(select
aptodcnm, sum(actualapplytoamount) as totalapplyamount
from
rm30201
group by
aptodcnm)
as
rm30201
inner join
sop30200
on
rm30201.aptodcnm=sop30200.sopnumbe
left join
sop30300
on
sop30200.sopnumbe=sop30300.sopnumbe
where
sop30200.ReqShipDate > '2008-12-31'
and
(
sop30200.custnmbr like 'usg%'
or
sop30200.custnmbr like 'cgc%'
)



I had to create it because RM30201 has multiple entries and i need a single entry as a starting point. it sums the amount applied from checks to an invoice, very often more than one check is applied to the same invoice.

 

[lbass]

Insert a running total that does a sum of {command.totalapplyamount}, evaluate on change of group: group#2, reset never.

Place the result in the report footer.

-LB

 

[bradlee27514]

I need it in group footer 1 because that is how the report is organized, by customer locations. That running total would sum up each record just as it is doing currently for each group, correct? I think my initial post may have been mis-labeled. I am grouping by customer and then by invoice. Once everything is finished, the detail lines will be hidden.

GH1 Invoice | Amount
D 1 | $10
GF2 1 | $10
D 2 | $5
D 2 | $5
GF2 2 | $5
GF1 Total | $20

The total should actually read $15. The details of the invoice (there are more than one line items on some invoices) is throwing the addition off. I need something akin to a distinct sum (but also taking into account that invoices could have the same amount).

 

[bradlee27514]

This worked:
"Use a RT as a SUM but set the evaluate for section to once per group (group2) and reset at GROUP1 palced on GF1.