Grid Portlett Not Displaying Columns in PPM

[Legacy User]

Hi - I'm rather new to creating portlets so bear with me. I created a mirror image of the My Assignments Portlet within PPM, but added a custom field in from another table. The query worked and I can see all of the fields within the portlet creation area.

 

However, after creating the portlet and adding a tab to my homepage to view it I am not seeing the actual portlet (the data doesn't display), but the filter on the portlet is showing the fields.

 

I am currently assigned to a project as well and do pull into the My Assignments Portlet for it, which makes this more baffling.

 

Am I missing something here, feel like this should be straight forward.

 

Appreciate any assistance.

 

Thanks!

Greg

[navzjoshi00]

Happy Friday

 

Is there any error on the app-ca.log?

 

Is there any condition that is not satisfied within the query ? Try running the sql in the db and check if you get results there

 

NJ

[Deactivated User]

Could you share the query and a screenshot of the portlet not displaying data?

[Legacy User]

Thanks for responding urmas / navz! So the My Assignments query that pulls in values into the portlet does not work in Oracle either (after switching to preview tab and using the SQL there). I'll go ahead and attach the code / screenshots. The first query is the working portlet / query, while second is not working (pictures are attached below each query). On the second query I bolded the values that I added.

 

My Assignments - Working Portlet:

 

SELECT @SELECT:DIM:USER_DEF:IMPLIED:TASK:pra.prid:assign_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prid:task_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prname:task_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:spr.id:project_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:mgr.full_name:project_manager@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:spr.unique_name:project_code@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:spr.is_template:is_template@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:spr.is_active:is_active@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:prj.prpriority:project_priority@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:mgr.id:project_manager_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.pristask:is_task@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.priskey:is_key@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:spr.name:project_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prPctComplete:task_percent_complete@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prchargecodeid:charge_code_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prStatus:task_status@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prStart:task_start@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prFinish:task_finish@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:CASE WHEN pra.prStart  IS NULL THEN tsk.prStart  ELSE pra.prStart  END:assign_start@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:CASE WHEN pra.prFinish IS NULL THEN tsk.prFinish ELSE pra.prFinish END:assign_finish@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prduration:task_duration@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:res.full_name:resource_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:res.full_name:task_owner_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:res.id:resource_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:NVL(pra.prpendactsum,0)/3600:pending_actuals@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:NVL(pra.practsum,0)/3600:actuals@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:NVL(pra.prestsum,0)/3600:etc@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:(NVL(pra.prestsum,0)/3600)+(NVL(pra.practsum,0)/3600)+(NVL(pra.prpendactsum,0)/3600):total_effort@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK: CASE WHEN ((NVL(pra.prestsum,0)/3600)+(NVL(pra.practsum,0)/3600)+(NVL(pra.prpendactsum,0)/3600)) = 0 THEN 0
                                                    ELSE (((NVL(pra.practsum,0)/3600))/((NVL(pra.prestsum,0)/3600)+(NVL(pra.practsum,0)/3600)+
                                                         (NVL(pra.prpendactsum,0)/3600))) END:percent_expended@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:pcc.prname:charge_code_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:t_statuslu.name:task_status_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:t_statuslu.lookup_code:task_status_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:t_statuslu.lookup_enum:task_status_enum@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:a_statuslu.lookup_enum:assign_status_enum@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:a_statuslu.name:assign_status_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:a_statuslu.lookup_code:assign_status_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:base.tot_eff:baseline_effort@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:base.finish_date:baseline_finish@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:CASE WHEN (pra.prestsum/3600 + pra.practsum/3600) < base.tot_eff THEN 0
                                                   ELSE base.tot_eff - (pra.prestsum/3600 + pra.practsum/3600) END:eac_past_base@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:CASE WHEN base.tot_eff IS NULL THEN 100
                                                   ELSE (base.tot_eff - (pra.prestsum/3600 + pra.practsum/3600))/base.tot_eff * 100 END:effort_variance@,
       @SELECT:DIM:USER_DEF:IMPLIED:PERIOD:s.slice_date:TP_START_DATE@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PERIOD:TO_CHAR(s.slice_date,'yyyy-mm-dd'):TP_START_DISPLAY@,
       @SELECT:METRIC:USER_DEF:IMPLIED:SUM(s.slice):hours@

  FROM prassignment pra
  JOIN prtask tsk
     ON tsk.prid = pra.prtaskid
  JOIN srm_projects spr
     ON spr.id = tsk.prprojectid
  JOIN odf_ca_task oct
     ON tsk.prid = oct.id
  JOIN inv_investments inv
     ON inv.id = spr.id
  JOIN odf_ca_inv oci
     ON oci.id = spr.id
  JOIN srm_resources res
     ON pra.prresourceid = res.id
  JOIN prj_projects prj
     ON spr.id = prj.prid
  LEFT OUTER JOIN srm_resources mgr
     ON mgr.user_id = prj.manager_id
  LEFT OUTER JOIN prchargecode pcc
     ON pcc.prid = tsk.prchargecodeid
  LEFT OUTER JOIN
       (SELECT object_id id, finish_date,
               usage_sum/3600 tot_eff
          FROM prj_baseline_details
         WHERE object_type = 'ASSIGNMENT'
               AND is_current = 1) base
     ON base.id = pra.prid
  JOIN (SELECT lu.lookup_code,
               lu.lookup_enum,
               lu.name
          FROM cmn_lookups_v lu
         WHERE lu.language_code = 'en'
               AND lookup_type = 'prTaskStatus') t_statuslu
     ON tsk.prStatus = t_statuslu.lookup_enum
  JOIN (SELECT lu.lookup_code,
               lu.lookup_enum,
               lu.name
          FROM cmn_lookups_v lu
         WHERE lu.language_code = 'en'
               AND lookup_type = 'prTaskStatus') a_statuslu
     ON pra.prstatus = a_statuslu.lookup_enum
  JOIN prj_blb_slices s
     ON pra.prid = s.prj_object_id
       AND s.slice > 0
       AND s.slice_request_id = (SELECT ID FROM PRJ_BLB_SLICEREQUESTS WHERE REQUEST_NAME=@WHERE:PARAM:USER_DEF:STRING:slice_by_string@ || 'RESOURCEESTCURVE'  )
       AND s.slice_date BETWEEN @WHERE:PARAM:user_def:DATE:startdate@ AND @WHERE:PARAM:user_def:DATE:enddate@

 WHERE inv.is_active = 1
       AND pra.prestsum > 0
       AND res.user_id = @WHERE:PARAM:USER_ID@
       AND @FILTER@
GROUP BY
       pra.prid,
       tsk.prid,
       tsk.prname,
       spr.id,
       mgr.full_name,
       spr.unique_name,
       spr.is_template,
       spr.is_active,
       prj.prpriority,
       mgr.id,
       tsk.pristask,
       tsk.priskey,
       spr.name,
       tsk.prPctComplete,
       tsk.prchargecodeid,
       tsk.prStatus,
       tsk.prStart,
       tsk.prFinish,
       pra.prStart,
       pra.prFinish,
       tsk.prduration,
       res.full_name,
       res.full_name,
       res.id,
       NVL(pra.prpendactsum,0)/3600,
       NVL(pra.practsum,0)/3600,
       NVL(pra.prestsum,0)/3600,
       pcc.prname,
       t_statuslu.name,
       t_statuslu.lookup_code,
       t_statuslu.lookup_enum,
       a_statuslu.lookup_enum,
       a_statuslu.name,
       a_statuslu.lookup_code,
       base.tot_eff,
       base.finish_date,
       s.slice_date,
       CASE WHEN pra.prStart  IS NULL THEN tsk.prStart  ELSE pra.prStart  END,
       CASE WHEN pra.prFinish IS NULL THEN tsk.prFinish ELSE pra.prFinish END,
       CASE WHEN ((NVL(pra.prestsum,0)/3600)+(NVL(pra.practsum,0)/3600)+(NVL(pra.prpendactsum,0)/3600)) = 0 THEN 0
                                                    ELSE (((NVL(pra.practsum,0)/3600))/((NVL(pra.prestsum,0)/3600)+(NVL(pra.practsum,0)/3600)+
                                                         (NVL(pra.prpendactsum,0)/3600))) END,
       CASE WHEN (pra.prestsum/3600 + pra.practsum/3600) < base.tot_eff THEN 0
                                                   ELSE base.tot_eff - (pra.prestsum/3600 + pra.practsum/3600) END,
       CASE WHEN base.tot_eff IS NULL THEN 100
                                                   ELSE (base.tot_eff - (pra.prestsum/3600 + pra.practsum/3600))/base.tot_eff * 100 END

My Project Assignments - Not working porlet:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:TASK:pra.prid:assign_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prid:task_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prname:task_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:spr.id:project_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:mgr.full_name:project_manager@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:spr.unique_name:project_code@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:spr.is_template:is_template@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:spr.is_active:is_active@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:prj.prpriority:project_priority@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:mgr.id:project_manager_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.pristask:is_task@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.priskey:is_key@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:spr.name:project_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prPctComplete:task_percent_complete@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prchargecodeid:charge_code_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prStatus:task_status@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prStart:task_start@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prFinish:task_finish@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:CASE WHEN pra.prStart  IS NULL THEN tsk.prStart  ELSE pra.prStart  END:assign_start@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:CASE WHEN pra.prFinish IS NULL THEN tsk.prFinish ELSE pra.prFinish END:assign_finish@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:tsk.prduration:task_duration@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:res.full_name:resource_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:res.full_name:task_owner_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:res.id:resource_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:NVL(pra.prpendactsum,0)/3600:pending_actuals@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:NVL(pra.practsum,0)/3600:actuals@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:NVL(pra.prestsum,0)/3600:etc@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:(NVL(pra.prestsum,0)/3600)+(NVL(pra.practsum,0)/3600)+(NVL(pra.prpendactsum,0)/3600):total_effort@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK: CASE WHEN ((NVL(pra.prestsum,0)/3600)+(NVL(pra.practsum,0)/3600)+(NVL(pra.prpendactsum,0)/3600)) = 0 THEN 0
                                                    ELSE (((NVL(pra.practsum,0)/3600))/((NVL(pra.prestsum,0)/3600)+(NVL(pra.practsum,0)/3600)+
                                                         (NVL(pra.prpendactsum,0)/3600))) END:percent_expended@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:pcc.prname:charge_code_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:t_statuslu.name:task_status_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:t_statuslu.lookup_code:task_status_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:t_statuslu.lookup_enum:task_status_enum@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:a_statuslu.lookup_enum:assign_status_enum@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:a_statuslu.name:assign_status_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:a_statuslu.lookup_code:assign_status_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:base.tot_eff:baseline_effort@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:base.finish_date:baseline_finish@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:M.CODE:Mnemonic@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:CASE WHEN (pra.prestsum/3600 + pra.practsum/3600) < base.tot_eff THEN 0
                                                   ELSE base.tot_eff - (pra.prestsum/3600 + pra.practsum/3600) END:eac_past_base@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TASK:CASE WHEN base.tot_eff IS NULL THEN 100
                                                   ELSE (base.tot_eff - (pra.prestsum/3600 + pra.practsum/3600))/base.tot_eff * 100 END:effort_variance@,
       @SELECT:DIM:USER_DEF:IMPLIED:PERIOD:s.slice_date:TP_START_DATE@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PERIOD:TO_CHAR(s.slice_date,'yyyy-mm-dd'):TP_START_DISPLAY@,
       @SELECT:METRIC:USER_DEF:IMPLIED:SUM(s.slice):hours@

  FROM prassignment pra
  JOIN prtask tsk
     ON tsk.prid = pra.prtaskid
  JOIN srm_projects spr
     ON spr.id = tsk.prprojectid
  JOIN odf_ca_task oct
     ON tsk.prid = oct.id
  JOIN inv_investments inv
     ON inv.id = spr.id
  JOIN odf_ca_c_client M
     ON M.ID = tsk.prid
  JOIN odf_ca_inv oci
     ON oci.id = spr.id
  JOIN srm_resources res
     ON pra.prresourceid = res.id
  JOIN prj_projects prj
     ON spr.id = prj.prid
  LEFT OUTER JOIN srm_resources mgr
     ON mgr.user_id = prj.manager_id
  LEFT OUTER JOIN prchargecode pcc
     ON pcc.prid = tsk.prchargecodeid
  LEFT OUTER JOIN
       (SELECT object_id id, finish_date,
               usage_sum/3600 tot_eff
          FROM prj_baseline_details
         WHERE object_type = 'ASSIGNMENT'
               AND is_current = 1) base
     ON base.id = pra.prid
  JOIN (SELECT lu.lookup_code,
               lu.lookup_enum,
               lu.name
          FROM cmn_lookups_v lu
         WHERE lu.language_code = 'en'
               AND lookup_type = 'prTaskStatus') t_statuslu
     ON tsk.prStatus = t_statuslu.lookup_enum
  JOIN (SELECT lu.lookup_code,
               lu.lookup_enum,
               lu.name
          FROM cmn_lookups_v lu
         WHERE lu.language_code = 'en'
               AND lookup_type = 'prTaskStatus') a_statuslu
     ON pra.prstatus = a_statuslu.lookup_enum
  JOIN prj_blb_slices s
     ON pra.prid = s.prj_object_id
       AND s.slice > 0
       AND s.slice_request_id = (SELECT ID FROM PRJ_BLB_SLICEREQUESTS WHERE REQUEST_NAME=@WHERE:PARAM:USER_DEF:STRING:slice_by_string@ || 'RESOURCEESTCURVE'  )
       AND s.slice_date BETWEEN @WHERE:PARAM:user_def:DATE:startdate@ AND @WHERE:PARAM:user_def:DATE:enddate@

 WHERE inv.is_active = 1
       AND pra.prestsum > 0
       AND res.user_id = @WHERE:PARAM:USER_ID@
       AND @FILTER@
GROUP BY
       pra.prid,
       tsk.prid,
       tsk.prname,
       spr.id,
       mgr.full_name,
       spr.unique_name,
       spr.is_template,
       spr.is_active,
       prj.prpriority,
       mgr.id,
       tsk.pristask,
       tsk.priskey,
       spr.name,
       tsk.prPctComplete,
       tsk.prchargecodeid,
       tsk.prStatus,
       tsk.prStart,
       tsk.prFinish,
       pra.prStart,
       pra.prFinish,
       tsk.prduration,
       res.full_name,
       res.full_name,
       res.id,
       NVL(pra.prpendactsum,0)/3600,
       NVL(pra.practsum,0)/3600,
       NVL(pra.prestsum,0)/3600,
       pcc.prname,
       t_statuslu.name,
       t_statuslu.lookup_code,
       t_statuslu.lookup_enum,
       a_statuslu.lookup_enum,
       a_statuslu.name,
       a_statuslu.lookup_code,
       base.tot_eff,
       base.finish_date,
       s.slice_date,
       M.CODE,
       CASE WHEN pra.prStart  IS NULL THEN tsk.prStart  ELSE pra.prStart  END,
       CASE WHEN pra.prFinish IS NULL THEN tsk.prFinish ELSE pra.prFinish END,
       CASE WHEN ((NVL(pra.prestsum,0)/3600)+(NVL(pra.practsum,0)/3600)+(NVL(pra.prpendactsum,0)/3600)) = 0 THEN 0
                                                    ELSE (((NVL(pra.practsum,0)/3600))/((NVL(pra.prestsum,0)/3600)+(NVL(pra.practsum,0)/3600)+
                                                         (NVL(pra.prpendactsum,0)/3600))) END,
       CASE WHEN (pra.prestsum/3600 + pra.practsum/3600) < base.tot_eff THEN 0
                                                   ELSE base.tot_eff - (pra.prestsum/3600 + pra.practsum/3600) END,
       CASE WHEN base.tot_eff IS NULL THEN 100
                                                   ELSE (base.tot_eff - (pra.prestsum/3600 + pra.practsum/3600))/base.tot_eff * 100 END

[Jeanne Gaskill]

Hi Greg,

 

I took a look at the join you added.  I don't think that will work.

 

JOIN odf_ca_c_client M
     ON M.ID = tsk.prid

 

The id in the odf_ca_c_client is the internal id for each object instance of that type that is generated as it is created.  The prid in the prtask table is the internal id for tasks from the odf_ca_tasks table.  It doesn't seem logical to me that these would match.  Is there a different field on one of these two tables that is a key to the other one?

 

Sincerely yours,

 

Jeanne Gaskill

Senior Support Engineer

CA Technologies, Inc.

[Legacy User]

Ahh you are right Jeanne. The issue right now is that field comes from a custom table and those tables don't really sync with anything that I can see (ID is the only "Key" number field that I see) .

[Legacy User]

I think I may have found the answer - Thanks everyone