Need Help with Drill-Down Portlet

[Pat Douglas]

I'm working on creating a drill-down portlet. The first level is used to display actuals by project and by date. I want the drill down to then show the resources who booked time to that project for the dates that were selected. So, by clicking on either the project code or the actuals number - I want to resulting pop-up window to already be filtered by project and the date range selected.

The NSQL queries I am using are below:

Main Portlet (Actuals by Project):
SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:z.id:id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.code:code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.name:name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.is_active:is_active@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.manager_id:mgr_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.MANAGER:manager@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.tda_parent_initiativ:plat_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.PLATFORM:platform@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.tda_parent_program:prog_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.PROGRAM:program@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.GvR:GvR@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.tda_corp_theme:ct_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.CORP_THEME:corp_theme@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.ENT_PRG:ent_prg@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.tda_sponsor_org:fu_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.FUN_UNIT:fu@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.tda_client_portolio:cp_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.CLI_PORT:cli_port@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.tda_soc_member:soc_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.SOC_NAME:soc_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.tda_sponsor:sp_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.SPO_NAME:spo_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.tda_tech_del_owner:sdel_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.SDEL:sdel_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.tda_program_director:pd_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.PROG_DIR:pd_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.ACTUALS:ACTUALS@

FROM
(select i.id, i.code, i.name, CASE WHEN i.is_active = '0' THEN 'No' ELSE 'Yes' END IS_ACTIVE, i.manager_id, mr.full_name MANAGER, oci.tda_parent_initiativ, pl.name PLATFORM, oci.tda_parent_program, pr.name PROGRAM,
oci.tda_gvr GvR, oci.tda_corp_theme, ctl.name CORP_THEME, CASE WHEN oci.tda_enterprise_prg = '0' THEN 'No' ELSE 'Yes' END ENT_PRG,
oci.tda_sponsor_org, ful.name FUN_UNIT, oci.tda_client_portolio, port.name CLI_PORT, oci.tda_soc_member, soc.full_name SOC_NAME, oci.tda_sponsor, spo.full_name SPO_NAME,
oci.tda_tech_del_owner, sdel.full_name SDEL, oci.tda_program_director, pd.full_name PROG_DIR, SUM(s.slice) ACTUALS
FROM PRJ_BLB_SLICES s
INNER JOIN prtimeentry te ON te.prid = s.prj_object_id
INNER JOIN prtimesheet ts ON ts.prid = te.prtimesheetid
INNER JOIN prassignment A ON a.prid = te.prassignmentid
INNER JOIN prtask t ON a.prtaskid = t.prid
INNER JOIN inv_investments i ON t.prprojectid = i.id
INNER JOIN INV_PROJECTS INVP ON INVP.PRID = i.id
INNER JOIN odf_ca_inv oci on oci.id = i.id
left join srm_resources mr on mr.user_id = i.manager_id
left join odf_ca_tda_platform pl on pl.id = oci.tda_parent_initiativ
left join inv_investments pr on pr.id = oci.tda_parent_program
left join cmn_lookups_v ctl on ctl.lookup_code = oci.tda_corp_theme
left join cmn_lookups_v ful on ful.lookup_code = oci.tda_sponsor_org
left join odf_ca_tda_portfolio port on port.pid = oci.tda_client_portolio
left join srm_resources soc on soc.id = oci.tda_soc_member
left join srm_resources spo on spo.id = oci.tda_sponsor
left join srm_resources sdel on sdel.id = oci.tda_tech_del_owner
left join srm_resources pd on pd.id = oci.tda_program_director
where INVP.IS_TEMPLATE = '0'
and INVP.is_program = '0'
and i.name not like 'NON-WORKING%'
and ctl.language_code = 'en'
and ful.language_code = 'en'
and s.SLICE_REQUEST_ID = '55555'
and ts.prstatus IN ('1','3','4')
and to_date(to_char(s.slice_date,'yyyy-mm-dd')) >= @WHERE:PARAM:USER_DEF:DATE:START_DATE@
and to_date(to_char(s.slice_date,'yyyy-mm-dd')) <= @WHERE:PARAM:USER_DEF:DATE:FINISH_DATE@
group by i.id, i.code, i.name, i.is_active, i.manager_id, mr.full_name, oci.tda_parent_initiativ, pl.name, oci.tda_parent_program, pr.name, oci.tda_gvr, oci.tda_corp_theme,
ctl.name, oci.tda_enterprise_prg, oci.tda_sponsor_org, ful.name, oci.tda_client_portolio, port.name, oci.tda_soc_member, soc.full_name, oci.tda_sponsor, spo.full_name,
oci.tda_tech_del_owner, sdel.full_name, oci.tda_program_director, pd.full_name)z

WHERE @FILTER@
HAVING @HAVING_FILTER@

Drill-Down:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:z.RID:RID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.RES_NAME:RES_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.code:code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.name:name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.TASK_NAME:TASK_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.slice_date:slice_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:z.ACTUALS:ACTUALS@

From
(select rownum RID, i.id, r.full_name RES_NAME, i.code, i.name, t.prname TASK_NAME, s.slice_date, s.slice ACTUALS
FROM PRJ_BLB_SLICES s
INNER JOIN prtimeentry te ON te.prid = s.prj_object_id
INNER JOIN prtimesheet ts ON ts.prid = te.prtimesheetid
INNER JOIN prassignment A ON a.prid = te.prassignmentid
INNER JOIN prtask t ON a.prtaskid = t.prid
INNER JOIN inv_investments i ON t.prprojectid = i.id
INNER JOIN INV_PROJECTS INVP ON INVP.PRID = i.id
INNER JOIN odf_ca_inv oci on oci.id = i.id
left join srm_resources r on r.id = ts.prresourceid
where INVP.IS_TEMPLATE = '0'
and INVP.is_program = '0'
and i.name not like 'NON-WORKING%'
and s.SLICE_REQUEST_ID = '55555'
and ts.prstatus IN ('1','3','4'))z

WHERE @WHERE:SECURITY:PROJECT:i.code@
AND @FILTER@

------------------------------
Senior Systems Admin
TD Ameritrade
------------------------------

[David Morton]

Your second (drill-down) query needs to take parameters of the form  @WHERE:PARAM:XML:STRING:/data/my_param1/@value@ 

You then place your dill down portlet on its own linkable portlet-page* and define the link-parameters on the page to match the ones in your NSQL ("my_param1" etc)

You then go back to the query for your first portlet and make sure that the query delivers the correct parameters and then add a "linking" section, which is where you specify the portlet-page that you set up earlier and map the query's attributes to the link-parameters on that page. 

Then go back to your first portlet and add the link to the column(s) you want to drill from.

(*you will notice when you eventually look at that portlet-page that the parameters will appear on the URL of the page, that is what the WHERE:PARAM:XML:STRING thing is doing ; reading values from the page URL )

--

I'll see if I can find a detailed/worked example of all this ; I'm sure that there must be one on these boards...

EDIT - if you search for "drill" or "drill down" you might find stuff, I could not immediately find the document/page I was thinking of.
and if you post where you are getting "stuck" then someone may be able to help....