none
Project Server 2007 - View Proposed Resource Assignments via SQL RRS feed

  • Question

  • My goal is to view resource assignments by booking type via the ProjectServer_Reporting database in SQL.  Our Resource Managers need the ability to distinguish between proposed and committed work for the entire workforce.  We do not utilize the timesheets in Project Server because we have a legacy, third-party time tracking system.

    I know that the data exists to view resource assignments by proposed and committed hours based on a Project Web Access report.  This report can be found by selecting the resource in the Resource Center then viewing Resource Availability.  Selecting "Include proposed bookings" in the Options on that page will add proposed hours to the resource's assignment work.  To my great frustration though, it does not distinguish between hours that are proposed and committed, so it renders this report useless for my team.

    I need the ability to display the information found in the Details section of the Resource Availability page, but with one extra field indicating whether the time is proposed or committed.  I would prefer to extract this data via SQL views/queries as opposed to PWA. 

    Can this be done?  

    Thank you!

    -Chris

    • Moved by Alexander.BurtonModerator Monday, November 15, 2010 11:27 AM SQL Query question (From:Project Server General Questions and Answers)
    Friday, November 12, 2010 4:51 PM

Answers

  • Hi Chris,

    I just had a quick look in the ReportingDB and yes, it can be done.  The information you are after is stored in the table MSP_EPMAssignment which holds details of the project, resource, task, effort and duration fields. The column assignmentbookingid holds information as to whether the booking is proposed or committed (you can see the reference data for that field in MSP_EPMAssignmentBooking

    I am also going to move this thread into the dev forum, so others can benefit from it.

    Hope this is of use.

     


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Monday, November 15, 2010 11:26 AM
    Moderator
  • I think something like this will work:

    SELECT

      MSP_EpmAssignmentByDay_UserView.TimeByDay

      ,MSP_EpmAssignmentByDay_UserView.TaskUID AS [MSP_EpmAssignmentByDay_UserView TaskUID]

      ,MSP_EpmAssignmentByDay_UserView.AssignmentWork

      ,MSP_EpmAssignment_UserView.AssignmentBookingID

      ,MSP_EpmAssignment_UserView.TaskUID AS [MSP_EpmAssignment_UserView TaskUID]

      ,MSP_EpmAssignment_UserView.ResourceUID

      ,MSP_EpmTask_UserView.TaskName

      ,MSP_EpmResource_UserView.ResourceName

      ,MSP_EpmResource_UserView.ResourceBookingType

    FROM

      MSP_EpmAssignmentByDay_UserView

      INNER JOIN MSP_EpmAssignment_UserView

        ON MSP_EpmAssignmentByDay_UserView.AssignmentUID = MSP_EpmAssignment_UserView.AssignmentUID

      INNER JOIN MSP_EpmTask_UserView

        ON MSP_EpmAssignment_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID

      INNER JOIN MSP_EpmResource_UserView

        ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID

    Test it out.

     


    Jack Dahlgren blogs at:
    Project and Retrovention
    and rarely Twitter
    Monday, November 15, 2010 10:24 PM
    Moderator

All replies

  • Hi Chris,

    I just had a quick look in the ReportingDB and yes, it can be done.  The information you are after is stored in the table MSP_EPMAssignment which holds details of the project, resource, task, effort and duration fields. The column assignmentbookingid holds information as to whether the booking is proposed or committed (you can see the reference data for that field in MSP_EPMAssignmentBooking

    I am also going to move this thread into the dev forum, so others can benefit from it.

    Hope this is of use.

     


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Monday, November 15, 2010 11:26 AM
    Moderator
  • I think something like this will work:

    SELECT

      MSP_EpmAssignmentByDay_UserView.TimeByDay

      ,MSP_EpmAssignmentByDay_UserView.TaskUID AS [MSP_EpmAssignmentByDay_UserView TaskUID]

      ,MSP_EpmAssignmentByDay_UserView.AssignmentWork

      ,MSP_EpmAssignment_UserView.AssignmentBookingID

      ,MSP_EpmAssignment_UserView.TaskUID AS [MSP_EpmAssignment_UserView TaskUID]

      ,MSP_EpmAssignment_UserView.ResourceUID

      ,MSP_EpmTask_UserView.TaskName

      ,MSP_EpmResource_UserView.ResourceName

      ,MSP_EpmResource_UserView.ResourceBookingType

    FROM

      MSP_EpmAssignmentByDay_UserView

      INNER JOIN MSP_EpmAssignment_UserView

        ON MSP_EpmAssignmentByDay_UserView.AssignmentUID = MSP_EpmAssignment_UserView.AssignmentUID

      INNER JOIN MSP_EpmTask_UserView

        ON MSP_EpmAssignment_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID

      INNER JOIN MSP_EpmResource_UserView

        ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID

    Test it out.

     


    Jack Dahlgren blogs at:
    Project and Retrovention
    and rarely Twitter
    Monday, November 15, 2010 10:24 PM
    Moderator
  • Thank you Jack and Alex for looking at this.  The code above does work and I appreciate you putting this together. 

    Unfortunately, I'm not seeing the results I would have hoped.  In one project plan, I have set myself to a Proposed resource.  This is not reflected in the results, but I think it's due to another problem. 

    My organization is using the original Project Server 2007.  It hasn't been modified since it was installed 3 years ago, meaning none of the Service Packs or hotfixes have been implimented.  It's a long story, but it ends with my IT department not actively supporting this software.   

    I've found one hotfix in particular that may address the problem (http://support.microsoft.com/kb/968860).  Would it be fair to assume that I will not be able to accurately monitor proposed and committed until we, at the very least, apply the last 3 years of updates?  Though I shouldn't require this in a sane world, if you believe that my issue cannot be resolved without the updates, I will use it as further proof that we need IT support. 

    It's embarrassing that I even have to ask this of you and the community at large.

    Thank you again for your help!

    -Chris

    Wednesday, November 17, 2010 2:38 PM