AnsweredAssumed Answered

Drill-down portlet not working as expected

Question asked by whole_milk on Jan 13, 2014
Latest reply on Jan 13, 2014 by Dave

I am 100% sure I am missing something somewhere. Calling all experts. Let me break my work down.

My expected output:
Portlet 1: Pie chart on top with one yes/no parameter (divides the pie by tech group)
Portlet 2: Somehow need to pass the yes/no param and the tech group from the pie to show a list of projects with their budgets

My actual output:
Pie works of course.
I am able to click and open the sub-portlet's portlet page, but it returns nothing.


What I did?:

NSQL1:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:DASH_STATUS:PRJ_COUNT.rowkey:rowkey@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DASH_STATUS:PRJ_COUNT.port_tech:port_tech@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DASH_STATUS:PRJ_COUNT.port_tech_code:port_tech_code@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DASH_STATUS:PRJ_COUNT.tot_count:tot_count@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DASH_STATUS:PARM_YESNO:yes_no@
FROM
(
SELECT tot_count||rownum rowkey, port_tech, port_tech_code, tot_count, @WHERE:PARAM:USER_DEF:STRING:yesno@ parm_yesno
FROM
(
select port_tech, port_tech_code, count(*) as tot_count
from 
(
select inv.id, inv.code, inv.name, lkp.name port_tech, lkp.lookup_code port_tech_code,
decode((select 1 
from inv_investments invest
where exists (
select * from fin_plans
where object_id = invest.id
and plan_type_code = 'BUDGET'
and status_code = 'APPROVED'
and is_plan_of_record = 1
) and invest.id = inv.id),1,'YES','NO') budget
from inv_investments inv,
odf_ca_project ocp,
odf_ca_pfm_portfolio pfm,
(select v.lookup_code, v.name from cmn_lookups_v v
where v.language_code = 'en'
  and v.lookup_type = 'PORTFOLIO_TECH_DEPT'
  and v.partition_code = 'cid'
  and v.is_active = 1) lkp
where inv.id = ocp.id
  and ocp.cid_port_name = pfm.id
  and lkp.lookup_code = pfm.tech_dept
  and pfm.cid_portfolio_type in ('domain','portfolio')
  and inv.is_active = 1
)
WHERE ((@WHERE:PARAM:USER_DEF:STRING:yesno@ IS NULL) OR (budget = @WHERE:PARAM:USER_DEF:STRING:yesno@))
group by port_tech, port_tech_code
) PORT_COUNT
) PRJ_COUNT
WHERE @FILTER@
 
NSQL2:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:DRILLER:COMP_ACT.rowkey:rowkey@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.port_name:port_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.inv_code:inv_code@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.inv_name:inv_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.port_tech:port_tech@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.port_tech_desc:port_tech_desc@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.project_type:project_type@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.budget:budget@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.manager_name:manager_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.sch_start:sch_start@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.sch_finish:sch_finish@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.perc_comp:perc_comp@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.stage_code:stage_code@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.status_desc:status_desc@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.budget_amt:budget_amt@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:DRILLER:COMP_ACT.budget_amt_curr:budget_amt_curr@
FROM
(
select inv_id||rownum rowkey,
port_name,
inv_id,
inv_code, inv_name, port_tech,
port_tech_desc,
project_type, budget,
manager_name, sch_start,
sch_finish,
perc_comp, stage_code,
status_desc, budget_amt, to_char(budget_amt,'$9,999,999,999.00') budget_amt_curr
FROM
(
select port.name port_name, inv.id inv_id, inv.code inv_code, inv.name inv_name, lkp.lookup_code port_tech, lkp.name port_tech_desc, prjtype.name project_type,
decode((select 1 
from inv_investments invest
where exists (
select * from fin_plans
where object_id = invest.id
and plan_type_code = 'BUDGET'
and status_code = 'APPROVED'
and is_plan_of_record = 1
) and invest.id = inv.id),1,'YES','NO') budget, 
(select full_name from srm_resources where user_id = inv.manager_id) manager_name,
to_char(inv.schedule_start,'MM/DD/YYYY') sch_start, to_char(inv.schedule_finish,'MM/DD/YYYY') sch_finish,
round(ip.percent_complete*100,2) perc_comp,
inv.stage_code,
prjstatus.name status_desc,
decode((select 1 
from inv_investments invest
where exists (
select * from fin_plans
where object_id = invest.id
and plan_type_code = 'BUDGET'
and status_code = 'APPROVED'
and is_plan_of_record = 1
) and invest.id = inv.id),1,(select sum(total_cost) from
        (select fin.object_id, round(findet.total_cost,0) total_cost
        from fin_plans fin, fin_cost_plan_details findet
        where fin.id = findet.plan_id
          and fin.object_code = 'project'
          and fin.is_plan_of_record = 1
          and status_code = 'APPROVED'
          and plan_type_code = 'BUDGET') where object_id = inv.id),0) budget_amt
from inv_investments inv,
inv_projects ip,
odf_ca_project ocp,
odf_ca_pfm_portfolio pfm,
pfm_portfolios port,
(select v.lookup_code, v.name from cmn_lookups_v v
where v.language_code = 'en'
  and v.lookup_type = 'PORTFOLIO_TECH_DEPT'
  and v.partition_code = 'cid'
  and v.is_active = 1) lkp,
(select v.lookup_code, v.name from cmn_lookups_v v
where v.language_code = 'en'
  and v.lookup_type = 'OBJ_IDEA_PROJECT_TYPE'
  and v.partition_code = 'cid'
  and v.is_active = 1) prjtype,
(select v.lookup_enum, v.lookup_code, v.name from cmn_lookups_v v
where v.language_code = 'en'
  and v.lookup_type = 'INVESTMENT_OBJ_STATUS'
  and v.partition_code = 'NIKU.ROOT'
  and v.is_active = 1) prjstatus
where inv.id = ocp.id
  and inv.id = ip.prid
  and pfm.id = port.id
  and ocp.cid_port_name = pfm.id
  and lkp.lookup_code = pfm.tech_dept
  and prjtype.lookup_code = ocp.obj_request_type
  and prjstatus.lookup_enum = inv.status
  and pfm.cid_portfolio_type in ('domain','portfolio')
  and inv.is_active = 1
) WHERE port_tech = @WHERE:PARAM:USER_DEF:STRING:port_tech@
AND budget = @WHERE:PARAM:USER_DEF:STRING:yesno@
) COMP_ACT
WHERE @FILTER@

3. I created a portlet page to put portlet 2 on it. On this page, I created 2 link parameters to mirror the 2 params being passed down.

4. On query 1, in the linking tab, I linked columns 3 and 5 to the page link parameters created in step 3.
 
When I click on the pie, the following link is what I get, but nothing.
 
Thoughts?

Outcomes