AnsweredAssumed Answered

Dynamic Look up not showin up in Query Object

Question asked by deepakallscripts on Mar 12, 2014
Latest reply on Mar 12, 2014 by deepakallscripts

Hello,

I have Created a Dynamic lookup with following NSQL:

Select 
@SELECT:OBSGroups:Groups@
from
(
SELECT
DISTINCT obs.level4_name as [OBSGroups]
FROM
niku.prj_obs_associations obsAssoc ,
niku.nbi_dim_obs obs 
WHERE
obsAssoc.TABLE_NAME = 'SRM_RESOURCES' 
AND obsAssoc.unit_id = obs.obs_unit_id 
AND obs.obs_type_name = 'IS Organization' 
AND level4_name IS NOT NULL
)
 
I am creating Query Object with following Query :
 
Select 
@SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:newid():uid@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:Date:dt@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:ProjectID:ProjectID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:ProjectName:ProjectName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:prjIdentifier:prjIdentifier@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:RESOURCENAME:RESOURCENAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:ResourceRole:ResourceRole@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:Manager:Manager@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:Active:Active@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:TASK:TASK@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:OBSNAME:OBSNAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:TOTALHOURS:TOTALHOURS@ 
from (
  SELECT   
REPLACE(RIGHT(CONVERT(VARCHAR(9),DATEADD(month, DATEDIFF(month, 0, SLICE_DATE), 0),6),6),' ','-') as Date,
INV.CODE [PROJECTID],  
INV.NAME [PROJECTNAME],  
ISNULL(lookups.lookup_Code,'') [prjIdentifier],
ISNULL(RES.FULL_NAME,'') [RESOURCENAME],  
case when pres.prisrole = 0 then roles.full_name else res.full_name end [ResourceRole],
ISNULL(Manager.FULL_NAME,'') as [Manager],
CASE RES.IS_ACTIVE WHEN 1 THEN 'Yes' ELSE 'No' END [Active],
CAST(TSK.PRID AS nvarchar(10)) PRID,  
TSK.PRNAME [TASK],  
ROUND(sum(ISNULL(SLC.SLICE,0)),2) [TOTALHOURS] , 
obs.level4_name as [OBSNAME]
 
  FROM   
NIKU.INV_INVESTMENTS INV  
JOIN NIKU.PRTASK TSK ON TSK.PRPROJECTID = INV.ID  
JOIN NIKU.PRASSIGNMENT ASGN ON ASGN.PRTASKID = TSK.PRID  
JOIN NIKU.SRM_RESOURCES RES ON RES.ID = ASGN.PRRESOURCEID AND RES.ID IN (SELECT PRJRES.PRID FROM NIKU.PRJ_RESOURCES PRJRES WHERE PRJRES.PRPRIMARYROLEID IS NOT NULL AND PRISROLE = 0) 
Left OUTER JOIN NIKU.SRM_RESOURCES Manager ON res.Manager_ID = Manager.ID 
JOIN NIKU.PRTIMESHEET TS ON TS.PRRESOURCEID = RES.ID AND TS.PRSTATUS = 4  
JOIN NIKU.PRTIMEENTRY TE ON TE.PRTIMESHEETID = TS.PRID AND TE.PRASSIGNMENTID = ASGN.PRID  
JOIN NIKU.PRJ_BLB_SLICES SLC ON SLC.PRJ_OBJECT_ID = TE.PRID AND 
 
(SLICE_DATE between cast(CAST((@WHERE:PARAM:USER_DEF:STRING:Year@) AS varchar) + '-' + CAST((@WHERE:PARAM:USER_DEF:STRING:Month@) AS varchar) + '-01' AS DATETIME) and 
DATEADD(m,1,cast(CAST((@WHERE:PARAM:USER_DEF:STRING:Year@) AS varchar) + '-' + CAST((@WHERE:PARAM:USER_DEF:STRING:Month@) AS varchar) + '-01' AS DATETIME)) -1) 
 
JOIN NIKU.PRJ_BLB_SLICEREQUESTS SLCREQ ON SLCREQ.REQUEST_NAME = 'DAILYTIMEENTRIES' and SLCREQ.ID = SLC.SLICE_REQUEST_ID  
JOIN niku.odf_ca_project AS odf ON INV.id = odf.id
LEFT OUTER JOIN (SELECT lookup_code ,name  FROM niku.cmn_lookups_v WHERE lookup_type = 'ECL_PROJ_ID' AND language_code = 'en') lookups ON odf.project_ident = lookups.lookup_code
JOIN niku.prj_resources pres ON res.id = pres.prid AND pres.prtrackmode = 2 and pres.prisrole in (0, 1)
LEFT JOIN niku.srm_resources roles ON roles.id = pres.prprimaryroleid
JOIN NIKU.PRJ_OBS_ASSOCIATIONS OBSASSOC ON (RES.ID = OBSASSOC.RECORD_ID AND OBSASSOC.TABLE_NAME = 'SRM_RESOURCES') 
JOIN NIKU.NBI_DIM_OBS_FLAT OBSFL ON (OBSASSOC.UNIT_ID = OBSFL.CHILD_OBS_UNIT_ID)   
JOIN NIKU.NBI_DIM_OBS OBS ON (OBSFL.PARENT_OBS_UNIT_ID = OBS.OBS_UNIT_ID  AND OBSFL.OBS_TYPE_ID = 5000001)
 
  WHERE    
Slice_Date IS NOT NULL   and obs.level4_name IS NOT NULL
and obs.level4_name = @WHERE:PARAM:USER_DEF:INTEGER:OBS@
  GROUP BY   
INV.CODE,  
INV.NAME,  
lookups.lookup_Code,
RES.id,
RES.FULL_NAME,  
case when pres.prisrole = 0 then roles.full_name else res.full_name end,
Manager.FULL_NAME,
RES.IS_ACTIVE,
TSK.PRID,  
TSK.PRNAME,  
DATEADD(month, DATEDIFF(month, 0, SLICE_DATE), 0),
obs.level4_name
    ) t where @FILTER@ 
Order By 
RESOURCENAME 
 
 
Now on screen where we assign  lookup to parameters, I am not able to see my look up in list. (please see attached snapshot).
 
 
Any Suggestions ??
 
Thanks,

 

Attachments

Outcomes