Clarity

Hi all.

  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.

Thanks!

Hmm ; "no" I've never built something that looks like your picture, but I do admire your ambition Rob....

Just thinking out loud here.... the problem seems to be that you are trying to merge two different sets of data ; (investments with their nice PRTASK/PRSUBPROJECT hierarchy) and then issues (which only have a hierarchy in as far as they "belong to" an investment)....  so you need to simulate some sort of hierarchical information against the set of issues ("inherited" from the relevant investment that they belong to).  So I like the idea of a database view that does all the heavy-lifting (simulating the hierarchy information against the issue / generating some psuedo-hierarchical structure) here but I'm not sure that you have to use a view, some nasty NSQL should be able to do this I believe?

Good luck with that then!

#notmuchhelpthen

Also just thinking out loud, but it might save you creating a view - have you thought about the INV_TRAVERSE_TO_BOTTOM_FCT function - just pass in a parent's ID and it will give you IDs of parent and children, including grand-children, plus the level number.

Dave - thanks for confirming that this is a merge & simulate a hierarchy problem to solve. I have visions of dynamic concatenated IDs floating around in my head. Time to hit a white board. I gotta admit I don't have my head solidly around this hierarchy stuff yet.

Owen - Awesome function! I had no idea this existed. I trolled all the views but I neglected to review the Functions. I created a little POC and I like what I'm seeing. Up next slide the Issues under the investments - thanks again for calling this one out.

More work to do but my timebox on this one has expired. Got to move on to other deliverables. I hope to circle back on this in a couple weeks - I'll post up results if I have something to show.

Thanks all!