• 1.  Project Cycle Time Analysis portlet

    Posted 11-18-2014 10:03 AM

    This portlet from the PMO accelerator appears to provide the exact business process metrics we need to track a key process.


    However, for some reason it always returns "no results" when I try to use it for my desired template/methodology. It works if i select some of my other custom templates, but of course, it does not work for the one that is most important to me.


    Anyone have any idea how to get this portlet to correctly register my desired template?

  • 2.  Re: Project Cycle Time Analysis portlet

    Posted 11-18-2014 11:53 PM

    1.059 Project Cycle Time Analysis



    Chart Portlet



    PMO Accelerator v3.0

    Query Used:


    Project Cycle Time Analysis


    The Project Cycle Time Analysis portlet displays the number of days spent in each project phase illustrated as a bar graph. Use this portlet to compare time spent in each phase for projects based on the same methodology. The project templates installed with the PMO Accelerator add-in are considered project methodologies and must be the basis of projects you wish to analyze with this portlet. Security on Project. Excludes templates and inactive projects.


    I've attached the pdf for reference



  • 3.  Re: Project Cycle Time Analysis portlet

    Posted 11-18-2014 11:54 PM
      |   view attached

    Pdf attached




  • 4.  Re: Project Cycle Time Analysis portlet

    Posted 11-19-2014 07:57 AM

    Thank you. I've read this documentation and it does not explain the situation. The portlet works with some of my customer-provided templates, but not others. ALL of my customer-provided templates appear in the filter options, so all are correctly recognized in the lookup for that attribute. Doesn't make any sense why they would provide a portlet which only works with their vendor developed templates, and does not work with any customer developed templates. But even moreso - does not make sense that it works with some of my templates but not others. What is required for the portlet to recognize the template? Is it something in the query structure for that portlet?

  • 5.  Re: Project Cycle Time Analysis portlet

    Posted 11-19-2014 10:04 AM

    For that you need to analyze the query



  • 6.  Re: Project Cycle Time Analysis portlet

    Posted 11-19-2014 10:21 AM

    This is the query used -





           CASE WHEN LENGTH( > 16 THEN @SUBSTR@(,1,16) @+@ '...' ELSE END project_short_name,      

           r.user_id Manager_id,

           r.full_name Manager_Name,

           CASE WHEN i.STATUS IN (1,5,8) THEN 1 ELSE 0 END is_approved,

           ProjAttr.obj_Methodology Methodology_ID,      

           ProjAttr.obj_request_type Project_Type_ID,      

           prj.is_template Is_Template,      

           @NVL@(PhaseMstr.Task_Code,'Undefined') Phase_ID,      

           @DBUSER@.CMN_TO_CHAR_FCT(CASE WHEN prj.is_template = 1 THEN (((10000 - PhaseMstr.Task_sequence) * 10)+0) ELSE (((10000 - PhaseMstr.Task_sequence) * 10)+100000)  END) Phase_ID_Sort,

           @NVL@(PhaseMstr.Task_Name,'Undefined') Phase_Name,

           PhaseMstr.Task_Sequence Phase_Seq,      

           taskTable.PrStart Start_Date,

           @UPPER@(i.odf_object_code) investment_code_upper,

           @DBUSER@.cop_calc_finish_time_fct(taskTable.PrFinish) Finish_Date,

           @DBUSER@.CMN_DATEDIFF_FCT(@DBUSER@.cop_calc_finish_fct(taskTable.PrFinish),(@DBUSER@.COP_DATE_TRUNC_FCT(taskTable.PrStart)+1)) duration_days

    FROM   cop_phase_rollup_v PhaseMstr

    INNER  JOIN  inv_investments i

           ON    PhaseMstr.Project_Id =

    INNER  JOIN  inv_projects prj

           ON    prj.PrId =

    INNER  JOIN  odf_ca_project ProjAttr

           ON =

    INNER  JOIN  PrTask taskTable

           ON    taskTable.PrId = PhaseMstr.task_Id

           AND   taskTable.PrProjectId = PhaseMstr.Project_Id

           AND   taskTable.prwbslevel = 1

    LEFT   OUTER JOIN prj_obs_associations assoc

                 ON   assoc.table_name = 'SRM_PROJECTS'

                 AND = assoc.record_id

    LEFT   OUTER JOIN prj_obs_units_flat flat

                 ON   assoc.unit_id = flat.unit_id

    LEFT   OUTER JOIN srm_resources r

                 ON   r.user_id = i.manager_id

    WHERE  i.odf_object_code = 'project'

    AND = @NVL@(:param_/data/id/@value,

    AND    i.is_active <> 0 

    AND    prj.is_program <> 1 

    AND    prj.is_template <> 1

    AND    PhaseMstr.IsTask = 0

    AND    PhaseMstr.ismilestone = 0

    AND    taskTable.PrFinish <> taskTable.PrStart

    AND    ((flat.branch_unit_id IS NULL) OR (flat.branch_unit_id = flat.branch_unit_id))

    AND in (select object_instance_id from odfsec_project_v2 where user_id = 1)