So, I'm trying to build out a table based upon a data set that looks like this:
| Org | Division | Team | Team Member | Team Member Sales |
| North | North Central | Elm | Andrew | $180 |
| West | Downtown | Birch | Ann | $1,240 |
| North | North Central | Maple | Erin | $600 |
| West | Uptown | Willow | Gregory | $230 |
| North | North Central | Oak | Jill | $500 |
| North | North Central | Oak | Kevin | $320 |
| West | Uptown | Hickory | Olaf | $550 |
| North | Northwest | Pine | Open | $0 |
| West | Uptown | Willow | Robin | $1,500 |
| North | Northwest | Pine | Satish | $465 |
With the goal of getting to a table that looks like this:
| Team Member | Team Member Sales | Team | Team Sales | Division | Division Sales | Org | Org Sales |
| Robin | $1,500 | Willow | $1,730 | Uptown | $2,280 | West | $3,520 |
| Gregory | $230 | Willow | $1,730 | Uptown | $2,280 | West | $3,520 |
| Olaf | $550 | Hickory | $550 | Uptown | $2,280 | West | $3,520 |
| Ann | $1,240 | Birch | $1,240 | Downtown | $1,240 | West | $3,520 |
| Jill | $500 | Oak | $820 | North Central | $1,600 | North | $2,065 |
| Kevin | $320 | Oak | $820 | North Central | $1,600 | North | $2,065 |
| Erin | $600 | Maple | $600 | North Central | $1,600 | North | $2,065 |
| Andrew | $180 | Elm | $180 | North Central | $1,600 | North | $2,065 |
| Satish | $465 | Pine | $465 | Northwest | $465 | North | $2,065 |
| Open | $0 | Pine | $465 | Northwest | $465 | North | $2,065 |
The sorting and order aren't a problem, but filling in some of the values is slowly driving me crazy. The key thing we're trying to get to is a new sum for each higher-level category (Team / Division / Org) on a row-level basis. For example, the sales for Team Willow add up to $1730 (with the value shown on two rows). That team is part of Division Uptown, the sales for which add up to $2280 (the value shown on three rows). That division belongs to Org West (four rows), sales for which add up to $3520. Note the number of rows for each category can vary.
I've been able to do up to two levels with the summary tool but have been stuck after that. Doing a cascading series of joins doesn't work as in the real data set I'm dealing with hundreds of categories for the Team, Division and Org fields. I'd appreciate any thoughts on how to attack a report like this.
