none
Get all projects where the resource is team member RRS feed

  • Question

  • Hi,

    what's the best approach to programmatically find all projects in which the resource is team member? Through PSI or through an RDB query? If so how?

    Thanks for any advice

    John


    John
    Saturday, December 18, 2010 8:24 AM

Answers

  • Hi Barbara and John,

    I just though of another very simple 'work-around':
    Create a manually scheduled task (only enter the task name, give it a logical name to indicate the purpose of the task) and set it to inactive. Assign the resources who should be part of the team but should have no assignments.

    The assignment should now be available in the MSP_EpmAssignment table.

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    Friday, January 7, 2011 1:56 PM
    Moderator

All replies

  • Hi John,

    I would go with a RDB query on the Project Server Reporting Database. You could for example query the MSP_EpmAssignment table to retreive all unique ProjectUID values where the resource is assigned in the project. You can store the query as a stored procedure with a parameter.

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    Tuesday, December 21, 2010 7:58 AM
    Moderator
  • Hi Hans,

    thanks for your feedback. Actually I would like to have a list of projects where the resource is team member. That would not necessarily imply that the resource has assignments.

    Do you have an idea how I can retrieve this?

    Thanks again

    John


    John
    Friday, January 7, 2011 9:45 AM
  • Hi John,

    this information can be found in MSP_PROJECT_RESOURCES_PUBLISHED_VIEW in Draft DB and in MSP_PROJECT_RESOURCES_WORKING_VIEW in Published DB. So far I haven't been not able to find this information in Reporting DB.
    NOTE: Queries on DBs other than Reporting are not supported!!!

    In PSI there is a ReadProjectTeam method, this could also help?

    Regards
    Barbara

    Friday, January 7, 2011 10:32 AM
    Moderator
  • Hi John,

    Barbara is correct that resources who are part of the team but have no assignments are not available in the Reporting database.
    The first question that comes to my mind is why you want to add resources to a team but do not assign them?

    If you really need to have this information, you can also consider following solution which is supported, but does require some custom development: using events (client side or server side) you could save the project team information in a custom database, for example when saving the project.
    Or as Barbara already pointed out, use the PSI to retreive the information you need.

    Regards,
    Hans


    My EPM blog: Projectopolis
    Friday, January 7, 2011 1:21 PM
    Moderator
  • Hi Hans,

    I use resources being team members without assignments for two reasons, even if they do not need to perform any tasks:

    • provide (read) access to a project workspaces
    • provide specific permissions on category level

    Regards
    Barbara

    Friday, January 7, 2011 1:42 PM
    Moderator
  • Hi Barbara and John,

    I just though of another very simple 'work-around':
    Create a manually scheduled task (only enter the task name, give it a logical name to indicate the purpose of the task) and set it to inactive. Assign the resources who should be part of the team but should have no assignments.

    The assignment should now be available in the MSP_EpmAssignment table.

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    Friday, January 7, 2011 1:56 PM
    Moderator