I'm working on a financial report. The reporting entity has five levels of hierarchy. There are hundreds of subs. We're trying to produce a pretty simple 3 line report for each level of the roll up that shows actual, budget, variance, ytd, py as columns and revenue, expense and income as rows. The problem is I get the revenue, expense, NI for the inner lowest level entity, but only the NI for all the others. The only way I've been able to produce the report is using cube formulas, which require a lot of maintenance.
Here's what I want
Actual Budget Variance YTD PY Full Yr Budget
Parent 1
Sub 1
Sub A
Rev
Exp
NI
Sub B
Rev
Exp
NI
Sub 1 (sum of sub a&B)
Rev
Exp
NI
Parent 1 (sum of sub 1 (and 2 or whatever)
Rev
Exp
NI
What I get is
Parent 1
Sub 1
Sub A
Rev
Exp
NI
Sub B
Rev
Exp
NI
Sub 1 - NI
Parent 1 - NI
