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 2nd 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.
