gcubed

First Hierarchical Grid Portlet

Discussion created by gcubed Champion on Mar 3, 2014
Latest reply on Feb 12, 2016 by Plidian

 

I just built my first hierarchical grid portlet.  We have between 4 to 8 levels of hierarchy in our project structure.  Just in case someone else is looking to do this with multiple hierarchy here is the simplified NSQL.

CHILD_INVESTMENTS – for some reason Clarity leaves null records in the INV_HIERARCHIES table so this provides a list of all investments which are a child.

INVEST_TREE – is a recursive query with the seed being all investment which do not have a parent and the recursive part walking down each node of the seed query.

INVEST_DISTINCT – portlets like unique records so this gives the portlet what it likes.

INVEST_NSQL – provides us the inner joins to other tables for additional fields i.e. Projects

So on the first call to the NSQL hg_row_id is null so we get all records with a DEPTH = 0.  All expands as are requested we get the records that have the PARENT_ID of the selected record.

SELECT @SELECT:DIM:USER_DEF:IMPLIED:X:X.UNIQUEID:UNIQUEID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.HG_HAS_CHILDREN:HG_HAS_CHILDREN@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PARENT_ID:PARENT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRNAME:PRNAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRFINISH:PRFINISH@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRSTART:PRSTART@
, @SELECT:DIM_PROP:USER_DEF:BOOLEAN:X:X.ACTIVE:ACTIVE@
FROM (
WITH
CHILD_INVESTMENTS AS (
  SELECT DISTINCT inv.ID, inh.PARENT_ID, inh.CHILD_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
),
 INVEST_TREE (ID, NAME, PARENT_ID, DEPTH, ROOT_ID, HG_HAS_CHILDREN) as (
  SELECT ID, NAME, PARENT_ID, 0 AS DEPTH, ID AS ROOT_ID, HG_HAS_CHILDREN
  FROM CHILD_INVESTMENTS
  WHERE PARENT_ID IS NULL
  UNION ALL
  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
  ),
  INVEST_DISTINCT AS (
      SELECT DISTINCT ID, NAME, PARENT_ID, DEPTH, ROOT_ID, HG_HAS_CHILDREN FROM INVEST_TREE
  ),
  INVEST_NSQL AS (
  SELECT inv.ID UNIQUEID
    , iv.depth
    , iv.HG_HAS_CHILDREN
    , iv.PARENT_ID
    , inv.NAME PRNAME
    , inv.SCHEDULE_FINISH PRFINISH
    , inv.SCHEDULE_START PRSTART
    , inv.IS_ACTIVE ACTIVE
  FROM INVEST_DISTINCT iv
    INNER JOIN inv_investments inv ON iv.id = inv.id
    INNER JOIN ODF_CA_PROJECT odfp ON iv.id = odfp.id  
  )
  SELECT  UNIQUEID
    , HG_HAS_CHILDREN
    , PARENT_ID
    , PRNAME
    , PRFINISH
    , PRSTART
    , ACTIVE
  FROM INVEST_NSQL
  WHERE Depth = NVL(@where:param:user_def:string:hg_row_id@, 0)
  UNION
  SELECT  UNIQUEID
  , HG_HAS_CHILDREN
  , PARENT_ID
    , PRNAME
    , PRFINISH
    , PRSTART
    , ACTIVE
  FROM INVEST_NSQL
  WHERE PARENT_ID = @where:param:user_def:string:hg_row_id@
  ORDER BY PRNAME
) X WHERE @FILTER@

 

Giving Back, (I have learned a lot on these boards)
Gene

Outcomes