Input into different hierarchy level and display balances in one module with Hierarchy and COA dim

michael_park

Hi,

I've been digging around all the resources but could not find any solution to my problem.

We are trying to build a legal hierarchy tree (ragged) with inputs into each entity in varying levels and then presenting those inputs in one module with 3 dimensions: legal entity hierarchy tree, chart of accounts, and months.

Thought I've got it working by using Formula as a summary method in the module, FINDITEM & PARENT functions but once the parent level is beyond 2 levels from the Child level, this won't work.

 

This is important when you have entities that don't roll up to a parent by summing 100% of a child and that parent entity has transactions on its own as well.

e.g 

Child 1 balance $100

Parent 1 owns 80% of Child 1 and has $10 transaction on its own.

Therefore Parent 1 should have a balance of $90 (80% of $100 and $10).

When you are using just normal roll-up sum this is impossible and when you change the summary method in a module to FORMULA it won't work with SUM or LOOKUP function.

 

Amounts entered in the Bottom Level 4 Child entities.

 

Amounts entered in the Level 3 Parent entities.

 

 

 

 

Amounts entered in the Level 2 Parent entities.

 

 

Hope this is clear.

Thanks for your help in advance.

 

KBeltz

Hey Michael,

 

Seems like a difficult case -- especially with LOOKUPS and SUMS not having the ability to have a formula.  I would suggest creating dummy list members at your L4 level that are for L3 and L2 inputs.  That way, the user is inputting everything on the "L4" level, even though some are inputs on L3 and L2.  

 

You should be able to turn the summary method on "SUM" on the input line to see the aggregation up to L3 and L2.