I have been playing about with performing rollups of data, and I seem to have ended up way more complex than I think I need to! I would appreciate any help/suggestions anyone can offer.
I have data with 1st field that specifies the level
Level
Description
Amount
1
Main Rollup 1
£100
2
Secondary Rollup 1
£50
3
Tertiary Rollup 1
£20
3
Tertiary Rollup 2
£15
2
Secondary Rollup 2
£3
I want the user to be able to select which level to view the data. A high level (low number) should roll up all later amounts, with the desc
e.g. view level 1 should give:
Main Rollup 1 £188
e.g. view level 2 should give:
Main Rollup 1
£100
Secondary Rollup 1
£85
Secondary Rollup 2
£3
e.g. view level 3 should give:
Main Rollup 1
£100
Secondary Rollup 1
£50
Tertiary Rollup 1
£20
Tertiary Rollup 2
£15
Secondary Rollup 2
£3
I want to be able to select the level to view (either list box or drill down/cyclic dimension) but also have the various levels in a pivot table, with totals at each level.
I am assuming I need to have the different levels as different columns:
Level
Description
Level 1
Level 2
Level 3
1
Main Rollup
Main Rollup 1
Main Rollup 1
Main Rollup 1
2
Secondary Rollup
Main Rollup 1
Secondary Rollup 1
Secondary Rollup 1
3
Tertiary Rollup
Main Rollup 1
Secondary Rollup 1
Tertiary Rollup 1
3
Tertiary Rollup
Main Rollup 1
Secondary Rollup 1
Tertiary Rollup 2
2
Secondary Rollup
Main Rollup 1
Secondary Rollup 2
Secondary Rollup 2
I have been chasing my tail a bit with this one, so I would appreciate any help anyone can offer.
That will give me a total of each level, however I want to be able to drill down, i.e. have a pivot table where I get totals of level1, but then I can expand that level and get the relevant totals at level2, then level3 etc...
