Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Need query which shows me prj_id, prj_name, actuals, planned

SOLVED
Go to solution
Highlighted
Ramazan1
Regular Collector

Need query which shows me prj_id, prj_name, actuals, planned

Hi,

 

I try to write a query to get the actuals and planned values for the projects, it should look like this:

 

Project_ID: 12345

Project_Name: xyz

Actuals: 10 PD

Planned: 12 PD

 

I wrote two queries where:

- the first query: shows me the actuals with prj_id and prj_name and

- the second: prj_id, prj_name and the planned days

 

in that case everything is ok. But when I join the 2 queries I get very different numbers, e.g.

 

Project_ID: 12345

Project_Name: xyz

Actuals: 8534 PD

Planned: 3962 PD

 

 Why does the problem appear when I join the two queries?

 

In the attachment I have the queries separately added.

 

Of course I have some values, which belong to us.They are most in the filterfields.

 

Regards,

Ramazan

1 REPLY
Ramazan1
Regular Collector
Solution

Re: Need query which shows me prj_id, prj_name, actuals, planned

Hi,

 

I have it. It shows me the Project_ID, Project_Name, Actuals and Planned.

 

SELECT
  IST.PROJEKT_ID PROJEKT_ID,
  IST.PROJEKTNAME PROJEKT_NAME,
  (CASE WHEN IST.EFFORT IS NULL THEN 0 ELSE IST.EFFORT END) ACTUALS,
  (CASE WHEN SOLL.PLANNED IS NULL THEN 0 ELSE SOLL.PLANNED END) PLANNED
FROM(
SELECT prj.pfm_request_id PROJEKT_ID, prj.project_name PROJEKTNAME, ROUND(SUM(efforts.act_eff)/8,0) EFFORT--, kper.period_id PERID, kou.org_unit_id OUID
FROM pm_projects prj
INNER JOIN pm_work_plans wp             ON prj.project_id = wp.project_id
INNER JOIN wp_tasks wpt                 ON wpt.work_plan_id = wp.work_plan_id
INNER JOIN tm_time_sheet_lines ttsl     ON ttsl.work_item_id = wpt.task_id
INNER JOIN tm_time_sheets tts           ON ttsl.time_sheet_id = tts.time_sheet_id
INNER JOIN ktmg_periods kper            ON tts.period_id = kper.period_id
INNER JOIN tm_actuals ta                ON ttsl.time_sheet_line_id = ta.time_sheet_line_id
AND ta.totals_flag ='Y'
INNER JOIN knta_users usr               ON tts.resource_id = usr.user_id
INNER JOIN KRSC_ORG_UNIT_MEMBERS koum   ON koum.user_id = usr.user_id
INNER JOIN KRSC_ORG_UNITS kou           ON kou.ORG_UNIT_ID = koum.ORG_UNIT_ID
INNER JOIN KRSC_ORG_UNITS kou2          ON kou2.ORG_UNIT_ID = kou.Parent_ORG_UNIT_ID
INNER JOIN rsc_resources res            ON res.user_id = usr.user_id
INNER JOIN rsc_roles rr                 ON rr.role_id = res.primary_role_id
--SUM EFFORTS
JOIN (SELECT ta.actuals_id actuals_id, SUM(tae.actual_effort) act_eff
      FROM tm_actuals_effort tae
      INNER JOIN tm_actuals ta ON ta.actuals_id = tae.actuals_id
      GROUP BY ta.actuals_id) efforts
ON efforts.actuals_id = ta.actuals_id
--Projectclass = Projects
JOIN (SELECT prj.pfm_request_id, krhdet.parameter48 Projektklassifizierung
      FROM kcrt_req_header_details krhdet, pm_projects prj
      WHERE krhdet.request_id = prj.pfm_request_id AND krhdet.parameter48 = 'Projects' ) Klassifizierung
ON Klassifizierung.pfm_request_id = prj.pfm_request_id
WHERE 1=1
  AND kou.org_unit_id in ([P.GRUPPE])
  AND kper.period_id in ([P.PERIODEN])
GROUP BY prj.pfm_request_id, prj.project_name
ORDER BY prj.project_name) IST,
(SELECT kprj.request_id PROJEKT_ID, kprj.project_name PROJEKTNAME, rp.position_name POS, ROUND(SUM(rpf.forecast_value/8),0) PLANNED--, rpf.forecast_date FD
FROM kcrt_fg_pfm_project kprj
INNER JOIN rsc_staffing_profiles rsp  ON kprj.prj_staff_prof_id = rsp.staffing_profile_id
INNER JOIN rsc_positions rp           ON rsp.staffing_profile_id = rp.staffing_profile_id
INNER JOIN rsc_position_forecast rpf  ON rp.position_id = rpf.position_id
JOIN (SELECT prj.pfm_request_id, krhdet.parameter48 Projektklassifizierung, krhdet.visible_parameter37 Kostnr
  FROM kcrt_req_header_details krhdet, pm_projects prj
  WHERE krhdet.request_id = prj.pfm_request_id AND krhdet.parameter48 = 'Projects' ) Klassifizierung
ON Klassifizierung.pfm_request_id = kprj.request_id
WHERE rsp.container_entity_type_code = 1
  AND rp.position_name like 'IT%'
  AND rp.position_name = '[VP.ROLLE]'
  AND rpf.forecast_date BETWEEN to_char(to_date('[P.START_DATE]','YYYY-MM-DD HH24:MI:SS'), 'DD.MM.YY') AND to_char(to_date('[P.END_DATE]','YYYY-MM-DD HH24:MI:SS'), 'DD.MM.YY')
  --AND kprj.request_id = 30674
GROUP BY kprj.request_id, kprj.project_name, rp.position_name) SOLL
WHERE IST.PROJEKT_ID = SOLL.PROJEKT_ID(+)
AND (IST.EFFORT <> 0 or SOLL.PLANNED <>  0)
ORDER BY PROJEKT_ID

//Add this to "OnDomLoad" event