AnsweredAssumed Answered

Calculate Allocation% through Hierarchy in NSQL

Question asked by Michiel_Meijler on Oct 15, 2010
Latest reply on Oct 20, 2010 by Owen_R
Is there a way to calculate the allocation% for a sub-project to the program?

i.e.:
A sub-project is allocated for 80% to a project.
The project is allocated for 50% the program.

Hierarchy:
Progr
|
|- Project : Allocation = 50%
| |
| |- Sub-project: Allocation = 80%

This means that every hour written (or planned) to the sub-project contributes for 40% (=50% * 80%) to the program.

Suppose the sub-project has in total 500 hours in actuals, the project itself has 200 hours in actuals, the total number of ours for the project is 200 + 80% * 500 = 600.
The program has 50% * 200 + 50% * 80% * 500 = 300 hours on actual work.

To be able to create NSQL queries and portlets reporting over programs, we use the INV_FLAT_HIERARCHIES table to determine which (sub-)projects make up the totals for the program. How can I create a construction in NSQL such that the hours from the (sub-) project can be multiplied by the correct allocation%?

Outcomes