none
A report that shows both resource capacity and project allocation RRS feed

  • Question

  • Project 2010, Office 2010, all updates, this isn't a support issue, more of a reporting question...

    I'm starting to wonder if it's just a cruel joke that resource capacity and resource allocation are kept so separate in the reporting database.  The only way to join the assignmentbyday table (where you get assigned work)  to resourcebyday (where you get capacity) is with projectUID or taskUID, which suddenly makes the query decide that capacity is measurable by project!

    I can't be the only one who wants to see timephased allocations by project (maybe in a stacked bar or area pivot chart) with a nice happy little marker for capacity running through (or hopefully along the top) of it. 

    I have written a series of queries (mostly by modifying the canned Resource Capacity report, also by attempting to do a UNION ALL to a couple separate sets of data) and the continued result is that the resource capactiy is calculated as per project per resource or vice versa, no matter what I do. (an example is that "Mark" is assigned to two projects in a given week, so his capacity shows as 80 hours during that week)

    I have also made some sad attempts to add a calculated field that multiplies resources by days, or divide the capacity by the number of projects,  to get a sort of "fake capacity" but even if I could get that to work it wouldn't account for resource end dates or holidays.

    Has anyone else hit this problem, or found a way around it?  Yes, I know that you can look at someone's resource capacity and allocation in the resource center, a few at a time, but we'd like to do this by RBS or by division, to help our planning needs.

    Anyone? Bueller?


    • Edited by ElliJ Saturday, February 25, 2012 1:52 AM
    Saturday, February 25, 2012 1:47 AM

Answers

  • Hi there--

    I have used Resource /;Capacity versus Demand Heatmap by Position Role" , "Resource Allocation Subreport" & Resource Allocation to get the similar requirement using the project server 's report pack 2 for 2007 version which works with 2010 with minor changes like filtering the data using RBS, these report filter the data using the Position/role. 

    Please see Christophe's blog which is about the report pack.

    http://blogs.msdn.com/b/chrisfie/archive/2009/08/21/project-server-2007-report-pack-ii-an-incredible-asset-you-should-all-leverage.aspx
    http://code.msdn.microsoft.com/EPMReportPack

    Hope that helps.


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

    • Marked as answer by ElliJ Wednesday, February 29, 2012 5:10 PM
    Monday, February 27, 2012 6:45 AM
  • Just for the record, I ended up finding a workaround for this which works quite well, though it's not bulletproof:

    SELECT
    MSP_EpmProject_UserView.ProjectName,
    MSP_EpmAssignmentByDay_UserView.TimeByDay,
    MSP_EpmAssignmentByDay_UserView.AssignmentWork,
    MSP_EpmResource_UserView.ResourceIsActive,
    MSP_EpmResource_UserView.ResourceIsGeneric,
    MSP_EpmResource_UserView.RBS,
    MSP_EpmResource_UserView.ResourceLatestAvailableTo,
    MSP_EpmResource_UserView.ResourceName

    FROM
    MSP_EpmProject_UserView INNER JOIN
    MSP_EpmTask_UserView ON
    MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

    INNER JOIN
    MSP_EpmAssignment ON
    MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment.TaskUID

    INNER JOIN
    MSP_EpmAssignmentByDay_UserView ON
    MSP_EpmAssignment.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID

    INNER JOIN
    MSP_EpmResource_UserView ON
    MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID

    WHERE
    (MSP_EpmResource_UserView.ResourceName IS NOT NULL) AND
    (MSP_EpmResource_UserView.RBS like '%staff%')

    UNION ALL

    SELECT
    MSP_EpmResource_UserView.ResourceBaseCalendar, <<---or any other value which is the same for all resources>>
    MSP_EpmResourceByDay_UserView.TimeByDay,
    MSP_EpmResourceByDay_UserView.Capacity as [AssignmentWork],
    MSP_EpmResource_UserView.ResourceIsActive,
    MSP_EpmResource_UserView.ResourceIsGeneric,
    MSP_EpmResource_UserView.RBS,
    MSP_EpmResource_UserView.ResourceLatestAvailableTo,
    MSP_EpmResource_UserView.ResourceName

    FROM
    MSP_EpmResource_UserView

    INNER JOIN
    MSP_EpmResourcebyDay_UserView ON
    MSP_EpmResourcebyDay_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID

    WHERE
    (MSP_EpmResource_UserView.ResourceName IS NOT NULL) AND
    (MSP_EpmResource_UserView.RBS like '%staff%')

    Once you pull the data in, rename your <resourcebasecalendar>, or whatever field you end up using for that, to "capacity".  As long as you don't need totals are are looking more for a nice chart with a capacity line, this works!

    No reporting services or PowerPivot required!  The query runs fast (unlike everything else I've tried) and it charts beautifully. 

    Tuesday, April 10, 2012 11:51 PM

All replies

  • You might check out the OLAP cubes in analysis services. some of them make this kind of reporting a bit easier.

    You can get the query right in reporting but it does take a more complex type query. I think I have one around here somewhere but I will have to dig for it.


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Saturday, February 25, 2012 2:27 AM
    Moderator
  • Hi there--

    I have used Resource /;Capacity versus Demand Heatmap by Position Role" , "Resource Allocation Subreport" & Resource Allocation to get the similar requirement using the project server 's report pack 2 for 2007 version which works with 2010 with minor changes like filtering the data using RBS, these report filter the data using the Position/role. 

    Please see Christophe's blog which is about the report pack.

    http://blogs.msdn.com/b/chrisfie/archive/2009/08/21/project-server-2007-report-pack-ii-an-incredible-asset-you-should-all-leverage.aspx
    http://code.msdn.microsoft.com/EPMReportPack

    Hope that helps.


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

    • Marked as answer by ElliJ Wednesday, February 29, 2012 5:10 PM
    Monday, February 27, 2012 6:45 AM
  • Maybe the problem is that I don't know how to pivot this information.  I hacked the query that you mentioned from the report pack and stuck it into my ODBC connection, but it still won't graph right.  There's a whole section in the development resource that talks about exactly this issue, and how to solve it by pulling the capacity data out and putting it in a separate table...but then what?  This is probably too particular a question to ask out here and the answer really is to just get reporting services working on the system, (I don't have the business intelligence dev studio running anywhere, etc)  but that's not at the top of a very  long list of "setup" things we still need to do.  I just have to prove it's worth the effort...  Thanks for your help!
    Wednesday, February 29, 2012 5:10 PM
  • Well, I got reporting services working, made them give me the access I needed, hacked the report to use RBS instead of position/role...and even the supposedly correct [SRS Capacity Calculated Contribution] gives me incorrect data.

    I have one more idea and after that I'm just going to tell them that what they are asking for is impossibe...

    Every resource in the our pool is in a project with a specific naming scheme, which means if I were to use a filter/mdx/expression/calculated field to tell it to only show the Capacity for a project with a name like "the name", it would be correct.  I have tried a number of combinations and queries and expressions and fields and I just can't make this work.  I don't understand why this has to be so hard.  Anyone have any more thoughts as to why this is not working?

    Thanks!  


    The picture above has the names cut off, but it really is broken down by resource and project. (the resource is above the Capacity row)
    • Edited by ElliJ Thursday, March 8, 2012 1:17 AM clarification
    Thursday, March 8, 2012 1:16 AM
  • Just for the record, I ended up finding a workaround for this which works quite well, though it's not bulletproof:

    SELECT
    MSP_EpmProject_UserView.ProjectName,
    MSP_EpmAssignmentByDay_UserView.TimeByDay,
    MSP_EpmAssignmentByDay_UserView.AssignmentWork,
    MSP_EpmResource_UserView.ResourceIsActive,
    MSP_EpmResource_UserView.ResourceIsGeneric,
    MSP_EpmResource_UserView.RBS,
    MSP_EpmResource_UserView.ResourceLatestAvailableTo,
    MSP_EpmResource_UserView.ResourceName

    FROM
    MSP_EpmProject_UserView INNER JOIN
    MSP_EpmTask_UserView ON
    MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

    INNER JOIN
    MSP_EpmAssignment ON
    MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment.TaskUID

    INNER JOIN
    MSP_EpmAssignmentByDay_UserView ON
    MSP_EpmAssignment.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID

    INNER JOIN
    MSP_EpmResource_UserView ON
    MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID

    WHERE
    (MSP_EpmResource_UserView.ResourceName IS NOT NULL) AND
    (MSP_EpmResource_UserView.RBS like '%staff%')

    UNION ALL

    SELECT
    MSP_EpmResource_UserView.ResourceBaseCalendar, <<---or any other value which is the same for all resources>>
    MSP_EpmResourceByDay_UserView.TimeByDay,
    MSP_EpmResourceByDay_UserView.Capacity as [AssignmentWork],
    MSP_EpmResource_UserView.ResourceIsActive,
    MSP_EpmResource_UserView.ResourceIsGeneric,
    MSP_EpmResource_UserView.RBS,
    MSP_EpmResource_UserView.ResourceLatestAvailableTo,
    MSP_EpmResource_UserView.ResourceName

    FROM
    MSP_EpmResource_UserView

    INNER JOIN
    MSP_EpmResourcebyDay_UserView ON
    MSP_EpmResourcebyDay_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID

    WHERE
    (MSP_EpmResource_UserView.ResourceName IS NOT NULL) AND
    (MSP_EpmResource_UserView.RBS like '%staff%')

    Once you pull the data in, rename your <resourcebasecalendar>, or whatever field you end up using for that, to "capacity".  As long as you don't need totals are are looking more for a nice chart with a capacity line, this works!

    No reporting services or PowerPivot required!  The query runs fast (unlike everything else I've tried) and it charts beautifully. 

    Tuesday, April 10, 2012 11:51 PM
  • So you are running this into Excel then?

    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Tuesday, April 10, 2012 11:54 PM
    Moderator
  • Yep!  The only annoying thing about it is that every time you update the chart, the capacity (I have the data series set to a line graph against bars) line turns back into a bar and you have to toggle it back.  Oh the humanity! :)
    Wednesday, April 11, 2012 12:03 AM
  • Interesting solution.  Another possible refinement -- replace the SQL after the "UNION ALL" from

    SELECT       MSP_EpmResource_UserView.ResourceBaseCalendar, <<---or any other value which is the same for all resources>>...

    to

    SELECT       'Capacity' as ProjectName,...

    so you do not have to do the renaming step.

    Alan Decker, QBE North America, 608-825-5073

    Wednesday, April 11, 2012 5:31 PM
  • Alan,

    The UNION already brings the "capacity" values in under ProjectName.  Whatever label that field has (in my case, it is "standard") shows as though it were a project name, and that is what needs to be renamed.   I didn't really explain that renaming part very well, I guess.



    • Edited by ElliJ Wednesday, April 11, 2012 5:59 PM
    Wednesday, April 11, 2012 5:57 PM
  • Report was very nice, but capacity for the period I given is not able retrieve, how to sum the resource capacity

    for example --> 31days-8 sat,sun holiday-calendar holidays -2 = 31-8-2=21*8=168

    This above value where we get. rest of the everything is good

    Thursday, August 10, 2017 2:56 PM