PM Task Completion Rates report

[LaraMc]

I am fairly new to work front and have figured out how to build new reports but only from an available field level. I do not know how to do any text mode/SQP coding and am thinking that the below report would require that in order to display what is being asked for. I am looking to build a new report with the following parameters: PM Task Completion Rates A report by project manager, that lists each project they have worked on (closed or open, level 1 & level 2) and for each project sums the number of tasks each project had, and then in the next column sums the total number of tasks completed on time, next column sums total number of tasks completed late, next column total number still open; With a total line at the bottom for all projects combined Any help or guidance would be appreciated. Lara McCleary SR Project Manager/WF Adminstrator Aetna Tampa, FL 203-988-5089

[Doug_Den_Hoed__AtAppStore]

Hi Lara, That's an interesting report you've described. At first, I didn't believe it would be possible, but as I mulled it over a bit, now think I might have a solution to offer. First, for background, I'd invite you to read about: "https://support.workfront.com/hc/en-us/articles/217170837-Creating-a-Matrix-Report">Matrix Reports "https://support.workfront.com/hc/en-us/articles/216668578-Understanding-Task-Statuses">Task Status "https://support.workfront.com/hc/en-us/articles/216636328-Understanding-Progress-Status-of-Tasks">Task Progress "https://support.workfront.com/hc/en-us/articles/216598108-Working-with-Prompts">Report Prompts With those concepts in hand, I'd then suggest you create a Task Level Matrix Report with settings to: View the Project Manager Name, Project Name, Project Status Filter for Projects that are "closed or open, level 1 & level 2" Group Rows by Project Manager, Project Group Columns by Task Status, Task Progress (Optional) Prompt for Project Manager, and/or Task Entry Date The latter would (for example) allow you to look at "Doug's stats on Tasks entered between Jan 1, 2017 and Dec 31, 2017". You might also consider Group Columns by Task Entry Date (to the Year), then by Task Status, Task Progress, for comparative purposes. Regards, Doug P.S. BONUS: this would make an interesting chart, too: by PM (e.g. Doug, Lara, etc.) and Task Status (e.g. Open, In Progress, Closed) along the x-axis, stacking the number of Tasks that by Task Progress (e.g. On Time, At Risk, Behind, Late) Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[Doug_Den_Hoed__AtAppStore]

NOTE: Lara M sent me this via a chat, but it's a good question, so I'm copying it in here and will then reply to it so others can follow along -------- HI Doug, I appreciate you taking the time to think about my request and provide an answer. As I started to build this out I realized that when you pull in a task level report as your base the project owner name does not appear in the column search when trying to add a new column. The owner fields do not appear in the searchable table at all. I tried copying the text for that field from another report and adding the column in as text mode but no data actually populates in the field. Not sure how to get around this limitation. Any suggestions? Here is the text I used for the owner field: descriptionkey=owner link.linkproperty.0.name=ID link.linkproperty.0.valuefield=owner:ID link.linkproperty.0.valueformat=int link.lookup=link.view link.valuefield=owner:objCode link.valueformat=val linkedname=owner listsort=nested(owner).string(name) namekey=owner querysort=owner:name section=0 shortview=false stretch=0 valuefield=owner:name valueformat=HTML width=150 Lara McCleary SR Project Manager/WF Adminstrator Aetna Tampa, FL 203-988-5089 Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[Doug_Den_Hoed__AtAppStore]

My pleasure, Lara, and good question. You've hit upon one of the "https://support.workfront.com/hc/en-us/articles/235635048-Most-Requested-Reporting-Text-Edits">Most Requested Reporting Text Edits : namely, to switch an ID that you can see in the Report View builder (in this case, a Task's Project's Owner ID) into some more meaningful attribute about related to that ID (in this case, the Owner Name instead of the Owner ID). To do so in your case: add the Owner ID as a column in your Report's view, which will Get You Close highlight that new Project: Owner ID column and switch it into Textmode click to edit the text replace each occurrence of owner:ID to owner:name NOTE: in doing so, rather than referring to the ID that is "on" the Project as a foreign key, your "hopping" to the related foreign owner record in the database (or object, if you prefer) and then retrieving its name column (or property, if you prefer) save the view When did this as I recorded the above steps, it resulted in the definition below, and worked as expected (although I added displayname=Project Owner after the fact and re-saved), including the bonus surprise that the resulting Project Owner was rendered as a clickable hyperlink, too. Regards, Doug valuefield=project:owner:name querysort=project:owner:name valueformat=HTML displayname=Project Owner linkedname=project namekey=view.relatedcolumn namekeyargkey.0=project namekeyargkey.1=owner:name Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[LaraMc]

Thank you Thank you Doug! The report is almost perfect. One last question, I am trying to either filter or prompt by portfolio name and am having the same issue that it does not appear in the search options, I am assuming again because it is a task level report. Is there a way to get around that issue and be able to pull in that field portfolio name to either filter or prompt on? Lara McCleary SR Project Manager/WF Adminstrator Aetna Tampa, FL 203-988-5089

[Doug_Den_Hoed__AtAppStore]

Wonderful, Lara: I'm glad it is taking shape! Since Portfolio Name is too many "hops" away to pull into a prompt natively, I'd suggest you use a "https://support.workfront.com/hc/en-us/articles/216598108-Working-with-Prompts">Custom Prompt . Assuming you have a relatively short and stable number of them, you'd create one drop-down entry for each Portfolio, with its accompanying Power Search doing the real work by filtering for the selected Portfolio by restricting the report to only those Tasks whose project:portfolioID matches the (here's the trick) corresponding hard coded ID. For example, for Custom Prompt dropdown "Portfolio A", the Power Search entry might be: project:portfolioID=1842ab524ca5a843&project:portfolioID_Mod=eq Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[LaraMc]

Woo Hoo! I actually understood what you were saying and got it to work! Thank you so very much! Lara McCleary SR Project Manager/WF Adminstrator Aetna Tampa, FL 203-988-5089

[Doug_Den_Hoed__AtAppStore]

Haha.That's awesome, Lara -- glad to hear it. Now that you have the "purist" approach working, there is another cheat I should mention. When faced with "too many hops", another "http://store.atappstore.com/2010/12/hows-my-portfolio/">common approach is to "bring data closer". In your example, if you added a custom calculated Parameter to the Project Custom Form called "Reporting Portfolio" with a formula of Portfolio Name, that would effectively "pull" the Portfolio Name onto the Project...bringing it within reach of the native Prompt screen...and therefor allowing you to simply prompt on Reporting Portfolio. Now, should your calculation every become "stale" for some reason, it's possible that the Project might then appear under an old Portfolio rather than the current one; but that aside, the cheat might be easier to maintain. Your call. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[CameronHo]

Doug, This thread has proven to be very helpful for me in my needs. Thanks for the input so far. Is it possible to incorporate a calculated column in a Matrix Report? I would like to have this type of report deliver automatically the percentage of tasks completed on-time vs late. Cameron Howitt

[Doug_Den_Hoed__AtAppStore]

Hi Cameron, Although standard list and matrix reports in Workfronts handle average, sum, count, min, and max aggregates, percentages are not. You could stick with sums, or consider using a pie chart, which is the one chart that's designed to show percentages. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads