Project Cycle Time Analysis portlet

[MatthewPalicki]

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?

[navzjoshi00]

1.059 Project Cycle Time Analysis

Type:

 

Chart Portlet

Source:

 

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

 

NJ

[navzjoshi00]

Pdf attached

 

NJ

Attachment(s)

Clarity v13 Add-in Contents Guide.pdf   4.29 MB 1 version

[MatthewPalicki]

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?

[navzjoshi00]

For that you need to analyze the query

 

NJ

[navzjoshi00]

This is the query used -

 

SELECT DISTINCT

       i.id project_int_id,  

       i.name project_name,      

       CASE WHEN LENGTH(i.name) > 16 THEN @SUBSTR@(i.name,1,16) @+@ '...' ELSE i.name 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 = i.id

INNER  JOIN  inv_projects prj

       ON    prj.PrId = i.id

INNER  JOIN  odf_ca_project ProjAttr

       ON    ProjAttr.id = i.id

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  i.id = 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    i.id = @NVL@(:param_/data/id/@value,i.id)

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    i.id in (select object_instance_id from odfsec_project_v2 where user_id = 1)

 

NJ