none
How to check resource availability? RRS feed

  • Question

  • Hello,

    i have a scenario, a resource is occupied in 3 projects... i want to check if resource is using more than its capacity then have to generate alert using c#.. how can i check it? Please reply. Thanks


    Hammad Umar

    Monday, February 20, 2012 5:52 AM

Answers

  • Ok, If you are not able to get the SQL, I have recently used the below query in one of my reports & run it in a SQL job to send the emails to PMO group based on the resource assignment if the hours are more than 8 on a day.

    SELECT        r1.ResourceName, p1.ProjectName, t1.TaskName, ad1.TimeByDay, 
    ad1.AssignmentWork, 0 AS Nonworking
                             
    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) 

    Now, You can filter the records based on the date & project adding the below two conditions:
     AND (ad1.TimeByDay BETWEEN @From AND @To)
     AND (a1.ProjectUID IN (@Projects))

    Regarding the PSI call , You can write a method in C# which will get you the resource's project using SQL query (Assignment view/resource view ) to get the the list of the projects from a Stored proc , where the resource has assigned work. You can use the statusing web service to get the data:

    http://msdn.microsoft.com/en-us/library/websvcstatusing_di_pj14mref.aspx

    Hope that helps.


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    • Marked as answer by Hammad Umar Monday, February 20, 2012 9:01 AM
    Monday, February 20, 2012 8:22 AM

All replies

  • HI there,

    You can read the assignments using projects erver reporting database SQL query on MSP_EpmAssignmentByDay & join it with project/resource views to get the exact data. from PSI, you can also get the data.
    1. Reference the Project.asmx service (ex: http://servername/pwa/_vti_bin/psi/Project.asmx)
    2. Use the ReadProjectEntities method to retrieve a DataSet and pass it a ProjectEntityType of Task, Assignment and Resource.

    Define some entity types:

    public const int ENT_TYPE_TASK = 2;
    public const int ENT_TYPE_RESOURCE = 4;
    public const int ENT_TYPE_ASSIGNMENT = 8;
    

    Then you can read the data:

    int entity = ENT_TYPE_TASK | ENT_TYPE_ASSIGNMENT | ENT_TYPE_RESOURCE; ProjectDataSet dataSet = project.ReadProjectEntities(projectUid, entity, DataStoreEnum.PublishedStore); // do stuff with these tables... //dataSet.Task //dataSet.Assignment //dataSet.ProjectResource

    ReadProjectEntities is good to use because you can read only the part of the project you need...As for the assigned work, 


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    Monday, February 20, 2012 6:10 AM
  • Thanks Amit, i think you dont get my question, i have a resource and below is its usage.

    Project 1 25%

    Project 2 35%

    Project 3 40%

    if any PM of these project give task above his usage then i have to generate an alert for PMO. i can se this thing happen on resource availability report, but how can i using c#. The way you told me is very long, i have to read every project one by one and then calculate it usage. Is there any default value or some thing from where i can get the resource avilability. Thanks


    Hammad Umar

    Monday, February 20, 2012 6:34 AM
  • Amit gave you the answer: "reporting database SQL query on MSP_EpmAssignmentByDay & join it with project/resource views to get the exact data" If you restrict teh date range you will get totals by resource or project or task. Get your database adinistrator to help you build the necessary query and tell them the shema is in the Proejct SDK (free download from MSDN.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    Monday, February 20, 2012 7:39 AM
    Moderator
  • Ok, If you are not able to get the SQL, I have recently used the below query in one of my reports & run it in a SQL job to send the emails to PMO group based on the resource assignment if the hours are more than 8 on a day.

    SELECT        r1.ResourceName, p1.ProjectName, t1.TaskName, ad1.TimeByDay, 
    ad1.AssignmentWork, 0 AS Nonworking
                             
    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) 

    Now, You can filter the records based on the date & project adding the below two conditions:
     AND (ad1.TimeByDay BETWEEN @From AND @To)
     AND (a1.ProjectUID IN (@Projects))

    Regarding the PSI call , You can write a method in C# which will get you the resource's project using SQL query (Assignment view/resource view ) to get the the list of the projects from a Stored proc , where the resource has assigned work. You can use the statusing web service to get the data:

    http://msdn.microsoft.com/en-us/library/websvcstatusing_di_pj14mref.aspx

    Hope that helps.


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    • Marked as answer by Hammad Umar Monday, February 20, 2012 9:01 AM
    Monday, February 20, 2012 8:22 AM
  • Thanks alot amit and Rod...

    Hammad Umar

    Monday, February 20, 2012 9:03 AM