How to retrieve comments, notes and any assignment level custom tasks field information in “Task update” page in PWA 2007?

Issue:

The “Task Update” data grid is hard wired and does not offer adding new fields for e.g. notes and comments. It allows changing column order but it does not save it and every time PM has to reorder the columns.

One of my clients wanted to get one page report showing all the desired information in correct order including comments and notes data(see attached screenshot). Project Manager was getting annoyed clicking each task link and seeing the submitted comments. In addition, there is no indication to show if a resource has submitted any comments on a particular task.

Solution:

I had two choices; either to extend the existing “Task Update” web part or to create a custom report in SSRS showing all the required fields data. I chose the 2^nd option and created an SQL that fulfills my client requirements. The Project Manager/Scheduler would be able to see only his/her own resources submitted tasks information exactly the same what they are seeing in the “Task Update” page. This SQL runs on Published database.

Enjoy the SQL. I HOPE IT HELPS!

SQL Query:

SELECT P.Proj_Name,TSK.TASK_ID,TSK.TASK_WINPROJ_UNIQUE_ID AS 'TASK_UID',A.Task_name,A.ASSN_START_DATE AS 'START',A.ASSN_FINISH_DATE AS 'FINISH',

     (SELECT ACFV.DATE_VALUE

      from MSP_ASSN_CUSTOM_FIELD_VALUES_SAVED AS ACFV

      INNER JOIN MSP_CUSTOM_FIELDS AS MCF

      ON ACFV.MD_PROP_UID=MCF.MD_PROP_UID_SECONDARY

      AND ACFV.MD_PROP_ID=MCF.MD_PROP_ID_SECONDARY

      WHERE MCF.MD_PROP_NAME LIKE 'Actual_Start'

      AND ACFV.PROJ_UID LIKE A.Proj_UID

      AND ACFV.ASSN_UID LIKE A.ASSN_UID) AS 'ACT_START_VAL',

     (SELECT ACFV.DATE_VALUE

      from MSP_ASSN_CUSTOM_FIELD_VALUES_SAVED AS ACFV

      INNER JOIN MSP_CUSTOM_FIELDS AS MCF

      ON ACFV.MD_PROP_UID=MCF.MD_PROP_UID_SECONDARY

      AND ACFV.MD_PROP_ID=MCF.MD_PROP_ID_SECONDARY

      WHERE MCF.MD_PROP_NAME LIKE 'Estimated_Finish'

      AND ACFV.PROJ_UID LIKE A.Proj_UID

      AND ACFV.ASSN_UID LIKE A.ASSN_UID) AS 'EST_FINISH_VAL',

                       

((A.ASSN_WORK/480000)* 8) AS 'WORK', ((A.ASSN_ACT_WORK/480000)* 8) AS 'ACT_WORK', ((A.ASSN_REM_WORK/480000)* 8) AS 'REM_WORK',

A.ASSN_PCT_WORK_COMPLETE as 'PercentComp',TSK_SUBM.TASK_PHY_PCT_COMP AS 'PhysicalPercent', R.RES_NAME,RM.RES_NAME AS 'OWNER', A.WASSN_Comments AS 'NOTES',

(SELECT Stuff((

           SELECT  ',' + MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS.ASSN_TRANS_COMMENT

           FROM  MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS

           LEFT JOIN MSP_ASSIGNMENT_TRANSACTIONS ON MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS.ASSN_TRANS_UID = MSP_ASSIGNMENT_TRANSACTIONS.ASSN_TRANS_UID

           WHERE MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS.ASSN_TRANS_UID = ASGN_TR.ASSN_TRANS_UID

           For                    XML Path('')

          ),1,1,'') ) AS 'COMMENTS'

FROM MSP_ASSIGNMENTS_SUBMITTED AS A

INNER JOIN MSP_PROJECTS AS P

ON P.Proj_UID=A.Proj_UID

INNER JOIN MSP_RESOURCES AS R

ON R.RES_UID=A.RES_UID

INNER JOIN MSP_ASSIGNMENT_TRANSACTIONS AS ASGN_TR

ON ASGN_TR.ASSN_UID=A.ASSN_UID

INNER JOIN ASSIGNMENT AS ASGN

ON ASGN.ASSN_UID=A.ASSN_UID

INNER JOIN MSP_TASKS AS TSK

ON TSK.TASK_UID=ASGN.TASK_UID

INNER JOIN MSP_TASKS_SUBMITTED AS TSK_SUBM

ON TSK_SUBM.TASK_PUBLISHED_UID=TSK.TASK_UID

INNER JOIN MSP_RESOURCES AS RM

ON RM.RES_UID=A.WRES_UID_Manager

WHERE ASGN_TR.ASSN_TRANS_UPDATE_DATE IS  NULL

    

Notes: It does not show all the comments history submitted by resources that have been approved in the past. It shows only the current comments (one or more) which are in the pending mode.