Project Details (Milestone and Task Tracking) Query Report

Project Details (Milestone and Task Tracking) Query Report

Info
Purpose

This report extracts project-level details to provide a comprehensive overview of project performance and tracking.
It helps monitor:
  1. Project progress
  2. Schedule adherence
  3. Cost performance
  4. Effort estimation vs actuals

The report includes the following columns:
  1. Project Title
  2. Project Status
  3. Project Owner
  4. Project Schedule Start
  5. Project Schedule End
  6. Project Actual Start
  7. Project Actual End
  8. Project Estimated Cost
  9. Project Actual Cost
  10. Project Estimated Hours
  11. Project Actual Hours
  12. Milestone ID
  13. Milestone Title
  14. Milestone Owner
  15. Milestone Status
  16. Milestone Schedule Start
  17. Milestone Schedule End
  18. Milestone Actual Start
  19. Milestone Actual End
  20. Milestone Estimated Hours
  21. Milestone Actual Hours
  22. Task ID
  23. Task Title
  24. Task Owner
  25. Task Status
  26. Task Percentage Of Completion
  27. Task Scheduled Start Time
  28. Task Scheduled End Time
  29. Task Actual Start Time
  30. Task Actual End Time

NotesDatabase: PostgreSQL

Please find the query below:

  1. SELECT projectdet .PROJECTID "Project Id",
  2. projectdet.TITLE "Project Title",
  3. projectstatus.STATUSNAME "Project Status",
  4. projectowner.FIRST_NAME "Project Owner",
  5. longtodate(projectdet.SCHEDULEDSTARTTIME) "Project Schedule start",
  6. longtodate(projectdet.SCHEDULEDENDTIME) "Project Schedule end",
  7. longtodate(projectdet.ACTUALSTARTTIME) "Project Actual start",
  8. longtodate(projectdet.ACTUALENDTIME) "Project Actual end",
  9. ProjectEstimations.ESTIMATEDCOST "Project Estimated Cost",
  10. ProjectEstimations.tot_taskhours_cost "Project Actual Cost",
  11. ProjectEstimations.Estimatedhours "Project Estimated hours",
  12. ProjectEstimations.Actualtaskhours "Project Actual hours",
  13. md.Milestoneid "Milestone ID",
  14. md.TITLE "Milestone Title",
  15. mdon.FIRST_NAME "Milestone Owner",
  16. mst.StatusName "Milestone Status" ,
  17. longtodate(md.SCHEDULEDSTARTTIME) "Milestone Schedule start",
  18. longtodate(md.SCHEDULEDENDTIME) "Milestone Schedule end",
  19. longtodate(md.ACTUALSTARTTIME) "Milestone Actual start",
  20. longtodate(md.ACTUALENDTIME) "Milestone Actual end",
  21. md.Estimatedhours "Milestone Estimated hours",
  22. md.Actualtaskhours "Milestone Actual hours",
  23. taskdet.TASKID "Task ID",
  24. taskdet.TITLE "Task Title",
  25. taskowner.FIRST_NAME "Task Owner",
  26. tst.StatusName "Task Status",
  27. taskdet.PER_OF_COMPLETION "Task Percentage Of Completion",
  28. LONGTODATE(taskdet.SCHEDULEDSTARTTIME) "Task Scheduled Start Time",
  29. LONGTODATE(taskdet.SCHEDULEDENDTIME) "Task Scheduled End Time",
  30. LONGTODATE(taskdet.ACTUALSTARTTIME) "Task Actual Start Time",
  31. LONGTODATE(taskdet.ACTUALENDTIME) "Task Actual End Time" FROM ProjectDetails projectdet
  32. LEFT JOIN TaskToProjects projtotaskdet ON projectdet.PROJECTID= projtotaskdet.PROJECTID
  33. LEFT JOIN TaskDetails taskdet ON projtotaskdet.TASKID=taskdet.TASKID
  34. LEFT JOIN TaskToProjects miletotaskdet ON taskdet.TASKID=miletotaskdet.TASKID
  35. LEFT JOIN MileStoneDetails md ON miletotaskdet.MILESTONEID=md.MILESTONEID
  36. LEFT JOIN SDUser projectownerdet ON projectdet.OWNERID=projectownerdet.USERID
  37. LEFT JOIN AaaUser projectowner ON projectownerdet.USERID=projectowner.USER_ID
  38. LEFT JOIN ProjectStatus projectstatus ON projectdet.STATUSID=projectstatus.STATUSID
  39. LEFT JOIN SDUSer mdo ON md.OWNERID=mdo.USERID
  40. LEFT JOIN AaaUser mdon ON mdo.USERID=mdon.USER_ID
  41. LEFT JOIN ProjectStatus mst ON md.Statusid=mst.STATUSID
  42. LEFT JOIN ProjectStatus tst on taskdet.Statusid = tst.STATUSID
  43. LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID
  44. LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
  45. LEFT JOIN ProjectEstimations ON projectdet.PROJECTID=ProjectEstimations.PROJECTID order by 1



    • Related Articles

    • Change Implementation Query Report

      Purpose This report extracts Change, Task, and Worklog details specifically for changes in the Implementation stage. It provides a consolidated view to track: Change progress Task execution Ownership and priorities Worklog activity The report ...

    • List of Services Template Report

      Tthe below Query to list all service catalog in the system Database : PostgreSQL Server - Version: 10.5 SELECT serd.Name "Service Category", reqtl.templatename "Service catalog", sla.slaname "SLA" , sdo.name "Site", array_to_string(array_agg(CASE ...

    • Instant Task Deployment Fails with “Invalid Authentication Passcode” - OS Deployment

      Problem Description: A client encountered a deployment failure when using an Instant Task to deploy an OS image over the network. The error displayed: Invalid authentication passcode. Observations: The authentication passcode has been verified as ...

    • Error while saving the email server details : Duplicate entry for key

      This Problem may happen when the application is holding to different mail server details. 1- So go to Reports > New query report and execute the following query : select * from IncomingMailServer 2- If you see multiple mail server configured in this, ...

    • Time Elapsed not avavilble on the Matrix report || Known issue at 15130 build || SD-136533

      Dear Team, Our client GCA is encountering an issue regarding the matrix reports, where the Time Elapsed column is not fetching and is not available as a selection when you try to create, so we referred to the support team, and after investigation, ...