none
How to see a list of Project's tasks with resources assigned to it in a SQL PWA database report RRS feed

  • Question

  • Dear Project Server users,

    I want to create a simple pivot table that contains a project name with the list of all tasks assigned to it and the list of all resources that are either on the project or on the task.

    But I somehow cannot find a relationship between the MSP EMPTASKS and MPS EPMResources tables.

    Am i looking at the wrong place?

    With best regards,
    Petar 
    Thursday, November 10, 2016 9:28 AM

Answers

  • hi Peter,

    Once you assign resource to a task, it becomes an Assignment, and you can find a relation of resource and task within an Assignment view. 

    In Assignment view you will find TaskUID and ResourceUID, and you can find a required information from the relevant views.

    hope this help you to retrieve required information, let me know if you need any help with constructing a SQL query.

    cheers.


    Khurram Jamshed - MBA, PMP, MCTS, MCITP ( Blog, Twitter, Linkedin )
    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.

    • Marked as answer by PetarB Tuesday, November 15, 2016 9:12 AM
    Tuesday, November 15, 2016 1:50 AM

All replies

  • hi Peter,

    Once you assign resource to a task, it becomes an Assignment, and you can find a relation of resource and task within an Assignment view. 

    In Assignment view you will find TaskUID and ResourceUID, and you can find a required information from the relevant views.

    hope this help you to retrieve required information, let me know if you need any help with constructing a SQL query.

    cheers.


    Khurram Jamshed - MBA, PMP, MCTS, MCITP ( Blog, Twitter, Linkedin )
    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.

    • Marked as answer by PetarB Tuesday, November 15, 2016 9:12 AM
    Tuesday, November 15, 2016 1:50 AM
  • Hi Khurram,

    I am using the following query that Barbara made:

    SELECT        P.ProjectName
       , T.TaskName
       , TP.TaskName AS ParentTaskName
       , T.TaskIndex
       , T.TaskOutlineNumber
       , R.ResourceName, R.ResourceGroup
    ,A.AssignmentActualWork, A.AssignmentActualCost
    FROM           dbo.MSP_EpmProject_UserView AS P 
    LEFT OUTER JOIN  dbo.MSP_EpmTask_UserView AS T
     ON P.ProjectUID = T.ProjectUID 
    LEFT OUTER JOIN  dbo.MSP_EpmTask_UserView AS TP
     ON TP.TaskUID= T.TaskParentUID
     -- do not show parent task for tasks on level 1 (their parent task is project summary task)
     AND TP.TaskIsProjectSummary = 0
    LEFT OUTER JOIN  dbo.MSP_EpmAssignment_UserView AS A 
     ON A.TaskUID = T.TaskUID  
    LEFT JOIN dbo.MSP_EpmResource_UserView AS R 
     ON R.ResourceUID = A.ResourceUID
    -- only "normal" projects
    ORDER BY P.ProjectName
        , T.TaskIndex

    But, I cannot select the resource departments field. I need this field for the report.

    Do you know why the sql always reports incorrect query?

    With best regards,
    Petar 

    Tuesday, November 15, 2016 10:31 AM
  • HI PeterB,

    if you are referring to default Resource Department field, then add below to your query above to get the resource department:

    R.[Resource Departments]

    And can you explain a bit more about this: Do you know why the sql always reports incorrect query?

    cheers.


    Khurram Jamshed | Blog |Twitter | Linkedin
    Tuesday, November 15, 2016 11:08 AM
  • Thanks Khurram,

    Hmm I am not sure, will check.

    Tuesday, November 15, 2016 11:31 AM