Well, it's getting late but I'm bored (TI processing around 16 millions lines of data in background) so going to have a stab at this.
First problem appears to be a simple enough cause -
I have this hierarchy
Total Depreciation Amount
- Calculated Depreciation
- Actual Depreciation
- Manual Adjustment
where Calculated Depreciation and Actual Depreciation have rules but Manual Adjustment doesnt.
I have these feeders
['Forecast','Calculated Depreciation']=>['Forecast','Total Depreciation Amount'];
['Actual Depreciation Amount']=>['Total Depreciation Amount'];
['Manual Adjustment']=>['Total Depreciation Amount'];
It seems that the Total Depreciation Amount does not show the consolidated value unless there is an amount in Manual Adjustment. Can anyone tell me where I have gone wrong?
That's because you're feeding into Total Depreciation Amount which is a consol. Feeding a consol is shorthand for feeding the children of the consol. Therefore
['Forecast','Calculated Depreciation']=>['Forecast','Total Depreciation Amount'];
reads to the system as
"If there's a number in Forecast/Calculated Depreciation, feed Forecast/Calculated Depreciation and Forecast/Actual Depreciation Amount and Forecast/Manual Adjustment"
That's why when you put in a manual Adj (the only place you can actually load something as the other two are rule calc'd) it works because the manual entry is pretty much feeding the consol. Easiest way to think about it is that essentially a consol is a system based rule that adds up the nodals beneath it. Therefore an entry in the nodal points of a consol act as a system feeder to make the system realise there's a number that needs to be displayed. No doubt someone can explain that better than I can at the minute but that's the easiest way to think about it.
Causes probs sometimes when you calc a C: level rule that doesn't have any data below it. Sounds stupid but it does happen. Have had to create an extra element that calc's at 1x10E-59 before to get the C: level to feed. It's all because you can't feed a Consol as the feeder will read it as "feed the children of the consol". Can be expanded to really say "feed all my bottom level nodals" rather than children to avoid confusion so you can't just add another level of consol to trick the system, you need a bottom level nodal entry at some point to force a display of the consol. Hope that bit makes sense.
A side effect in your example by the way is also that you're essentially feeding Calculated Depreciation and Actual Depreciation from Manual Adj. Therefore, my gut feeling is that you're overfeeding your model with these feeders as Manual Adj is highly unlikely to be the driver for the other two calcs. Feeding consolidation is a concept that gets a lot of people.
Now that's out the way, I assume that neither of the feeders for Actual or Calculated Dep are working (otherwise these should have acted as data that would have caused the Total to display).
['Forecast','Calculated Depreciation']=N:IF (!GL Account @='A321551', DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2416211','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321551'),
IF(!GL Account @='A321531',DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351011','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321531'),
IF(!GL Account @='A321532', DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351012','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321532'), IF(!GL Account @='A321533', DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351013','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321533'), IF(!GL Account @='A321534' , DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351014','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321534'), IF(!GL Account @='A321512' , DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2321012','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321512'),IF(!GL Account @='A321522' ,DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2341012','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321522'),0)))))));
Feeders;
[{'Actual','Forecast'},'Cost']=>['Actual+Forecast','Cost'];
[{'Actual','Forecast','Actual+Forecast'},'Cost']=>['Calculated Depreciation'];
['Forecast','Calculated Depreciation']=>['Forecast','Total Depreciation Amount'];
['Actual','Actual Depreciation Amount']=>['Forecast','Actual Depreciation Amount'];
[{'Actual','Forecast'},'Actual Depreciation Amount']=>['Actual+Forecast','Actual Depreciation Amount'];
['Forecast','Actual Depreciation Amount']=>DB('DepreciationCalc','Forecast',!Year,DIMNM('Month',DIMIX('Month',!Month)+1),!Projects By Division,!GL Account,'Actual Depreciation Amount');
['Actual Depreciation Amount']=>['Total Depreciation Amount'];
['Manual Adjustment']=>['Total Depreciation Amount'];
['Forecast','Total Depreciation Amount']=>DB('Expense','Forecast',!GL Account,!Projects By Division,'Unspec Contract',!Year,!Month,'Row 1','Amount');
couple of general points here -
is there a reason that you have rule driven Actual+Forecast? A consolidation would be a hell of a lot faster and make your rule/feeder writing a lot easier.
your calc'd dep rule is pretty messy considering it's doing the same thing again and again with different codes. Would put the codes you want to fire from (A321551, A321531, A321532, A321533, A321534, A321512, A321522) into a consolidation - I'm going to call it Zanzibar for the following example. Then create a mapping cube with these elements in a dim and a string field with one element. Then map in the codes. Then rewrite your rule as
['Forecast','Calculated Depreciation']=N:IF
(ELISANC('GL Account','Zanzibar',!GL Account)=1,
DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,DB(REFCUBE TO YOUR MAPPING),'Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown',!GL Account)
,STET or CONTINUE depending on which you want);
then use a feeder from Depreciation Calc along the lines of
['Actual+Forecast','Cost','{2416211,2351011,2351012,2351013,2351013}']=>DB('DepreciationCalc','Forecast',,!Year,SUBST(!Month,LONG(!Month)-4),!Projcts By Division,'Zanzibar','Calculated Depreciation');
as this seems to be where your data is driven from. Could always create a consol with these elements also to trim your rule further or specifically point them at the the appropriate areas within Zanzibar if you want to be as lean as possible if you so desire.
['Actual','Actual Depreciation Amount']=N:IF(ELISANC('GL ACCOUNT','A321500',!GL Account)=1,DB('Expense','Actual',!GL Account,!Projects By Division,'All Contracts',!Year,!Month,'All Rows','Amount'),0);
['Actual','All Contracts',{'A321512','A321521','A321522','A321523','A321531','A321532','A321533','A321534','A321535','A321551','A321553'},'All Rows','Amount']=>DB('DepreciationCalc','Actual',!Year,!Month,!Projects By Division,!GL Account,'Actual Depreciation Amount');
Think the bold !GL Account is what's causing you a problem potentially although very hard to tell without looking at it in more detail. I'm guessing that changing that to 'A321500' will force a feed down that consol which hopefully should sort out your problem. Or maybe it's Zanzibar thinking about it. Getting too late and far on in this post to be bothered to go back and try and repick - sorry
Once you get the feeders for Actual and Forecast Dep Amount sorted correctly, it'll flow up to your consol to sort your initial problem.
Also, as an aside, noticed you have
DIMNM('Month',DIMIX('Month',!Month)+1),!
somewhere in your rules. That's 'orrible - much safer to change that to an attribute lookup against month for next period in the rule and previous period in the feeder. Saves someone killing your rules by adding a rogue element in the month dim.
Apologies if the syntax went a bit screwy towards the end, KMFDM came on my IPod and that always ruins my concentration
Anyroad, give that a bash, hope it helps. My TI has just finished loading so going to go and try to unpick my own code now
