AnsweredAssumed Answered

Project Hierarquical Portlet Filter

Question asked by menan07 Employee on Aug 5, 2014
Latest reply on Aug 8, 2014 by menan07

Hi guys,

I just create a hierarquical portlet from projects and status report. I would like to filter the project and display all status reposts for the filtered project but this is not happening.

 

When i filter it displays the project and the + symbol but when i click it does not show any child.

 

Any suggestion?

 

Here goes my NSQL query:

 

SELECT 
  @SELECT:DIM:USER_DEF:IMPLIED:STATUSREPORT:ROWNUM:ID@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:inv_id:inv_id@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:sts_id:sts_id@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:titulo:titulo@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:tipo_inv:tipo_inv@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:gerente:gerente@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:data_termino:data_termino@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:data_rel_status:data_rel_status@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:status_report:status_report@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:link_inv:link_inv@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:link_sts:link_sts@,
  @SELECT:DIM_PROP:USER_DEF:IMPLIED:STATUSREPORT:hg_has_children:hg_has_children@,
  @SELECT:METRIC:USER_DEF:IMPLIED:cop_schedule_status:cop_schedule_status@,
  @SELECT:METRIC:USER_DEF:IMPLIED:cop_scope_status:cop_scope_status@,
  @SELECT:METRIC:USER_DEF:IMPLIED:cop_cost_eft_status:cop_cost_eft_status@,
  @SELECT:METRIC:USER_DEF:IMPLIED:overall_status:overall_status@
FROM
(
  select 
  inv.id inv_id,
  null sts_id,
  inv.name titulo,
  prj.is_program tipo_inv,
  res.full_name gerente,
  inv.schedule_finish data_termino,
  null data_rel_status,
  null status_report,
  sts.cop_schedule_status,
  sts.cop_scope_status,
  sts.cop_cost_eft_status,
  sts.cop_overall_status overall_status,
  1 link_inv,
  0 link_sts,
  case when sts.cop_overall_status is not null then inv.id else null end hg_has_children
  from 
  inv_investments inv
  join inv_projects prj on prj.prid = inv.id
  join srm_resources res on inv.manager_id = res.user_id
  left join (
   select
   odf_parent_id,
   cop_schedule_status,
   cop_scope_status,
   cop_cost_eft_status,
   (cop_schedule_status+cop_scope_status+cop_cost_eft_status) cop_overall_status
   from odf_ca_cop_prj_statusrpt
   where rowid in (
    select max(rowid) from odf_ca_cop_prj_statusrpt 
    where cop_report_date >= @where:param:user_def:date:data_inicio@ and cop_report_date <= @where:param:user_def:date:data_fim@ 
    group by odf_parent_id
   )
  ) sts on inv.id = sts.odf_parent_id
  where 
  prj.is_template = 0
  and inv.is_active = 1
  and inv.schedule_start <= @where:param:user_def:date:data_fim@
  and inv.schedule_finish >= @where:param:user_def:date:data_inicio@
  and @where:security:project:inv.id@
  and @where:param:user_def:integer:hg_row_id@ is null


  union all
  select
  null inv_id,
  sts.id sts_id,
  to_char(cop_report_date, 'dd/mm/yyyy') titulo,
  2 tipo_inv,
  null gerente,
  null data_termino,
  cop_report_date data_rel_status,
  decode(sts.cop_report_status, 'DRAFT', 'Rascunho', 'Final') status_report,
  sts.cop_schedule_status,
  sts.cop_scope_status,
  sts.cop_cost_eft_status,
  (sts.cop_schedule_status+sts.cop_scope_status+sts.cop_cost_eft_status) overall_status,
  0 link_inv,
  1 link_sts,
  null hg_has_children
  from 
  odf_ca_cop_prj_statusrpt sts
  where 
  cop_report_date >= @where:param:user_def:date:data_inicio@ and cop_report_date <= @where:param:user_def:date:data_fim@
  and sts.odf_parent_id = @where:param:user_def:integer:hg_row_id@
  order by data_rel_status desc
)
WHERE @FILTER@
and @where:param:user_def:string:titulo:filtro_nome@

Outcomes