Hierarchical Program Portlet

[Robert_Ensinger]

Hi all.
I need a Hierarchical Portlet that shows a program, the program's key milestones, the program's sub projects, their phases and milestones.
- The user should be able to un-check a box to turn off phases.
- The user should be able to un-check a box to turn off non key milestones.

The code I have working on the happy path is based off of the PMO Accelerator's Project Manager Schedule Dashboard query. Unfortunately, when I 'un-check' something, this must break the hierarchy chain, for the 'sub' of that something fails to draw. This hierarchy code is a bit over my head.

A) Does anyone see a quick & easy fix to this existing code?
B) Should I just scrap it and start fresh? I've done that too, but I can't get the sub project WBSes to draw - again, the problem is my skill with this hierarchy stuff.

If someone can help get this working, this is something I believe ought to be in the PMO Accelerator. I will gladly 'open source' the finished version.

Thanks!
.

Attachment(s)

fresh_start_program_hierarchy_test.txt   777 B 1 version

Program Hierarchy NSQL.txt   29 KB 1 version

[Anon Anon]

Can't "debug" that NSQL in my head.... and I can't see how your 2 "show" parameters are coded in the NSQL (if at all?)

BUT.... if you add two returned columns that are either 1 or 0 (whether the record is a "key milestone" and whether the record is a "key task") then can you get the filters to work OK just as "normal" @FILTER@ filters) - ie the filtering is done last (after the hierarchy has been evaluated) - not sure if I'm making any sense here?

Dunno though - this might just not work, I have never dug enough into actually what happens to the SQL statements when running hierarchy NSQL.... :unsure:

[Robert_Ensinger]

Hi Dave. Yes, I already tried that approach. The problem is that whatever level I 'turn off', nothing draws after it. I think I've had an ah-HA, the problem is that when turning off WBS elements, I need to ensure the parent-child relationship (hg_has_children) isn't broken. Example: If I turn off the phase in the test project above, the milestones have to know that the project is their parent now. Likewise, I think also instead of dealing with 'Project Level' and 'WBS Level', what I actually require is 'Project Level' - 'Phase Level' - 'Milestone Level'. Phases are their own level. Thanks for prodding me to keep thinkin'.

[Anon Anon]

OK yeah I think your comment fits into the "I've never quite worked out what the SQL looks like with hierarchical portlets" category.... but "yes" I would agree that if you are dynamically removing levels in the hierarchy in the main body of the SQL (what I think I was suggesting was removing the data "last" - but as I say I dunno if that works for HPs) you would then need to still provide that hierarchical relationship in the SQL (now to the project instead of the task).... you would need to have a bit of ugly SQL going on populating the parent id depending on the value of the "param_***" parameter(s).

All good "fun" I think? :ph34r:

[Rajini]

Hi Rob,

for the hierarchial portlet.. you would have a select statement for each level.. ie. programs, programs key milestone,phases, sub projects , sub projects milestones and phases....

if you dont need the phases or milestones.. on those select respective statments.. or levels.. you would have a condition ...parameter "include milestones"=0...

for the first level .. here for you the program... the HG_ROW_ID would be null (in the where clause) and "+" sign or the hierarchy will show based on "hg_has_children" column in your program select statement...
... so you bascially control the condition to check if the programs should show hierarchy in this column... example.. for that particular column i say... case when program has milestones and when parameter include milestoes=1 then the id of the program (which is passed into the hg_row_id when you click the "+" sign.)... else null.....

for second level on.. which is shown if there is a child...the hg_row_id will have the id of the parent selected ...

you can select the parameters declared in your where clauses .. where required...for example for hg_row_id parameter.... "@SELECT:PARAM:USER_DEF:STRING:HG_ROW_ID@".. in the inner queries...

Thanks and Regards,
Rajini