Expand my Community achievements bar.

Need help on Chart Report - Showing each Job Role FTE total (Stacked) per working week.

Avatar

Level 3
I need to create a chart report that displays each Job Role FTE total (stacked) within each Work Week (work week on X Axis). Has anyone got any suggestions for me? I've tried a few times myself but am not getting very far with any of the types of reports I have built. Help is greatly appreciated! Regards, Linden
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

15 Replies

Avatar

Level 7
What specifically are you showing on the y-axis? Is it accumulated assigned hours for a particular job role?

Avatar

Level 3
Hi Greg, Ideal would be FTE so we could see totals of each job role required per week. Thanks, Linden

Avatar

Level 7
Sorry, in looking at this closer, I don't know how to do a report showing by week the number of hours planned per job role. Hopefully someone else will pipe in.

Avatar

Level 3
Thanks anyway, Greg. Can you recommend any types of chart display reports that will demonstrate similar behavior?

Avatar

Level 7
Unfortunately I cannot. Hopefully someone else can.

Avatar

Level 10
What you are looking for is very easy to do in Excel. I haven't figured out how to get a stacked bar chart out of WorkFront though. Are you interested in producing it in Excel?

Avatar

Level 3
Lucas, If it’s impossible to produce somehow in WF, Management will review a report from Excel - providing the data comes from WF. I’d love to know what type of data report you’re producing in WF along with how you set this up in Excel. Thank you! -Linden

Avatar

Level 10
Hi: In the attached Word doc, I have the Group Matrix report that creates the data. Then, export the Matrix report into Excel. Then build a stacked bar chart on the data, per the attached example. If you have a lot of roles, the graph is going to get busy. Be advised this report uses the Resource Estimate object. To create this object, go to the Business Case, Resource Estimate, select the resource pool, and set the budget to schedule. If you’re not sure how to create the resource estimate object, let me know, I’ll send more details. Thanks! Eric

Avatar

Level 7
If there was a "Like" button for this, I'd click on it. :-)

Avatar

Level 9
Just FYI... I added this to the Reporting filebase in the Resources section of the Community. In Reply to Greg Beck:
If there was a "Like" button for this, I'd click on it. :-)
-Nate Bagley --- Workfront Community Manager - Work Smart, Work Happy Message me directly at:

Avatar

Level 3
Lucas, Sorry for the delay in getting back to you. I can confirm that Resource Estimates have been added from each project to my appropriate resource pool, so we are good there. I’ve built the Matrix report you suggested; though, I didn’t get the same results as you so I have some questions below: 1. Can you confirm what the scheduled hours column should be calculating? 2. Did you build from a resource estimate type report? 3. Do you have any additional settings in your columns? 4. Is your Matrix showing FTE total value or something different? If FTE, how did you achieve this? Thank you, Linden

Avatar

Level 10
Hi: 1. Can you confirm what the scheduled hours column should be calculating? è The Scheduled Hours should be the hours in the current forecast - the project plan; è The Budgeted Hours should be the hours in the labor budget. This number is visible and adjusted in the capacity planner. The Resource Estimate screen in the Business Case will show the PM the difference between the labor being allotted to the project (budgeted hours) and the labor being requested (current forecast in the project plan); è When you select Set Budget to Schedule in the Resource Estimates screen, the budget number will be the same as the current forecast in the report; è When PMs adjust the project plan, you will have to go back and set budget to schedule. I also find it interesting to look at the differences between the budget (last current forecast) and the current forecast. WorkFront highlights differences in red. It tells me what kind of resource changes the PM is making. But I digress… è I set budget to schedule in the Resource Estimate screen, then I ran the report I had built. Note that Budgeted and Scheduled hours are identical: [cid:image002.jpg@01D1CC5B.684222B0] 2. Did you build from a resource estimate type report? è Yes, I started with a Resource Estimate report. è I tried to get a Kick Start of the report, so I could just email you the text mode code, but I can’t get this report to appear in the KickStart Export screen. Not sure why. [cid:image008.jpg@01D1CC5B.684222B0] [cid:image009.jpg@01D1CC5B.684222B0] 3. Do you have any additional settings in your columns? è Nope. Building the report took less than a minute. The default Resource Estimate report has these four attributes: Alloc. Date, Bud. Hours, Sch. Hour, and ObjId. I add Job Role: Name and delete ObjID. I summarize the Sch. Hours attribute by SUM; è Then I create a Matrix Grouping, group the row on Resource Estimate >> Allocation Date and then group the column on Job Role >> Name; è For the Row Grouping, I group dates by Week and Show weeks with no data: [cid:image010.jpg@01D1CC5B.684222B0] è On my screen, after I select Group Dates by Week, it doesn’t show up on the screen, its blank, but I know it’s there: [cid:image011.jpg@01D1CC5B.684222B0] è I open the Matrix Settings and deselect Show Record Counts and Deselect Show Value Column. [cid:image012.jpg@01D1CC5B.684222B0] è That’s it. Send me a screen shot of your fields and what the resulting report looks like… 4. Is your Matrix showing FTE total value or something different? If FTE, how did you achieve this? è The Matrix report shows the sum of all scheduled hours for a given week (row) for each role (column). I’m not calculating FTE, it’s just straight hours in this report. Having said that, do you want FTE? I haven’t used FTE in a labor utilization calculation, but it’s do-able. Let me know how it goes! Eric

Avatar

Level 3
Hey Lucas, Apologies for delay - I’d been out on medical leave the last week. I’ve tried again with your instructions below - this is working great. I also added the stacked column chart with the group column option - this might help you out, too? Here are the chart options I selected below if you would like to try: [cid:image008.jpg@01D1D167.1ABB8A70] Here is the stacked result: Note - I tried grouping dates by ‘week of year’, but this would not roll over to 2017 so I kept it at ‘week’. [cid:image010.jpg@01D1D167.1ABB8A70] This is almost exactly what I need; however, my managers would prefer to be looking at Job Role FTE instead of the scheduled hours. You mention below that it’s doable. Do you know how? The above is a great start but I know they will keep pushing me for FTE. Another question that’s running through my mind is whether we could build the same type of chart, but based on planned hour totals per job role within the work weeks? The reason I ask is that my PMs are constantly having the change their plan due to the nature of our business, so I fear they will resist this method based on having to always reset their resource estimates. It would be great to understand if this is possible to do with the current plan in a project? Thanks again, this is coming along nicely. -Linden

Avatar

Level 8
I think the trap is making sure 'Group Columns' is clicked - see attached Word document

Avatar

Level 3
Definitely want/ need FTE. I’ve applied the following textmode to get my scheduled hours column to show FTE instead: aggregator.displayformat=doubleAsString aggregator.function=SUM aggregator.namekey=scheduledhours.abbr aggregator.valuefield=scheduledHours aggregator.valueformat=val descriptionkey=scheduledhours displayname=FTE isInlineEditable=false linkedname=direct listsort=doubleAsDouble(scheduledHours) namekey=scheduledhours.abbr querysort=scheduledHours section=0 shortview=false stretch=0 textmode=true valueexpression=ROUND(DIV({scheduledHours},40),3) valuefield=scheduledHours valueformat=doubleAsString width=150 This gives me the FTE result in my column which is great, but does not change my values in the matrix report or the chart. Can we apply textmode to the matrix groupings or chart selections? Cheers, Linden