none
how to find team member of all project using SQL query?? RRS feed

  • Question

  • Hello all

    i want to list out the team member from the project server for all project

    how can i do it using SQL query????

    i have listed team member name using this query

    Select 	mr.RES_NAME,
    	SG.WSEC_GRP_NAME,
    	mr.WRES_EMAIL,
    	mr.WRES_ACCOUNT 
    FROM MSP_RESOURCES MR
    INNER JOIN MSP_WEB_SECURITY_GROUP_MEMBERS SGM ON (WRES_GUID = MR.RES_SECURITY_GUID)
    INNER JOIN MSP_WEB_SECURITY_GROUPS SG ON (SG.WSEC_GRP_GUID = SGM.WSEC_GRP_GUID and sg.WSEC_GRP_NAME = 'Team Members') 
    ORDER BY RES_NAME
    

    But i can't find out it with project name.

    please give me suggestion.


    Kartik Ghodasara Associative Software engineer, Synoverge Tech Pvt. Ltd. Ahmedabad

    Wednesday, July 11, 2012 9:15 AM

Answers

  • Hi Kartik,

    I hope that you understand querying the project server draft, publsihed database is not supported. To get the data for Project resource, You will need to use PSI - project web service. You can get the ProjectResource table from, project dataset.

    http://msdn.microsoft.com/en-us/library/gg204413
    You can use ReadprojectEntities method & use ProjectEntityType as 4 to read the resources.

    project TEam dataset :Represents the enterprise resource team members on a project.
    http://msdn.microsoft.com/en-us/library/websvcproject.projectteamdataset_di_pj14mref_methods

    From SQL query, You can get the Project resources when the resource is assigned to Task & project is published. Here is the sample query, you can extend based on your needs.

    You will need to join  MSP_EpmResource_UserView, MSP_EpmTask_UserView, MSP_EpmAssignmentByDay_UserView & MSP_EpmProject_UserView to get the records from Reporting database.

    SELECT        r1.ResourceName, p1.ProjectName, t1.TaskName, t1.TaskIndex,
    ad1.TimeByDay, ad1.AssignmentWork
    FROM            MSP_EpmResource_UserView AS r1
     LEFT OUTER JOIN   MSP_EpmAssignment_UserView AS a1 ON r1.ResourceUID = a1.ResourceUID 
     INNER JOIN
                             MSP_EpmAssignmentByDay_UserView AS ad1 ON a1.AssignmentUID = ad1.AssignmentUID INNER JOIN
                             MSP_EpmTask_UserView AS t1 ON a1.TaskUID = t1.TaskUID INNER JOIN
                             MSP_EpmProject_UserView AS p1 ON t1.ProjectUID = p1.ProjectUID
    WHERE        (r1.ResourceIsActive = 1) AND (r1.ResourceIsGeneric = 0) AND (r1.ResourceIsTeam = 0)

    Another way:

    select  p.projectname, t.TaskUID , r.resourcename 
    from MSP_EpmAssignment_UserView a ,MSP_EpmTask_UserView t , MSP_EpmProject_UserView p, MSP_EpmResource_UserView r
    WHERE a.TaskUID = t.TaskUID
    AND t.ProjectUID= p.ProjectUID
    AND a.ResourceUID = r.ResourceUID
    AND (r.ResourceIsActive = 1) AND (r.ResourceIsGeneric = 0) AND (r.ResourceIsTeam = 0)
    If you are looking for finiding Team members info in Security groups, use the PSI.
    http://msdn.microsoft.com/en-us/library/websvcsecurity_di_pj14mref

    Hope that helps.


    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”. Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82




    Wednesday, July 11, 2012 11:15 AM
  • HI amit,

    I found my solution

    here it is.

    select distinct	MP.PROJ_NAME,
    	MPR.RES_NAME,
    	MR.WRES_EMAIL,
    	MT.TASK_NAME	
    from [ProjectServer_Published].[dbo].[MSP_PROJECTS] AS MP
    INNER JOIN [ProjectServer_Draft].[dbo].[MSP_PROJECT_RESOURCES] AS MPR ON
    	(RES_NAME is not null )
    	and 
    	(MP.PROJ_UID = MPR.PROJ_UID)
    INNER JOIN [ProjectServer_Published].[dbo].[MSP_RESOURCES] AS MR ON
    	(MPR.RES_UID = MR.RES_UID)
    LEFT JOIN [ProjectServer_Published].[dbo].[MSP_ASSIGNMENTS] AS MA ON
    	(MP.PROJ_UID = MA.PROJ_UID)
    RIGHT JOIN [ProjectServer_Published].[dbo].[MSP_TASKS] AS MT ON
    	(MA.TASK_UID = MT.TASK_UID)	
    WHERE	
    	MP.PROJ_PROP_MANAGER IN (@Project_Manager)

    Where @Project_Manager is parameter of Report Builder

    Thanks for support.


    Regards, Kartik Ghodasara Associative Software engineer, Synoverge Tech Pvt. Ltd. Ahmedabad

    Tuesday, August 28, 2012 6:46 AM

All replies

  • Hi Kartik,

    I hope that you understand querying the project server draft, publsihed database is not supported. To get the data for Project resource, You will need to use PSI - project web service. You can get the ProjectResource table from, project dataset.

    http://msdn.microsoft.com/en-us/library/gg204413
    You can use ReadprojectEntities method & use ProjectEntityType as 4 to read the resources.

    project TEam dataset :Represents the enterprise resource team members on a project.
    http://msdn.microsoft.com/en-us/library/websvcproject.projectteamdataset_di_pj14mref_methods

    From SQL query, You can get the Project resources when the resource is assigned to Task & project is published. Here is the sample query, you can extend based on your needs.

    You will need to join  MSP_EpmResource_UserView, MSP_EpmTask_UserView, MSP_EpmAssignmentByDay_UserView & MSP_EpmProject_UserView to get the records from Reporting database.

    SELECT        r1.ResourceName, p1.ProjectName, t1.TaskName, t1.TaskIndex,
    ad1.TimeByDay, ad1.AssignmentWork
    FROM            MSP_EpmResource_UserView AS r1
     LEFT OUTER JOIN   MSP_EpmAssignment_UserView AS a1 ON r1.ResourceUID = a1.ResourceUID 
     INNER JOIN
                             MSP_EpmAssignmentByDay_UserView AS ad1 ON a1.AssignmentUID = ad1.AssignmentUID INNER JOIN
                             MSP_EpmTask_UserView AS t1 ON a1.TaskUID = t1.TaskUID INNER JOIN
                             MSP_EpmProject_UserView AS p1 ON t1.ProjectUID = p1.ProjectUID
    WHERE        (r1.ResourceIsActive = 1) AND (r1.ResourceIsGeneric = 0) AND (r1.ResourceIsTeam = 0)

    Another way:

    select  p.projectname, t.TaskUID , r.resourcename 
    from MSP_EpmAssignment_UserView a ,MSP_EpmTask_UserView t , MSP_EpmProject_UserView p, MSP_EpmResource_UserView r
    WHERE a.TaskUID = t.TaskUID
    AND t.ProjectUID= p.ProjectUID
    AND a.ResourceUID = r.ResourceUID
    AND (r.ResourceIsActive = 1) AND (r.ResourceIsGeneric = 0) AND (r.ResourceIsTeam = 0)
    If you are looking for finiding Team members info in Security groups, use the PSI.
    http://msdn.microsoft.com/en-us/library/websvcsecurity_di_pj14mref

    Hope that helps.


    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”. Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82




    Wednesday, July 11, 2012 11:15 AM
  • Hi amit,

    thanks for your reply

    i have executed your query it executed successfully but with no result.

    because my assignment_userview table is empty.

    i have build team for each project but i don't know how to assign task to team member.


    Kartik Ghodasara Associative Software engineer, Synoverge Tech Pvt. Ltd. Ahmedabad

    Wednesday, July 11, 2012 1:16 PM
  • Hi Kartik,

    Open up the project/s in Project Professional, once you have built the team using enterprise resources you can assign a resource to a task using a number of methods including using the Task Information window (double click the task or click the Task tab then click the Information tab in the Properties section), once the window is open click the Resources tab. Or insert the Resource Names column to the view if it is not there already then click the drop down in the Resource Names cell on the same row as the task and select the enterprise resource.

    Save and Publish the project and you will see data for this project using that query.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com

    Wednesday, July 11, 2012 7:08 PM
    Moderator
  • Kartik,

    Are you attempting to list out all Enterprise Resources or all users? They aren't necessarily the same thing.

    What I see above is an attempt to list out what would be the intersection of the two groups. Security only applies to users and the Resource table only contains Resources. Your query would only retrieve resources which are also users and in the Team Member group.

    Treb Gatte

    Thursday, July 12, 2012 2:45 AM
    Moderator
  • HI amit,

    I found my solution

    here it is.

    select distinct	MP.PROJ_NAME,
    	MPR.RES_NAME,
    	MR.WRES_EMAIL,
    	MT.TASK_NAME	
    from [ProjectServer_Published].[dbo].[MSP_PROJECTS] AS MP
    INNER JOIN [ProjectServer_Draft].[dbo].[MSP_PROJECT_RESOURCES] AS MPR ON
    	(RES_NAME is not null )
    	and 
    	(MP.PROJ_UID = MPR.PROJ_UID)
    INNER JOIN [ProjectServer_Published].[dbo].[MSP_RESOURCES] AS MR ON
    	(MPR.RES_UID = MR.RES_UID)
    LEFT JOIN [ProjectServer_Published].[dbo].[MSP_ASSIGNMENTS] AS MA ON
    	(MP.PROJ_UID = MA.PROJ_UID)
    RIGHT JOIN [ProjectServer_Published].[dbo].[MSP_TASKS] AS MT ON
    	(MA.TASK_UID = MT.TASK_UID)	
    WHERE	
    	MP.PROJ_PROP_MANAGER IN (@Project_Manager)

    Where @Project_Manager is parameter of Report Builder

    Thanks for support.


    Regards, Kartik Ghodasara Associative Software engineer, Synoverge Tech Pvt. Ltd. Ahmedabad

    Tuesday, August 28, 2012 6:46 AM