none
MS Project Server 2010: can server admins see all pending approvals? RRS feed

  • Question

  • Hello,

    is it possible that the project server administrator can see all pending approvals of all projects?

    In our organisation are not only team members which don't book their worked hours on projects. There are project leaders too, which don't accept the tracked hours of team members.

    If it is possible to see all pending approvals as project server administrator, please tell me, how we can see it.

    Thanks in advance


    Murol


    • Edited by Murol Wednesday, April 16, 2014 7:17 AM
    Wednesday, April 16, 2014 7:15 AM

Answers

  • I m sorry. That was our customized database. You can try the below query:

    SELECT DISTINCT  R.RES_NAME as [RESOURCE NAME], RB.RES_NAME STATUS_MANAGER, P.PROJ_NAME,A.TASK_NAME,
    [STATUS] = Case TR.ASSN_TRANS_ACTION_ENUM When 0 Then
    'Pending' When 1 Then 'Accepted' When 2 Then 'Rejected' End,
    IS_PUBLISHED = CASE WHEN P.WPROJ_LAST_PUB >TR.ASSN_TRANS_SUBMIT_DATE THEN 'Yes' ELSE 'No' END, 
    TASKSTATUS = CASE WHEN AU.ASSN_UID IS NULL AND TR.ASSN_TRANS_ACTION_ENUM = 0 THEN 'Saved' ELSE 'Submitted' END,
    BASELINED = CASE AB_BASE_NUM WHEN 0 THEN 'Yes' ELSE 'No' END
     FROM MSP_ASSIGNMENT_TRANSACTIONS TR WITH(READPAST)
    LEFT OUTER JOIN dbo.MSP_CONVERSIONS C ON ((C.CONV_VALUE = 
    TR.ASSN_TRANS_ERROR_ENUM) AND (STRING_TYPE_UID = 'A16A340A-4AD1-4F62-880C-564C45021502'))
    LEFT OUTER JOIN MSP_ASSIGNMENTS_SAVED A WITH(READPAST) ON TR.ASSN_UID = A.ASSN_UID
    LEFT OUTER JOIN MSP_ASSIGNMENTS_SUBMITTED AU WITH(READPAST) ON TR.ASSN_UID = AU.ASSN_UID
    LEFT OUTER JOIN MSP_ASSIGNMENT_BASELINES AB WITH(READPAST) ON TR.ASSN_UID = AB.ASSN_UID AND AB.AB_BASE_NUM = 0
    LEFT OUTER JOIN MSP_PROJECTS P ON A.PROJ_UID = P.PROJ_UID
    LEFT OUTER JOIN MSP_TASKS_SAVED TA ON TR.TASK_UID = TA.TASK_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES R ON A.RES_UID = R.RES_UID AND R.PROJ_UID = P.PROJ_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES RD ON 
    TR.ASSN_TRANS_DELEGATEE_RES_UID = RD.RES_UID AND P.PROJ_UID = RD.PROJ_UID
    LEFT OUTER JOIN MSP_RESOURCES RD1 ON TR.ASSN_TRANS_DELEGATEE_RES_UID = RD1.RES_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES RS ON
    TR.ASSN_TRANS_SUBMITTER_RES_UID = RS.RES_UID AND P.PROJ_UID = RS.PROJ_UID
    LEFT OUTER JOIN MSP_RESOURCES RS1 ON TR.ASSN_TRANS_SUBMITTER_RES_UID = RS1.RES_UID
    LEFT OUTER JOIN MSP_RESOURCES RA ON TR.ASSN_TRANS_APPROVER_RES_UID = RA.RES_UID
    LEFT OUTER JOIN MSP_RESOURCES RB ON A.WRES_UID_Manager = RB.RES_UID
    LEFT OUTER JOIN PS_TestFarm01__Reporting.dbo.MSP_EpmResource mer on A.WRES_UID_Manager=mer.ResourceUID

    Note: Just check the Reporting database if it is right (Underlined).


    Cheers, Amit Wairkar




    • Edited by Amit.Wairkar Tuesday, April 22, 2014 12:07 PM
    • Marked as answer by Murol Tuesday, April 22, 2014 12:43 PM
    • Unmarked as answer by Murol Tuesday, April 22, 2014 12:43 PM
    • Marked as answer by Murol Tuesday, April 22, 2014 1:03 PM
    Tuesday, April 22, 2014 12:05 PM
  • Hello Amit,

    can you help us please a second time?

    Before we execute the SQL-query against the productive EPM, we try it against a second test-db PS_TestFarm01_ProjectServer_Reporting and get the error message

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'MSP_ASSIGNMENT_TRANSACTIONS'.

    Thanks and kind regards


    Murol


    It works. The yellow text must contain PS_TestFarm01_ProjectServer_Published

    Murol

    • Marked as answer by Murol Tuesday, April 22, 2014 2:11 PM
    Tuesday, April 22, 2014 2:11 PM

All replies

  • Hello Murol,

    out of the box you don't have the possibility to see all pending approvals. You have to develop a service over PSI or a special report.

    Best Regards

    Bernhard Fischer


    Bernhard Fischer [MVP] | www.wodey.de

    Wednesday, April 16, 2014 3:40 PM
  • Hello Bernhard,

    do you have an idea how to develop a service over PSI or a special report?

    Thanks and kind regards

    Peter


    Murol


    • Edited by Murol Tuesday, April 22, 2014 7:09 AM
    Tuesday, April 22, 2014 7:08 AM
  • Hi Murol,

    This is the query for checking all the pending approvals from the Published database:

    SELECT DISTINCT  mcc.MemberValue as CC, R.RES_NAME as [RESOURCE NAME], RB.RES_NAME STATUS_MANAGER, P.PROJ_NAME,A.TASK_NAME,
    [STATUS] = Case TR.ASSN_TRANS_ACTION_ENUM When 0 Then
    'Pending' When 1 Then 'Accepted' When 2 Then 'Rejected' End,
    IS_PUBLISHED = CASE WHEN P.WPROJ_LAST_PUB >TR.ASSN_TRANS_SUBMIT_DATE THEN 'Yes' ELSE 'No' END, 
    TASKSTATUS = CASE WHEN AU.ASSN_UID IS NULL AND TR.ASSN_TRANS_ACTION_ENUM = 0 THEN 'Saved' ELSE 'Submitted' END,
    BASELINED = CASE AB_BASE_NUM WHEN 0 THEN 'Yes' ELSE 'No' END
     FROM MSP_ASSIGNMENT_TRANSACTIONS TR WITH(READPAST)
    LEFT OUTER JOIN dbo.MSP_CONVERSIONS C ON ((C.CONV_VALUE = 
    TR.ASSN_TRANS_ERROR_ENUM) AND (STRING_TYPE_UID = 'A16A340A-4AD1-4F62-880C-564C45021502'))
    LEFT OUTER JOIN MSP_ASSIGNMENTS_SAVED A WITH(READPAST) ON TR.ASSN_UID = A.ASSN_UID
    LEFT OUTER JOIN MSP_ASSIGNMENTS_SUBMITTED AU WITH(READPAST) ON TR.ASSN_UID = AU.ASSN_UID
    LEFT OUTER JOIN MSP_ASSIGNMENT_BASELINES AB WITH(READPAST) ON TR.ASSN_UID = AB.ASSN_UID AND AB.AB_BASE_NUM = 0
    LEFT OUTER JOIN MSP_PROJECTS P ON A.PROJ_UID = P.PROJ_UID
    LEFT OUTER JOIN MSP_TASKS_SAVED TA ON TR.TASK_UID = TA.TASK_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES R ON A.RES_UID = R.RES_UID AND R.PROJ_UID = P.PROJ_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES RD ON 
    TR.ASSN_TRANS_DELEGATEE_RES_UID = RD.RES_UID AND P.PROJ_UID = RD.PROJ_UID
    LEFT OUTER JOIN MSP_RESOURCES RD1 ON TR.ASSN_TRANS_DELEGATEE_RES_UID = RD1.RES_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES RS ON
    TR.ASSN_TRANS_SUBMITTER_RES_UID = RS.RES_UID AND P.PROJ_UID = RS.PROJ_UID
    LEFT OUTER JOIN MSP_RESOURCES RS1 ON TR.ASSN_TRANS_SUBMITTER_RES_UID = RS1.RES_UID
    LEFT OUTER JOIN MSP_RESOURCES RA ON TR.ASSN_TRANS_APPROVER_RES_UID = RA.RES_UID
    LEFT OUTER JOIN MSP_RESOURCES RB ON A.WRES_UID_Manager = RB.RES_UID
    LEFT OUTER JOIN GITL_Project_External_2012.dbo.MSP_EpmResource mer on A.WRES_UID_Manager=mer.ResourceUID
    LEFT OUTER JOIN GITL_Project_External_2012.dbo.MSPLT_CC mcc on mer.CC=mcc.LookupMemberUID
    WHERE (TR.ASSN_TRANS_ACTION_ENUM = 0 OR P.WPROJ_LAST_PUB < TR.ASSN_TRANS_SUBMIT_DATE) 
    AND TR.ASSN_TRANS_ACTION_ENUM =0 
    AND R.RES_NAME IS NOT NULL
    ORDER BY P.PROJ_NAME, A.TASK_NAME

    You can take this data in excel and filter to Submitted task status.


    Cheers, Amit Wairkar

    Tuesday, April 22, 2014 8:53 AM
  • Hello Amit,

    I'm not prcticed in MS SQL queries. We get an error message, when executing the query:

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'GITL_Project_External_2012.dbo.MSP_EpmResource'.

    Can you help us with the right object name?

    Thanks in advance and kind regards


    Murol

    Tuesday, April 22, 2014 11:35 AM
  • I m sorry. That was our customized database. You can try the below query:

    SELECT DISTINCT  R.RES_NAME as [RESOURCE NAME], RB.RES_NAME STATUS_MANAGER, P.PROJ_NAME,A.TASK_NAME,
    [STATUS] = Case TR.ASSN_TRANS_ACTION_ENUM When 0 Then
    'Pending' When 1 Then 'Accepted' When 2 Then 'Rejected' End,
    IS_PUBLISHED = CASE WHEN P.WPROJ_LAST_PUB >TR.ASSN_TRANS_SUBMIT_DATE THEN 'Yes' ELSE 'No' END, 
    TASKSTATUS = CASE WHEN AU.ASSN_UID IS NULL AND TR.ASSN_TRANS_ACTION_ENUM = 0 THEN 'Saved' ELSE 'Submitted' END,
    BASELINED = CASE AB_BASE_NUM WHEN 0 THEN 'Yes' ELSE 'No' END
     FROM MSP_ASSIGNMENT_TRANSACTIONS TR WITH(READPAST)
    LEFT OUTER JOIN dbo.MSP_CONVERSIONS C ON ((C.CONV_VALUE = 
    TR.ASSN_TRANS_ERROR_ENUM) AND (STRING_TYPE_UID = 'A16A340A-4AD1-4F62-880C-564C45021502'))
    LEFT OUTER JOIN MSP_ASSIGNMENTS_SAVED A WITH(READPAST) ON TR.ASSN_UID = A.ASSN_UID
    LEFT OUTER JOIN MSP_ASSIGNMENTS_SUBMITTED AU WITH(READPAST) ON TR.ASSN_UID = AU.ASSN_UID
    LEFT OUTER JOIN MSP_ASSIGNMENT_BASELINES AB WITH(READPAST) ON TR.ASSN_UID = AB.ASSN_UID AND AB.AB_BASE_NUM = 0
    LEFT OUTER JOIN MSP_PROJECTS P ON A.PROJ_UID = P.PROJ_UID
    LEFT OUTER JOIN MSP_TASKS_SAVED TA ON TR.TASK_UID = TA.TASK_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES R ON A.RES_UID = R.RES_UID AND R.PROJ_UID = P.PROJ_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES RD ON 
    TR.ASSN_TRANS_DELEGATEE_RES_UID = RD.RES_UID AND P.PROJ_UID = RD.PROJ_UID
    LEFT OUTER JOIN MSP_RESOURCES RD1 ON TR.ASSN_TRANS_DELEGATEE_RES_UID = RD1.RES_UID
    LEFT OUTER JOIN MSP_PROJECT_RESOURCES RS ON
    TR.ASSN_TRANS_SUBMITTER_RES_UID = RS.RES_UID AND P.PROJ_UID = RS.PROJ_UID
    LEFT OUTER JOIN MSP_RESOURCES RS1 ON TR.ASSN_TRANS_SUBMITTER_RES_UID = RS1.RES_UID
    LEFT OUTER JOIN MSP_RESOURCES RA ON TR.ASSN_TRANS_APPROVER_RES_UID = RA.RES_UID
    LEFT OUTER JOIN MSP_RESOURCES RB ON A.WRES_UID_Manager = RB.RES_UID
    LEFT OUTER JOIN PS_TestFarm01__Reporting.dbo.MSP_EpmResource mer on A.WRES_UID_Manager=mer.ResourceUID

    Note: Just check the Reporting database if it is right (Underlined).


    Cheers, Amit Wairkar




    • Edited by Amit.Wairkar Tuesday, April 22, 2014 12:07 PM
    • Marked as answer by Murol Tuesday, April 22, 2014 12:43 PM
    • Unmarked as answer by Murol Tuesday, April 22, 2014 12:43 PM
    • Marked as answer by Murol Tuesday, April 22, 2014 1:03 PM
    Tuesday, April 22, 2014 12:05 PM
  • Hello Amit,

    thank you, that is very great. We even see the unpublished.

    I interprete:

    Taskstatus Submitted, Status Accepted, Is_published yes --> it is ok

    Taskstatus Submitted, Status Pending, Is_published no/yes --> Approval missing

    Taskstatus Submitted, Status Accepted, Is_published no --> Publishing missing

    Taskstatus Saved, Status Pending, Is_published yes/no --> Submit missing

    I hope, my interpretation is ok.

    Many many thanks and kind regards from Mannheim in Germany


    Murol


    • Edited by Murol Tuesday, April 22, 2014 1:14 PM
    Tuesday, April 22, 2014 12:43 PM
  • Hello Amit,

    can you help us please a second time?

    Before we execute the SQL-query against the productive EPM, we try it against a second test-db PS_TestFarm01_ProjectServer_Reporting and get the error message

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'MSP_ASSIGNMENT_TRANSACTIONS'.

    Thanks and kind regards


    Murol

    Tuesday, April 22, 2014 1:37 PM
  • Hello Amit,

    can you help us please a second time?

    Before we execute the SQL-query against the productive EPM, we try it against a second test-db PS_TestFarm01_ProjectServer_Reporting and get the error message

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'MSP_ASSIGNMENT_TRANSACTIONS'.

    Thanks and kind regards


    Murol


    It works. The yellow text must contain PS_TestFarm01_ProjectServer_Published

    Murol

    • Marked as answer by Murol Tuesday, April 22, 2014 2:11 PM
    Tuesday, April 22, 2014 2:11 PM
  • Hey Murol,

    U should practically try what u have interpreted. Thats how you will learn.

    Was my pleasure helping u.


    Cheers, Amit Wairkar

    Tuesday, April 22, 2014 3:51 PM
  • Hello Amit,

    thank you, your helping was very great. Yesterday, I marked an answer.

    Kind regards


    Murol

    Wednesday, April 23, 2014 5:51 AM
  • Excellent query! Thank you so much!
    Thursday, October 8, 2015 3:51 PM