I am looking for a portlet or report that allows the user to create a report based upon a project. that shows the resoruces working on that project, and lists all child projects and for each child lists the resoures.
I should think that you need to create a custom query based portlet for that.
Take the resources for the resources for a project from prteam and the subprojects eg from INV_FLAT_HIERARCHIES or
INV_HIERARCHIES and then their teams in the same manner.
Check this -
Flip me an email. I will forward you the query.
Hi Atul. I'm digging around for a soup starter on some Issues and Risk roll-ups. This might help me as well - I'll shoot you a note over on LinkedIn.
Oops. Nevermind. Too easy. CA Clarity Tuesday Tip: Inv_Flat_Hierarchies.
I get investment trees like this.
WITH CHILD_INVESTMENTS AS --Get all investments which have a parent and are active
( SELECT DISTINCT inv.ID, inh.PARENT_ID, inp.PARENT_ID AS HG_HAS_CHILDREN, inv.NAME
FROM INV_INVESTMENTS inv
LEFT OUTER JOIN INV_HIERARCHIES inh ON inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL
LEFT OUTER JOIN INV_HIERARCHIES inp ON inv.ID = inp.PARENT_ID
WHERE inv.IS_ACTIVE = 1
INVEST_TREE (ID, NAME, PARENT_ID, DEPTH, ROOT_ID, HG_HAS_CHILDREN) AS -- pull the tree base on a root parent
(SELECT ID, NAME, PARENT_ID, 0 AS DEPTH, ID AS ROOT_ID, HG_HAS_CHILDREN
WHERE PARENT_ID IS NULL
--AND ID = 5014117 --set this to the top of the tree of interest
SELECT ci.ID, ci.NAME, ci.PARENT_ID, DEPTH + 1, ROOT_ID, ci.HG_HAS_CHILDREN
FROM CHILD_INVESTMENTS ci
INNER JOIN INVEST_TREE iv
ON ci.PARENT_ID = iv.ID
select * from INVEST_TREE
Retrieving data ...