Clarity

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

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@

You should be able to do it using a user parameter type filter (i.e. a @WHERE:PARAM:USER_DEF:INTEGER:***@ filter in the NSQL) but not a "normal" filter.

You are going to have to code some slightly complex/repetitive SQL to use the parameter filter to pick up the right "parent" levels in the (already complex) NSQL that we need to drive hierarchical portlets though.