I'm trying to build a portlet to present Hierarchical Investments (Programs/Projects/Subs, whatever) and their Issues - and example of my test data is shown below.
I'm basing my code off of the PMO Accelerator's Project Manager Schedule Dashboard. As shown below, I can present the Investment and their Issues in a hierarchical portlet & I can present the Investments in their hierarchy.
I'm having a tough time bringing this all together in NSQL. The investment hierarchies are dynamically built off of Parent and WBS Sequence (since sub projects are in PRTASK). Issues obviously aren't in PRTASK therefore have no WBS sequence. I've not had success trying to hijack or shoehorn these in sequence. I'm getting cross eyed and running out of ideas. Attached is the a_hierarchy_test_5 NSQL shown above. To see it run, create a portlet & feed an investment ID into it as shown. Use 'do not show results until filter' for easiest testing.
1) Has anyone done something like this successfully this before? If so, care to share? Or..
2) Has anyone done this unsuccessfully before and they recommend I give up?
My next idea is to try to build a SQL View that marries the data and generates a new sequence, but I'm starting to feel like I'm at diminishing returns.