AnsweredAssumed Answered

Passing first level input parameters in drill down portlets

Question asked by rg2027 on Nov 17, 2010
Latest reply on Nov 18, 2010 by rg2027
HI All,

Could some one please help me with the below requirement.

I need to create drill down portlet

In the first level I have 3 user defined fields based on which the query1 is formed and data perfectly displayed

For the second level I need to have list of projects for each of the selected OBS.
This is normally easy to do but in my requirement I need to display those projects which also satisfy 3 input parameters at the first level



1st Query

Select @SELECT:DIM:USER_DEF:IMPLIED:PRJ:obsUnits.id:ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:obsUnits.NAME:OBSName@,
@SELECT:METRIC:USER_DEF:IMPLIED:count(*):count@
FROM
niku.inv_investments inv ,
niku.ODF_CA_PROJECT odfproj , --niku.srm_projects srmproj,
niku.PRJ_OBS_ASSOCIATIONS obsAsn ,
niku.PRJ_OBS_UNITS obsUnits ,
niku.PRJ_OBS_TYPES obsTypes,
niku.PRJ_OBS_OBJECT_TYPES obsObjTypes ,
niku.CMN_LOOKUPS lukup,
niku.CMN_CAPTIONS_NLS captions
WHERE
odfproj.id = inv.ID
AND obsAsn.RECORD_ID = odfproj.ID
AND obsUnits.ID = obsAsn.UNIT_ID
AND obsTypes.ID = obsUnits.TYPE_ID
AND obsObjTypes.TYPE_ID = obsTypes.ID
AND obsObjTypes.ID = 5000001
and odfproj.actualreleasedate between (@WHERE:PARAM:USER_DEF:DATE:FROM_DATE@)[size=4][size]
and [size=3](@WHERE:PARAM:USER_DEF:DATE:TO_DATE@)[size]
and lukup.LOOKUP_TYPE='tf_requesttype'
AND lukup.LOOKUP_CODE = odfproj.tf_requesttype
AND lukup.ID = captions.PK_ID
AND captions.TABLE_NAME = 'CMN_LOOKUPS'
AND captions.LANGUAGE_CODE = 'en'
and captions.name like [size=3]@WHERE:PARAM:USER_DEF:STRING:Request_tYPE@[size]
AND @FILTER@
GROUP BY obsUnits.NAME,obsUnits.id


In the second page I have created 4 link parameters

LnK_OBS_ID
LnK_FromDate
LnK_ToDate
LnK_RequestType

In order to have drill down , in my query1 I need to map the available fields with the link parameters.

Available fields as it can be observed in above query1 are id,name and count

I have mapped id to LnK_OBS_ID of the link parameters

How do I associate other data(which is not available from query1) to the remaining link parameters?????( LnK_FromDate,
LnK_ToDate,
LnK_RequestType)

I tried using the input parameter is select query but I am not able to generate valid NSQL

eg :

Select @SELECT:DIM:USER_DEF:IMPLIED:PRJ:OBS_Details.id:ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:OBS_Details.obsName:OBSName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:(@WHERE:PARAM:USER_DEF:DATE:FROM_DATE@):fromDate@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:(@WHERE:PARAM:USER_DEF:DATE:TO_DATE@):toDate@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:(@WHERE:PARAM:USER_DEF:STRING:Request_tYPE@):RequestType@,
@SELECT:METRIC:USER_DEF:IMPLIED:count(*):count@

Please give me some ideas for meeting this requirement

Outcomes