AnsweredAssumed Answered

Filter in Hierarchial portlet doesn't work correctly

Question asked by rajesh_reddy_nyl on Jun 20, 2017
Latest reply on Jun 20, 2017 by Dave_3.0

Hi,

 

I am not able to filter child entries in Hierarchical Portlet. What I'm trying to get is full tree if a leaf level Child or Branch level child or top level Parent is filtered.

 

Example

 

NameLevel
A0
     B1
     C1
          D2
E0

 

If I filter for D I should get full A->C->D or A->B,C->D. I'm ok even if I get B in the list when I filter for D.

 

 

Below is the NSQL code I'm using for hierarchal portlet.

 

 

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 PFM_PORTFOLIOS inv
  LEFT OUTER JOIN pfm_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL
  LEFT OUTER JOIN pfm_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.finish_DATE PRFINISH
    , inv.START_DATE PRSTART
    , inv.IS_ACTIVE ACTIVE
  FROM INVEST_DISTINCT iv
    INNER JOIN PFM_PORTFOLIOS inv ON iv.id = inv.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@
  union
/* This is the part that enables Export to Excel action to   work - get all the rows for parent and children */
    SELECT UNIQUEID, null HG_HAS_CHILDREN, PARENT_ID, PRNAME, PRSTART,PRFINISH, ACTIVE
    FROM INVEST_NSQL
    WHERE   @where:param:user_def:integer:hg_all_rows@ = 1
  ORDER BY PRNAME
) X WHERE @FILTER@

Outcomes