Clarity PPM

Expand all | Collapse all

Grid Portlett Not Displaying Columns in PPM

Jump to Best Answer
  • 1.  Grid Portlett Not Displaying Columns in PPM

    Posted 06-08-2017 06:36 PM

    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



  • 2.  Re: Grid Portlett Not Displaying Columns in PPM

    Posted 06-09-2017 01:31 AM

    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



  • 3.  Re: Grid Portlett Not Displaying Columns in PPM

    Posted 06-09-2017 03:45 AM

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



  • 4.  Re: Grid Portlett Not Displaying Columns in PPM

    Posted 06-09-2017 09:44 AM

    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 Assignments - Working Portlet
    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
    My Project assignment


  • 5.  Re: Grid Portlett Not Displaying Columns in PPM
    Best Answer

    Posted 06-09-2017 11:57 AM

    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.



  • 6.  Re: Grid Portlett Not Displaying Columns in PPM

    Posted 06-09-2017 12:12 PM

    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) .



  • 7.  Re: Grid Portlett Not Displaying Columns in PPM

    Posted 06-09-2017 12:21 PM

    I think I may have found the answer - Thanks everyone