Forum Discussion
Show correct %-Allocation in Resource Usage for a specific project when using Resource Pool
John-project Thanks, this is an option I didn't consider. I tried it but I got the same result for Res1.
Using Assignment for Project and Resource for Name (%-Allocation is empty):
Using Assignment in both cases:
The %-Allocation value is provided for Res1, but the value is wrong, it is not by group.
Would you try with the same sample files I shared just to double check. Thanks!
Please advise, and thanks again for your response!
Sorry for the delay in responding, I got a lot of things going on.
I did try your files and indeed I see the issue about % Alloc not appearing for a resource that has more than one assignment in the project. You also mention something about the % Alloc value being wrong. That I don't see. Your screen shot of the Resource Usage view is kind of obliterated by overlay of the group definition windows. This is what I see when the timescale is expanded to show weekly data. Unless the "lot of things I have going on" is clouding my vision, I don't see any issue with % Alloc for Res1.
I tweaked my own test files to more closely parrot your file and what I see is that when a resource is assigned to more than one task in a given Project, the % Alloc value is missing from a grouped display. Why this happens, I have no idea and I have no tricks on how to make it happen.
So, what would I do if I wanted a Resource Usage view that delineated each resources % Alloc by project in a dynamic master? I'd use VBA to export the data to Excel in a format similar to what you'd expect to see with the grouped Resource Usage view. I already have a macro that exports resource data from a master file with a resource pool but it would have to be modified to break down the format by subproject.
Yeah, probably not the answer you were hoping for but there is a way "outside the box".
John
- I am glad you came to the same conclusion. Since Work is calculated correctly, then knowing the working days per month via the Excel function NETWORKDAYS, the allocation can be calculated. I read that using Project's Visual Reports it is possible to export to Excel, but I haven't tried it. I haven't tried the VB approach, if you share the Excel file for this scenario (pool resource and filtering for a given project only) it would be great. As you said it is not the solution I was looking for, but probably a good workaround. Thanks again for your help on this.
The Excel function NETWORKDAYS will not give the correct result for a couple of reasons. Although the function includes a argument for holidays there is no field in Project that provides Project calendar information. Second, a given resource may also have, in addition to holidays, time off for vacations, etc. Project does however provide the Work Availability timescaled field. From Work and Work Availability, Percent Allocation can be calculated for each timescaled period.
Project's Visual Reports feature does provide an export to Excel of Resource Usage information including a Work Availability report, but, the data is total resource data, not broken down by project and there is no way to include the Project field in the cube data.
I'm not sure what you mean by "share the Excel file for this scenario". As I noted the existing macro I have is not set up to break down the data by Project. In its current state, this is what the Excel export looks like. It was written and optimized for another user's master file structure. Modification is needed to produce the report format you want.
John
- Thanks, I thought you were willing to share the macro if it is broken down by project it would be great, if not you can please share it, I am not a VBA expert, but I will see what I can do.
You are correct about NETWORKDAYS, it is a simplification, but INMO there is no other way to do it, because MS Project as we know now, doesn't calculate %-Allocation in certain scenarios, so we can not rely on this calculation. The only calculation we know is correct by projects is Work, so we can calculate a specific allocation, say %-Theoretical Allocation, since it doesn't consider the particular resource constraints such as vacation, local holidays, etc. That is what I meant. Export to Excel will do one portion of the work, exporting hours, then find a way to deduce from the hours %-Allocation. It is a pitty that MS Project doesn't provide %-Allocation in all cases. Thanks so much for your help.