Report total... from a GROUP level

[jammer]

Hey All,

I have a report based on a query that joins a 'parent' and 'child'
table.
Each row in the query corresponds to a row in the child table, with a
few fields from the Parent table.

At the bottom of the report, in the footer, I total some values from
the Child table.

I also have a group by for each Parent record.

I want to get a SUM for one of the Parent fields (which is shown in a
group by header).

So if I have 2 parent records, with 3 child records each, I want to sum
Parent.age over the two parent records.

How do I do that?

Thanks for any ideas!

Joe

[Rich P]

Hi Joe,

Try using a subreport - or 2 subreports. A subreport is just a report
within a report. YOu can do your detail stuff in one subreport and the
cumulative query in the 2nd subreport. That is one idea, probably the
hard way to do it.

something a little easier might be to to include your detail data and
sum data in the same table/query:

Select * from
((select detail stuff) Union All (Select Sum stuff)) t1

Note: when you do a

select * from (Select * from tbl1 where something) t1

You have to alias the contents within the Parentheses (...) with
something like t1 or xyz or Steve... give the (select *...) a name - the
alias.

Just some ideas.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[jammer]

Rich,

Thanks for the ideas.

I should have mentioned, I already have a report with a sub-report.

Within the subreport, I have the Parent and Child query.

Within the subreport I sum child fields ( the sum values are shown in
the report footer section). I also display these sum values on the
main report.

The problem is that in the sub report... I have a "group by" section
(which is grouped on one of the Parent fields), and what I want (in the
end) is to SUM one of the parent fields at the end of the subreport.

The problem is the parent field does not appear in the DETAIL section
of the subreport, so I can't do a sum on it.

Is there some way to do a sum on fields in the Group by section?

Thanks,
Joe

Rich P wrote:

Hi Joe,

Try using a subreport - or 2 subreports. A subreport is just a report
within a report. YOu can do your detail stuff in one subreport and the
cumulative query in the 2nd subreport. That is one idea, probably the
hard way to do it.

something a little easier might be to to include your detail data and
sum data in the same table/query:

Select * from
((select detail stuff) Union All (Select Sum stuff)) t1

Note: when you do a

select * from (Select * from tbl1 where something) t1

You have to alias the contents within the Parentheses (...) with
something like t1 or xyz or Steve... give the (select *...) a name - the
alias.

Just some ideas.

Rich

*** Sent via Developersdex http://www.developersdex.com ***