How can I get a "view" or "report" that calculates and shows custom fields for resource usage instead of "Work"hours? RRS feed

  • Question

  • Disclaimer: I'm a noob when it comes to Microsoft Project.  I also don't know, yet, how to speak MS Project lingo, so please excuse me if I'm describing something incorrectly.

    I've spent the better part of two days looking for a solution.

    My project context:

    Resources: "Development" and "Production".  These are not people, but an activity type.  Our operations team as a whole works in either development or production.

    Tasks: individual segmented activities that have custom fields with physical attributes and descriptive attributes.  

    Custom field values: are manually calculated in Excel and then copy/pasted into the correct task/field in MS Project.

    Durations are manually calculated in Excel based on work rates (3 feet/day, for example for development group) and then copy/pasted into the correct task/field in MS Project.


    Task 1 (nameA), Custom field-distance in feet (40), Resource (Development)

    Task 2 (nameB), Custom field-weight in lbs (200), Resource (Production)

    Task 3 (nameC), Custom field-distance in feet (70), Resource (Development)

    Task 4 (nameD), Custom field-weight in lbs (800), Resource (Production)

    I have my entire project created (hopefully correctly), scheduled, and leveled so as to not over allocate resource capabilities.  Development resource is set to 200% max units, signifying that the project can allocate two development activities to be performed at one time, regardless of complete or partial overlapping.  

    The Problem/Ask:

    I want to view, or create a report or Visual Report, etc. where the physical custom field values are shown; not the work details (hours worked).  I don't care about the work hours. For an example, if Task 1 is half done in a specific time window (i.e. week 2, Qtr 1, 2017, etc.), I want to see 20 feet developed, not 50% of the total work hours.  If at the same time Task 1 is half complete, Task 3 is 10 feet in, then I want to see the summary of total footage developed in that time period; which would be 30 feet developed.

    I am sure I can probably copy/paste the resource usage work details out of Project and into Excel to then manually calculate the physical attributes, but I'm trying to find a more logical, simple and efficient way of doing this.

    What I've explained is really simplified, but I'm hoping for some guidance so that I can apply what I hope to gain here to the other aspects of this project schedule reporting.  In actuality, I have 4 custom fields with physical characteristics and more with descriptive "buckets".

    snap shot of my current 'resource usage' view that shows calculated work hours.


    Tuesday, December 27, 2016 10:37 PM

All replies

  • rmargvos,

    The first thing I have to ask is, why do you want to use Project? Based on the description of what you want out of the application and the fact that you are manually "scheduling" your stuff, the better application is Excel.

    If you are expecting to time scale your custom fields similar to the Resource Usage view, you are out of luck. Custom fields can only be shown in aggregate (i.e. single valued). Why? Because Project has now way of "knowing" how to distribute the custom values over the time period.

    Probably not the answers you wanted but you asked for guidance so my guidance is, use Excel.


    Wednesday, December 28, 2016 12:04 AM
  • I assumed since that the Work field calculates the portion of hours; such as 50 hours worked in that period of the 150 total hours for that task, that I should be able to use that percentage (work hours/total hours or 33.3% in this example) to multiply and sum up my custom fields.

    The scheduling and leveling of tasks was pretty simple.  It's just this next step of being able to report the tangible schedule; not work hours.  I guess today will focus on trying to figure out how to report or export the work hours (pic in OP) and work some Excel.

    The best solution, which is not an option for me at the moment unfortunately, is to use actual mine planning software. But since i don't have that, I'm trying to use this. 


    Wednesday, December 28, 2016 2:39 PM
  • rmargvos,

    Well you can use the percentage of hours worked as a multiplier for any custom field, you just can't have Project spread that over the period. You can however show the composite value. For example, in your scenario for "task 1", the custom field is 40 feet of distance and that task spans 10 working days. For linear progress, that would be 4 feet per day. On day 5, you declare 40% complete which translates to 16 feet of progress instead of the 20 feet expected by day 5. Is that what you are after?

    Project calculates a schedule using the work equation:

    Duration = Work/Units

    Where duration is the time span during which the task will be done, work is the effort required to accomplish the task, and units are the number of resources assigned.


    Wednesday, December 28, 2016 4:56 PM
  • Hello John,

    My final result needs to resemble the image below.  I need summarized my custom fields, with formula logic to put the correct values into the appropriate column/row.  Project calculates the work hours for individual tasks, I then need to then calculate footage if it is development task or tonnage if production, these values get once more separated if it is ore or waste depending on grade, etc.

    You are right in that MS Project is not the ideal tool. But I didn't have time to wait for procurement to get me the proper tool.

    Snapshot of simple abbreviated mining schedule


    Wednesday, December 28, 2016 5:49 PM
  • rmargvos,

    How did you prepare the screen shot? Excel? Then I think that answers your question. Excel is the more appropriate application for what you want to do.

    Why do you want to use Project at all? Are you scheduling tasks and tracking their completion? For example, how does a foot or ton of ore relate to work hours or elapsed time? Note, in Project duration is a span of time and that's what the Percent Complete field measures whereas work is the effort expended in the duration and work is measured by Percent Work Complete.

    If there is a better industry tool that does what you need, then I'd press for that rather than trying to mold Project into something it isn't.


    Wednesday, December 28, 2016 8:19 PM