none
MSP 2010 Professional - Report showing total % allocation by resource per project RRS feed

  • Question

  • I have just recently upgraded from MSP 2003 to 2010 Pro.  I am still taking courses and reading on features, but need this answer quickly so hoping someone can help. 

    I am using a shared resource pool and have a decent understanding of the basic resource reports available.  I would like to see a Resource Usage Report or view that shows each resource with a total allocation percentage at the project level, roughly like this example:

                                Project A               Project B

    Mike                         70%                      30%

    John                        100%                     0%

    Deb                            50%                   50%

    Is this possible with existing reports or views?

    The reason for this is that our current SLA planning process typically uses Excel to assign a percentage allocation to each resource across several projects until the total is 100% for that resource.  This highly simplified table helps show if we are fully assigning all resources and are able to cover all desired projects.  As we build project schedules from that starting point, the report I want would be a good cross-check against the inital planning grid. 

    Thanks in advance to any takers!

    Thursday, May 23, 2013 2:18 AM

All replies

  • SGinSC,

    Here is something that will get you close to what you want. Take a look and see if it meets your needs.

    First, make sure you have SP1 installed. If you do not, you can get it at, http://technet.microsoft.com/en-us/office/ee748587

    To get the view do the following:

    1. Open the resource pool with the Resource Usage view

    2. Set the details of the timescale data to show Work and % Allocation

    3. Go to View/data group and hit the down arrow in the Group By selection box to bring up the submenu.

    4. Select New Group By

    5. Name your group

    6. Set the first group criteria to be Name

    7. Set the second group criteria to be Project (Note: this will include the full path of each sharer file but that can be addressed)

    8. After setting the second group criteria, check the option to group assignments, not resources

    9. In the field type for the second criteria change it from Resource to Assignment

    10. Hit Apply

    You can adjust the timescale as desired by each group summary row will show the total work hours per project per time period. Unfortunately the % allocation only shows on group summary lines when a resource is assigned only one task per project. However, assuming a resource works a normal 8 hour day, the work total per project will indicate how fully that resource is utilized on that project, for that time period.

    Theoretically you could also use the Resource Work Summary report under Visual Reports, but the visual reports functionality isn't all that reliable. But you might want to try it (Hint: try different level of usage data time spans if you get a failure).

    Hope this helps.

    John

    Thursday, May 23, 2013 3:41 PM
  • When you open project, be sure to check the "Load Summary Resources Assignments" on the login screen.  If you don't see this login screen, you need to go to FILE / INFO and under accounts select "Chose an Account".   I think this gives you more summary information for resource shared pool.

    If not, then perhaps someone else can give you a solution.

    Or best solution.  See about installing Project Server.  Resource management is one of its best features.

    Cheers!


    Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Friday, May 24, 2013 2:53 AM
    Moderator
  • Michael,

    As far as I know, that only applies if the user is already connected to Project Server. No such option exists for Project Pro not connected to Project Server.

    John

    Friday, May 24, 2013 2:55 PM
  • Thanks John for you feedback.

    Another solution for Shared Pool is, I think if you open all the projects that the resources are assign to, you will be able to aggregrate their work capacity.

    Cheers.


    Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Friday, May 24, 2013 3:40 PM
    Moderator
  • A very belated thank you for your answer!  This effort got put on the back-burner which is why I had not replied earlier.  Now it's back in the spotlight. 

    I do not have SP1 and am not set up to load it immediately, so could not follow your advice.  However, I found something reasonably workable that raises another question.

    After tweaking the Resource Usage view to show the right timescale and Percent Allocation instead of Work, along with some additions to the view columns, I had a very nice report.  The problem was in trying to export that to Excel for additional clean-up.  I have fretted with the Report setup, exporting to Excel with the associated mapping, etc. but something was always missing.  I suspect I'm still unfamiliar with some of the procedures here.

    Fortunately, I was able to copy and paste directly from the MSP view into Excel, and this gave me what I needed.  If there's an easier way to export, I'd love to know. 

    The columns I have for the view are Resource, Task Summary Name, Task Name, Work & Overallocated.  The right hand pane has a weekly timescale and Percent Allocation.  I just did 2 copy-and-paste operations--one from the left pane and one from the right, to consolidate into Excel.  There has to be a more direct way, but after failing multiple attempts with the other MSP functions and running out of time, this worked out OK.

    Sunday, November 17, 2013 11:24 PM
  • A very belated thank you for your answer!  This effort got put on the back-burner which is why I had not replied earlier.  Now it's back in the spotlight. 

    As I replied to John, I couldn't take advantage of his direction since I do not have SP1 and am not set up to load it immediately. We do not use Project Server, either, and based on other reading I believe John is correct that the approach you suggest doesn't work without it.

    However, I found something reasonably workable that raises another question.

    After tweaking the Resource Usage view to show the right timescale and Percent Allocation instead of Work, along with some additions to the view columns, I had a very nice report.  The problem was in trying to export that to Excel for additional clean-up.  I have fretted with the Report setup, exporting to Excel with the associated mapping, etc. but something was always missing.  I suspect I'm still unfamiliar with some of the procedures here.

    Fortunately, I was able to copy and paste directly from the MSP view into Excel, and this gave me what I needed.  If there's an easier way to export, I'd love to know. 

    The columns I have for the view are Resource, Task Summary Name, Task Name, Work & Overallocated.  The right hand pane has a weekly timescale and Percent Allocation.  I just did 2 copy-and-paste operations--one from the left pane and one from the right, to consolidate into Excel.  There has to be a more direct way, but after failing multiple attempts with the other MSP functions and running out of time, this worked out OK.

    Sunday, November 17, 2013 11:25 PM