PMO - Project Status Report Issues (639 Views)
Reply
Occasional Advisor
cABany
Posts: 15
Registered: ‎04-30-2012
Message 1 of 3 (639 Views)

PMO - Project Status Report Issues

We would like to start using this report however somewhere along the way we broke it (still not sure how..).

 

The report only seems to see a particular  project type... one that we no longer use. I'm not sure how it was ever "set" to view this project type. If it's not, then what it's doing is completely ignoring other project types... I say this because only one project out of twenty active ones is displayed when setting up the report...

 

Just to test the report, I ran it against the old project... The report failed. The message I received was "missing expression".

 

So there are two issues:

 

1.) We can't seem to run the report against current project types

2.) The report fails when submitting for a "missing expression"

 

Any help/suggestions is appreciated.

 

Below is the output...

 

 

Running report/web/knta/drv/rpt/SolcProjectStatus.jsp
Posting report request tohttp://10.40.82.21:8080/itg/servlet/RunJspReport
Post data= 'REPORT_JSP=%2Fweb%2Fknta%2Fdrv%2Frpt%2FSolcProjectStatus.jsp&OUT_BASEFILE=rep_31562&USER_ID=1&REPORT_ID=31562&SUBMISSION_LANGUAGE=AMERICAN&KEYID=34205&KEY=1960135734'
Writing report file/opt/ppm/reports/rep_31562.html
Error running report.
(Cleaning up any partial report output...)
HTTP response:500 - javax.servlet.jsp.JspException: ORA-00936: missing expression


   SELECT
    pp.project_name
   ,req.description
   ,per1.long_name start_period
   ,per2.long_name finish_period
   ,rd.visible_parameter14
   ,ste.state_name
   ,pp.project_id
   ,REPLACE(pfm.prj_project_manager_username,'#@#',';')
   ,pt.project_type_name
   ,pfm.project_health_code
   ,rd.visible_parameter17 -- override_date
   ,rd.visible_parameter18 -- override_description
   ,rd.parameter11 -- override_health
   ,DECODE(rd.parameter12,'NO_OVERRIDE',rd.visible_parameter2,rd.visible_parameter12) schedule_health
   ,DECODE(rd.parameter12,NULL,'','NO_OVERRIDE','Calculated','Manual') schedule_assesment_method
   ,rd.parameter46 schedule_comments
   ,DECODE(rd.parameter5,'NO_OVERRIDE',rd.visible_parameter3,rd.visible_parameter5) cost_health
   ,DECODE(rd.parameter5,NULL,'','NO_OVERRIDE','Calculated','Manual') cost_assesment_method
   ,rd.parameter47 cost_comments
   ,DECODE(rd.parameter22,'NO_OVERRIDE',rd.visible_parameter21,rd.visible_parameter22) issue_health
   ,DECODE(rd.parameter22,NULL,'','NO_OVERRIDE','Calculated','Manual') issue_assesment_method
   ,rd.parameter45 issue_comments
   ,rd.parameter8  scopeHealth
   ,rd.parameter48 scopeComments
   ,DECODE(rd.parameter10,NULL,rd.parameter11, rd.parameter10) projectHealthCode
   ,DECODE(rd.parameter17,NULL,'Calculated','Overridden') projectHealthAssesmentMethod
   ,TO_CHAR(req.last_update_date, 'Mon dd, yyyy hh:mi am')
   FROM pm_projects   pp
   JOIN pm_project_types pt ON pt.project_type_id = pp.project_type_id
   JOIN kcrt_requests req ON req.request_id = pp.pfm_request_id
   JOIN kcrt_request_details rd ON rd.parameter1 = TO_CHAR(req.request_id) AND rd.request_type_id= AND rd.batch_number=1
   JOIN kcrt_fg_pfm_project pfm ON pfm.request_id = pp.pfm_request_id
   LEFT OUTER JOIN pm_project_rollup ppr ON ppr.rollup_id = pfm.prj_project_id
   LEFT OUTER JOIN kdrv_projects prj ON prj.pfm_request_id    = pp.pfm_request_id
                                      AND prj.parent_project_id = -1
                                      AND prj.project_type_code ='PROJECT'
   JOIN ppm_fiscal_periods  per1 ON per1.fiscal_period_id = pp.start_date_period
   JOIN ppm_fiscal_periods  per2 ON per2.fiscal_period_id = pp.finish_date_period
   LEFT OUTER JOIN kdrv_states ste ON ste.state_id = prj.state_id
   WHERE pp.pfm_request_id = 30517
   AND rd.creation_date =
   (SELECT MAX(x.creation_date)
    FROM kcrt_request_details x
    WHERE x.request_type_id=
         AND x.parameter1=TO_CHAR(30517)  -- Project Initiation Request_ID (INPUT)
   )
 
javax.servlet.jsp.JspException: ORA-00936: missing expression


   SELECT
    pp.project_name
   ,req.description
   ,per1.long_name start_period
   ,per2.long_name finish_period
   ,rd.visible_parameter14
   ,ste.state_name
   ,pp.project_id
   ,REPLACE(pfm.prj_project_manager_username,'#@#',';')
   ,pt.project_type_name
   ,pfm.project_health_code
   ,rd.visible_parameter17 -- override_date
   ,rd.visible_parameter18 -- override_description
   ,rd.parameter11 -- override_health
   ,DECODE(rd.parameter12,'NO_OVERRIDE',rd.visible_parameter2,rd.visible_parameter12) schedule_health
   ,DECODE(rd.parameter12,NULL,'','NO_OVERRIDE','Calculated','Manual') schedule_assesment_method
   ,rd.parameter46 schedule_comments
   ,DECODE(rd.parameter5,'NO_OVERRIDE',rd.visible_parameter3,rd.visible_parameter5) cost_health
   ,DECODE(rd.parameter5,NULL,'','NO_OVERRIDE','Calculated','Manual') cost_assesment_method
   ,rd.parameter47 cost_comments
   ,DECODE(rd.parameter22,'NO_OVERRIDE',rd.visible_parameter21,rd.visible_parameter22) issue_health
   ,DECODE(rd.parameter22,NULL,'','NO_OVERRIDE','Calculated','Manual') issue_assesment_method
   ,rd.parameter45 issue_comments
   ,rd.parameter8  scopeHealth
   ,rd.parameter48 scopeComments
   ,DECODE(rd.parameter10,NULL,rd.parameter11, rd.parameter10) projectHealthCode
   ,DECODE(rd.parameter17,NULL,'Calculated','Overridden') projectHealthAssesmentMethod
   ,TO_CHAR(req.last_update_date, 'Mon dd, yyyy hh:mi am')
   FROM pm_projects   pp
   JOIN pm_project_types pt ON pt.project_type_id = pp.project_type_id
   JOIN kcrt_requests req ON req.request_id = pp.pfm_request_id
   JOIN kcrt_request_details rd ON rd.parameter1 = TO_CHAR(req.request_id) AND rd.request_type_id= AND rd.batch_number=1
   JOIN kcrt_fg_pfm_project pfm ON pfm.request_id = pp.pfm_request_id
   LEFT OUTER JOIN pm_project_rollup ppr ON ppr.rollup_id = pfm.prj_project_id
   LEFT OUTER JOIN kdrv_projects prj ON prj.pfm_request_id    = pp.pfm_request_id
                                      AND prj.parent_project_id = -1
                                      AND prj.project_type_code ='PROJECT'
   JOIN ppm_fiscal_periods  per1 ON per1.fiscal_period_id = pp.start_date_period
   JOIN ppm_fiscal_periods  per2 ON per2.fiscal_period_id = pp.finish_date_period
   LEFT OUTER JOIN kdrv_states ste ON ste.state_id = prj.state_id
   WHERE pp.pfm_request_id = 30517
   AND rd.creation_date =
   (SELECT MAX(x.creation_date)
    FROM kcrt_request_details x
    WHERE x.request_type_id=
         AND x.parameter1=TO_CHAR(30517)  -- Project Initiation Request_ID (INPUT)
   )
 

 

Honored Contributor
Jason Nichols KY
Posts: 404
Registered: ‎05-06-2008
Message 2 of 3 (603 Views)

Re: PMO - Project Status Report Issues

If memory serves for this Report Type, you must also submit a Project Status Request.  The report uses this request type to find the projects that will report on.  It's been a while since I worked on a project that was using that report type, but I clearly remember having to have the request type involved as well.

Occasional Advisor
cABany
Posts: 15
Registered: ‎04-30-2012
Message 3 of 3 (559 Views)

Re: PMO - Project Status Report Issues

You were right on that... It had been so long I had forgotten that a request was needed first.

 

So from the debug I found (I believe) where the missing expression lies.

 

SELECT kr.request_id, kr.description, kl.meaning, ku.full_name assigned_to_name,
DECODE(krd.parameter2,NULL,'',TO_CHAR(TO_DATE(krd.parameter2,'YYYY-MM-DD HH24:MI:SS'),'Mon DD, YYYY')) date_identified, ks.status_name
    FROM kcrt_fg_pfm_project pfmprj
    JOIN kcrt_fg_master_proj_ref proj_ref ON pfmprj.prj_project_id = proj_ref.ref_master_project_id
    JOIN kcrt_request_details krd ON krd.request_id = proj_ref.request_id

    AND krd.request_type_id =?????
    AND krd.batch_number = 1
    JOIN kcrt_requests kr ON kr.request_id = krd.request_id
    LEFT OUTER JOIN knta_lookups kl ON kl.lookup_code = kr.priority_code AND kl.lookup_type = 'ISSUE_PRIORITY'
    LEFT OUTER JOIN knta_users ku ON ku.user_id = kr.assigned_to_user_id
    JOIN kcrt_statuses ks ON ks.status_id = kr.status_id
    WHERE pfmprj.request_id = 33565
    AND (kr.status_id IN ( 4 )  OR
      (kr.status_code in ('CLOSED_SUCCESS', 'CLOSED_FAILURE') and kr.last_update_date > sysdate- 7*2))
    AND kr.priority_code in (decode ('Y', 'Y', 'CRITICAL', 'N/A'),
                                     decode ('Y', 'Y', 'HIGH', 'N/A'),
                                     decode ('N', 'Y', 'NORMAL', 'N/A'),
                                     decode ('N', 'Y', 'LOW', 'N/A'))
 ORDER BY kl.seq desc, krd.parameter3 desc

 

I of course added the ????... This would seem to be happening in the Issues section of the report... I'm at a loss for why the expression isn't complete.

 

I'll open up an incident with Support.

 

Thanks!

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.