Report - Planned Hours vs Hours from Statement of Work

[lhedrick]

Hello all, I am trying to create a new report that is called Planned hours vs hours from the SOW (statement of work). I created a custom form that allows me to input buckets of hours for specific types of work PM hours Creative lead hours Strategy hours UI Hours UX hours Content hours IA Hours QA Hours Now, I want to create a report that displays a list of each project with these hours in columns next to the actual hours logged by the roles responsible for the work in those buckets. This will allow me to see how far off we were in quoting the project from the start. I know I can use planned hours but those are sometimes different than what was in the original SOW. So maybe all 3, SOW hours, planned hours and actual hours (as they are being logged and when the project is complete). anyone ever created a report like this. thanks Layne Layne Hedrick Somnio

[BarryBu2]

I'm going to assume you're logging hours at the task level, as you're mentioning planned hours. If you're allowing people to log hours against the project, that's a whole other can of worms. You're going to have to do a task level report, and 'pull down' the SOW estimates from the project level. You can roll up total planned hours and total actual hours to the project level, but not by role. So the easy bit is a task level report with project name and task name columns. Then add the 'SOW hours' column (in my example, PM hours). You want to aggregate this on Average, because the total amount will be repeated for each task. You also want to group by project name. To add planned hours only for a particular role, add a column, convert to text mode and use: aggregator.displayformat=intAsInt aggregator.function=SUM aggregator.valueexpression=IF({role}.{name}="Project Manager",DIV({workRequired}/60)) aggregator.valueformat=intAsInt displayname=PM Planned Hours querysort=workRequired textmode=true valueexpression=IF({role}.{name}="Project Manager",DIV({workRequired}/60)) valueformat=intAsInt Actual hours is the same, but replace workRequired with actualWorkRequired (note capitalisation). We have to divide this by 60, because it's reporting minutes. (If you use the native planned and actual hours fields, they are 'compound' and return things like '2 Hours'. If you convert to text, it's minutes). Your report will include every task of every project you include (use filters to control what projects you want) but if you collapse them, the summary line shows what you want. Barry Buchanan Work Management Australia

[lhedrick]

OK, now we are getting somewhere. Is there a way to report on the aggregate without having to show each task under a project for that role? Not a huge deal to close it up but if there is a way not to show them at all that would be best. If so, I could then add more columns for other roles but if I have to show all tasks underneath, that might not be understandable. Lastly, how did you get the custom form number (amount of PM) hours to display on the project level. Right now when I close up the project level, I have the planned hours and actual hours aggregate but the hours from the SOW don't. any thoughts. thanks again, almost there. Layne Hedrick Somnio

[lhedrick]

By the way, here is how that column looks now displayname=DP Hours linkedname=project namekey=view.relatedcolumn namekeyargkey.0=project namekeyargkey.1=Digital Producer Hours querysort=DE:project:Digital Producer Hours valuefield=project:Digital Producer Hours valueformat=customNumberAsString So there is no aggregation code included. any thoughts on how to do that? thanks again. Layne Layne Hedrick Somnio

[lhedrick]

Also, is there a way to have the view display with the rows collapsed by default? thanks again. Layne Hedrick Somnio

[BarryBu2]

Dear Layne, The alternative to viewing each row is to show the report in summary view. However, that will only show fields (native or custom), not calculated columns so it won't work in this instance. There is a suggestion in the idea exchange to set whether aggregating reports show expanded or collapsed, but I don't believe it's on the roadmap for development (yet). To get your custom field to aggregate you just need to add two lines to text code: aggregator.function=SUM aggregator.valuefield= DP Hours Variation 1: If the DP Hours field is entered at the request level (it doesn't appear to be listed in your first post, so I'm not sure) then you would use AVERAGE instead of SUM - that is, you want the total to be the same that is listed for every row, since every row is listing the project total anyway. aggregator.function=AVG aggregator.valuefield= DP Hours Barry Buchanan Work Management Australia

[lhedrick]

Thanks so much. OK, your suggestion did not work. Here is what I tried. The last column in the above screenshot is the number from the custom form I created. It cannot SUM at the top line because 40 is the total hours for a PM on this project. That is how many hours we included in the statement of work. 40 is what I am trying to get to show up on the same line as the 21 (planned hours) and 7 (logged hours). Those do sum at the top level. If I can't get the 40 to show up at the top level, it just means I can collapse all the project numbers and it will be a very long report. Here is the original code: displayname=DP Hours from SOW linkedname=project namekey=view.relatedcolumn namekeyargkey.0=project namekeyargkey.1=Digital Producer Hours querysort=DE:project:Digital Producer Hours valuefield=project:Digital Producer Hours valueformat=customNumberAsString I tried replacing all of the above with what you posted

aggregator.function=SUM aggregator.valuefield=DP Hours

I tried adding your 2 lines of code to the above code. linkedname=project namekey=view.relatedcolumn namekeyargkey.0=project namekeyargkey.1=Digital Producer Hours querysort=DE:project:Digital Producer Hours valuefield=project:Digital Producer Hours valueformat=customNumberAsString aggregator.function=SUM aggregator.valuefield=DP Hours By the way, the custom form for this value is input at the project level, not the request level. any further thoughts would be greatly appreciated. thanks again. Layne Layne Hedrick Somnio

[BarryBu2]

Except in rare circumstances, the aggregator.valuefield should be the same as valuefield: linkedname=project namekey=view.relatedcolumn namekeyargkey.0=project namekeyargkey.1=Digital Producer Hours querysort=DE:project:Digital Producer Hours valuefield= project:Digital Producer Hours valueformat=customNumberAsString aggregator.function=SUM aggregator.valuefield= DP Hours project:Digital Producer Hours Barry Buchanan Work Management Australia

[lhedrick]

Thank you again but that did not work either. I did notice that when I copied and pasted what you had (obviously changing out the edit you made) and then clicked save I got this aggregator.function=SUM aggregator.valuefield=project:Digital Producer Hours linkedname=project namekey=view.relatedcolumn namekeyargkey.0=project namekeyargkey.1=Digital Producer Hours querysort=DE:project:Digital Producer Hours textmode=true valuefield=project:Digital Producer Hours valueformat=customNumberAsString Not sure why it automatically adds what is in red above. Also not sure if that matters. But I could not remove it because it just kept putting it back. thanks as always. Layne Hedrick Somnio

[BarryBu2]

Dear Layne, Textmode=true just means you are using text mode (not standard mode) to define the column. It's not a problem that it keeps getting added. When you say it still isn't working, does that mean the aggregate is still showing as blank or are you getting a number that isn't correct? Barry Buchanan Work Management Australia