none
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

Answers

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.
    http://social.msdn.microsoft.com/Forums/en-us/project2010custprog/thread/b819df03-6675-43dd-83a9-8f91ae652096

    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: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    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:

    http://msdn.microsoft.com/en-us/library/gg178522

    Thanks

    Paul


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

    • Marked as answer by gkrilov Friday, September 7, 2012 1:11 PM
    Thursday, September 6, 2012 4:21 PM
    Moderator
  • 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.

    Thanks

    Paul


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

    Thursday, September 6, 2012 10:05 PM
    Moderator
  • 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.

    Thanks,


    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

    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 :)

    Thanks,

    Greg

    Friday, September 7, 2012 1:12 PM