views:

32

answers:

0

Hello, this is my first post, so I hope I get everything right...

I am trying to generate a report that shows a summary of items held for each customer in our database. Our customers can have children companies, so I need the report to group on the parent company, then show the children and their sums, and then show the total for the Parents + children. For example:

Parent Customer                             Amount
    -Parent Customer (again)
        -Parent Customer's Item 1     $1000
        -Parent Customer's Item 2     $500
                                        Total      $1500
    -Child Customer 1
        -Child Customer's Item 1       $2500
        -Child Customer's Item 2       $3000
                                       Total      $5500
    -Child Customer 2
        -Child Customer's Item 1       $250
        -Child Customer's Item 2       $300
                                       Total      $550
(repeat with Parent 2, etc...)
Total                                             $7550

For simplification, my table structure is as follows:

Customers:
-CustomerID
-ParentID
-Name

Items:
-ItemID
-CustomerID
-Item Name
-Amount

CustomerID is the Primary Key, and ParentID has a Foreign Key Reference to Customers.CustomerID. The DB is MSSQL 2008, and I am using MSSQL Report Bulder 2.0

Thanks!