Help: Question to the Group on Report Grouping

Has anyone successfully created a report that has a main Table and 2 Group
Levels and correctly shows aggregates (SUM) in both Group footers and the
Table footer?
Like this:
_______________________________________________________________
Table
Group #1
Group #2
Detail Row #1 (this shows fields that relate to Group #1. HideDuplicates is
set to True.
Detail Row #2 (this shows fields that relate to Group #2)
Group #2 Footer
Group #1 Footer
Table Footer
_______________________________________________________________
The data I am selecting contains, per row, all the data to populate the
detail rows. (It's basically a flat row that selects invoice and invoice
line item information. So it looks like this:
CustID, InvoiceID, Invoice Number, Invoice Amt, Invoice Line Item ID,
Invoice Line Item Amount...
1,001,123ABC,100.00,1,50
1,001,123ABC,100,2,50
... and so on...
The Invoice fields go in the first detail row. The invoice line item fields
go in the 2nd detail row. I'm just showing line items by invoice by
customer.
I have SUMs in the Group #2 footer. Works fine.
I have SUMs in the Group #1 footer. Problem: Since each detail row
contains the value, it adds them all. For example, if I put invoice amount
in Detail Row #1 and SUM(invoiceamount) in the Group #1 footer it adds
invoice amount in every detail row so, of course, the value isn't correct.
Now, it only shows on the report once because of HideDuplicates is true.
The same applies to what goes in the Table footer.
Is this report design wrong? Any ideas on how to handle this'
--
Adrian M.
MCPHi Adrian,
I'm not 100% sure I understand how you are designing your report, how's
this?: You have many invoices, and each invoice has many line items,
right? So for each invoice you want a sum of the invoice amount?
Without knowing what your groups are, here is what I would suggest, I
hope this helps: I would add another group - this group would be where
you put your invoice fields. There would then only be 1 detail row for
your line items.
Group #1 ?
Group #2 ?
Group #3 invoice info
Detail Row line items
Take care,
Michelle|||I think my question is similar. I'm trying to total items that are grouped
but I don't want all the items totaled, just the grouping. For example, I
have 1 - 1, 2-2's and 3-3's, which display as, 1-1, 2-2, 3-3 (in a table
format). I want to sum those 'groups', meaning the sum would be '6', not '14'.
How can I do that?
Bill
"Adrian M." wrote:
> Has anyone successfully created a report that has a main Table and 2 Group
> Levels and correctly shows aggregates (SUM) in both Group footers and the
> Table footer?
> Like this:
> _______________________________________________________________
> Table
> Group #1
> Group #2
> Detail Row #1 (this shows fields that relate to Group #1. HideDuplicates is
> set to True.
> Detail Row #2 (this shows fields that relate to Group #2)
> Group #2 Footer
> Group #1 Footer
> Table Footer
> _______________________________________________________________
> The data I am selecting contains, per row, all the data to populate the
> detail rows. (It's basically a flat row that selects invoice and invoice
> line item information. So it looks like this:
> CustID, InvoiceID, Invoice Number, Invoice Amt, Invoice Line Item ID,
> Invoice Line Item Amount...
> 1,001,123ABC,100.00,1,50
> 1,001,123ABC,100,2,50
> ... and so on...
> The Invoice fields go in the first detail row. The invoice line item fields
> go in the 2nd detail row. I'm just showing line items by invoice by
> customer.
>
> I have SUMs in the Group #2 footer. Works fine.
> I have SUMs in the Group #1 footer. Problem: Since each detail row
> contains the value, it adds them all. For example, if I put invoice amount
> in Detail Row #1 and SUM(invoiceamount) in the Group #1 footer it adds
> invoice amount in every detail row so, of course, the value isn't correct.
> Now, it only shows on the report once because of HideDuplicates is true.
> The same applies to what goes in the Table footer.
> Is this report design wrong? Any ideas on how to handle this'
> --
> Adrian M.
> MCP
>
>|||So are you trying to COUNT and not SUM?|||No, I'm trying to sum. I'm trying to su the values that are grouped, not all
the values of data set.
"Vivienne" wrote:
> So are you trying to COUNT and not SUM?
>|||I have a similar problem and I know there has to be a way to do it. My
dataset query returns results like this:
Company Value CaseNumber CaseDate
---
Microsoft 134.40 00462 June 25
Microsoft 134.40 01568 May 6
Microsoft 134.40 00224 Sept 12
Sony 212.00 00986 Mar 22
Sony 212.00 02944 Dec 1
I want the company and value shown at the group level, and each case listed
in the details for each company. I can get everything to work except for a
grand total of the company's values.
Here is how I want the report to look:
Microsoft 134.40
00462 June 25
01568 May 6
00224 Sept 12
Sony 212.00
00986 Mar 22
02944 Dec 1
--
Total 346.40
Everything I've tried gives me an error, or returns a Total of 827.20
instead of 346.40.
Thanks in advance for any help.