Best way to retrieve all resources for a given project RRS feed

  • Question

  • Hello here is my problem,

    In the application I am working on a person logs in and is able to see a project list where they are either a PM or a resource.
    Now in our environment there are 100+ projects and to be able to check if they are a resource I need to first do ReadProject on each project and then check the projects resources against the logged in persons ID.

    The problem is that ReadProject is taking ~1second to run on each project and you can see how this completely breaks the application as sifting through the projects can take a few minutes..

    My question is what is the best way of doing this? Is there maybe a table in the reporting database I can simply query to return all of the resources for a given project? I know I can find the PMs name from the EpmProject table but as for the other resources I am lost.. I tried looking in the RDBMS but could not see anything an association between a project and the many resources it might have.

    Does anyone know the solution to my problem?

    Wednesday, September 5, 2012 8:31 PM


All replies

  • Hi there--

    Please see below thread to find the solution for resources project. You can pass the Porjectname or UID to filter the data.

    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)

    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:

    Thursday, September 6, 2012 7:34 AM
  • Hi Amit,

    while the query works - it only shows resources who have a task assigned to them on a particular project.. is it possible to retrieve the resources who have just been added to a project but don't have any tasks??

    For example the following resources were added but not assigned to any task yet

    Thursday, September 6, 2012 2:08 PM
  • Hi there,

    This information is not in the Reporting database, you will need to use the PSI ReadProjectTeam method to get the Project Team / Resources:



    Paul Mather | Twitter | | CPS

    • Marked as answer by gkrilov Friday, September 7, 2012 1:11 PM
    Thursday, September 6, 2012 4:21 PM
  • Hi Paul,

    I have tried it and although this is way faster than doing ReadProject and iterating through the resources this is still kind of slow for when there is a large number of projects.. Right now this takes 5-7 seconds to go through 80 projects.

    Is ReadProjectTeam the absolute fastest way of iterating through a projects resources? If it is then I guess it will have to do but if there is anything else you can think of please let me know! Otherwise tell me and I will accept your above answer as the correct one.

    Thursday, September 6, 2012 7:22 PM
  • Hi there,

    It is probably the quickest supported way. The quickest way would be direct database access to the draft / published databases but that is not supported.



    Paul Mather | Twitter | | CPS

    Thursday, September 6, 2012 10:05 PM
  • Yes, Reporting database doesn't have the project resource information. It maintains the assignment information for resources. As Paul has sugegsted, to read all resources of a Project, PSI ReadProjectTeam is the correct way.


    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:

    Friday, September 7, 2012 7:17 AM
  • Thanks a lot guys, I guess it is up to me to figure out how to make it a bit faster and reduce the waiting time.. maybe call it in a background thread as soon as the PM logs in :)



    Friday, September 7, 2012 1:12 PM