Forum Discussion
Show correct %-Allocation in Resource Usage for a specific project when using Resource Pool
Lets see if this works for you. I didn't use your test files as I already have a couple of my own. I created a master with the two sharer files inserted as subprojects. This is the basic Gantt Chart view:
Then I set up a group for the Resource Usage view. It's a little different from what you have. Note it groups the Project field by assignment
Now, when that group is applied and the Project field is autofiltered for the Sharer1 file, this is the result:
Does that do it for ya?
John
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