Hierarchical Portlet

Blog Post created by kumni11 Employee on Mar 22, 2018

Hello Readers


This post is to educate you with basic concept around how to write a Hierarchical Portlet in CA Clarity PPM.


Creation of Hierarchy portlet revolves around the concept of transferring the data from one level to next and so on. 

So, if for example we talk about a hierarchy portlet for OBS structure which allows you to expand the OBS tree structure level by level then it becomes the need that Parent OBS UNIT detail is passed on the child OBS UNITS so that the filtering is possible.


This traversing of data from one level to another is achieved using two parameters in NSQL :

   1. hg_has_children  ---- This parameter defines the selected column which will carry the data from the parent level to child level

   2. hg_row_id  ------ This parameter stores the value from previous level's hg_has_children and thus can be used in the where clause of the query to filter out any value.


Example : Two levels of OBS units


---Level 1 -----




to_char(id) as hg_has_children

from prj_obs_units


and depth=1



------Level 2 ----------




null as hg_has_children

from prj_obs_units

 where  parent_id in (@WHERE:PARAM:USER_DEF:STRING:hg_row_id@ )



Explanation :

In level 1 : Fetched three fields Name, ID and ID with alias hg_has_children (this is passed to the level 2 hg_row_id parameter ). HG_ROW_ID in level 1 is kept as null because it is the first level and it receives no value from anywhere. 


Level 2 :  Since the example if only two level hierarchy so the hg_has_children is having "null" passed as the column., this defines the end of the hierarchy. In the where clause the parent_id is filtered using hg_row_id which has the unit IDs from level 1 passed to it.


UNION is important as it defines the separation of levels. 

Note:  UNION ALL will not work as UNION in hierarchy portlet.



Another problem in Hierarchical portlet is Export to Excel which would not work properly unless the query is not designed to handle that.


I will cover the export to excel related code practices in the next post.


#HappyLearning #PPM hierarchical portlets