Overallocation SQL Query (PS2010) RRS feed

  • Question

  • I have a query which returns allocated work from PS2010. I have an issue where MSP_EpmAssignment_UserView.AssignmentIsOverallocated returns TRUE for a resource when in fact they are not over allocated in PS. How is this property calculated?

    • Edited by _c Friday, June 6, 2014 9:49 AM typo
    Friday, April 25, 2014 4:22 PM

All replies

  • This may not be a direct answer to your question, but I'd also look at querying Capacity from the CapacityByDay table to compare against these values.  That may help figure out where the discrepancy is.

    I'd also consider simplifying your query - for example, you don't need the Assignment Start/End dates for timephased data. (Maybe you're using it for a report)

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky

    Saturday, April 26, 2014 11:08 AM
  • Thanks, but I can't find capacity values in that table.

    • Edited by _c Friday, June 6, 2014 9:50 AM typo
    Monday, April 28, 2014 8:07 AM
  • I don't have the db in front of me, but the 32 should be in the TimeByDay column in the ResourceCapacitybyDay table. (not entirely sure of the names, but that is close).  You'll have to group by the month and do a sum of the TimeByDay column to get monthly figures however.

    Back to your initial question, sometimes capacity and work are calculated at a different level of granularity than you may be expecting - which could explain why someone is coming up overallocated.  Depending on settings in the client, it could calculate overallocations for each 15 minute period of the schedule.

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky

    Monday, April 28, 2014 1:15 PM