The next phase for Workfront Community ideas is coming soon. Learn all about it in our blog!
Group milestone tasks by quarter in project report using text mode.
Hi Workfront gurus,
I have a text mode report that pulls in milestones (zero duration tasks) and groups them according to planned completion date. I can do this using relative dates (greater than, less than today) but I want to group for specific quarters (absolute dates). For example, I'd like to group by Q1 2022, Q2 2022 etc but I can't figure out how to do it.
This is what I have for the relative dates. Any help or guidance on how to change this for specific date ranges would be greatly appreciated.
Incomplete Milestones (tasks) less than 30 days out
column.22.displayname=Column
column.22.sharecol=true
column.22.textmode=true
column.22.value=<div style="padding:5px 5px 5px 5px;border-bottom:2px; background:#cccccc; text-align:center; font-weight:bold; font-size:11pt;border-radius:0px 0px 0px 0px; box-shadow: 0px 0px #858585;"><b>KEY MILESTONES</b></div><div style="padding:3px 3px 3px 3px; border-bottom:0px; background:#f5f5f5; text-align:left; font-size:10pt; border-radius:0px 0px 0px 0px; box-shadow: 0px 0px #858585;"><b>Incomplete (< 30 days out)</b><br>
column.22.valueformat=HTML
column.23.displayname=Column
column.23.listdelimiter=<p>
column.23.listmethod=nested(tasks).lists
column.23.sharecol=true
column.23.textmode=true
column.23.type=iterate
column.23.valueexpression=IF({plannedCompletionDate}<$$TODAYed+30d,IF({numberOfChildren}=0,IF({duration}=0,IF({percentComplete}<100,(CONCAT(" ‚óá ",IF({condition}=0,'<b><font color=03a219>[Green] </font></b>',IF({condition}=1,'<b><font color=e19503>[Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519>[Red] </font></b>',""))),{name}," (Planned: ",{plannedCompletionDate},")"))))))
column.23.valueformat=HTML
Incomplete Milestones (tasks) more than 30 days out
column.24.displayname=Column
column.24.sharecol=true
column.24.textmode=true
column.24.value=<br><b>Incomplete (> 30 days out)</b><br>
column.24.valueformat=HTML
column.25.displayname=Column
column.25.listdelimiter=<p>
column.25.listmethod=nested(tasks).lists
column.25.sharecol=true
column.25.textmode=true
column.25.type=iterate
column.25.valueexpression=IF({plannedCompletionDate}>$$TODAYed+30d,IF({numberOfChildren}=0,IF({duration}=0,IF({percentComplete}<100,(CONCAT(" ‚óá ",IF({condition}=0,'<b><font color=03a219>[Green] </font></b>',IF({condition}=1,'<b><font color=e19503>[Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519>[Red] </font></b>',""))),{name}," (Planned: ",{plannedCompletionDate},")"))))))
column.25.valueformat=HTML
Milestones (tasks) Completed in the past 2 weeks
column.26.displayname=Column
column.26.sharecol=true
column.26.textmode=true
column.26.value=<br><b>Complete (Past 2 weeks)</b><br>
column.26.valueformat=HTML
column.27.displayname=Column
column.27.listdelimiter=<p>
column.27.listmethod=nested(tasks).lists
column.27.sharecol=true
column.27.textmode=true
column.27.type=iterate
column.27.valueexpression=IF({actualCompletionDate}>$$TODAYed-14d,IF({numberOfChildren}=0,IF({duration}=0,IF({percentComplete}=100,(CONCAT(" ‚óÜ ",{name}," (Actual: ",{actualCompletionDate}," | Planned: ",{plannedCompletionDate},")"))))))
column.27.valueformat=HTML
Milestones (tasks) Completed more than 2 weeks ago
column.28.displayname=Column
column.28.sharecol=true
column.28.textmode=true
column.28.value=<br><b>Complete (> 2 weeks)</b><br>
column.28.valueformat=HTML
column.29.displayname=Complete (past 30days)
column.29.listdelimiter=<p>
column.29.listmethod=nested(tasks).lists
column.29.textmode=true
column.29.type=iterate
column.29.valueexpression=IF({actualCompletionDate}<$$TODAYed-14d,IF({numberOfChildren}=0,IF({duration}=0,IF({percentComplete}=100,(CONCAT(" ‚óÜ ",{name}," (Actual: ",{actualCompletionDate}," | Planned: ",{plannedCompletionDate},")"))))))
column.29.valueformat=HTML
Sample of current report:
Topics
Topics help categorize Community content and increase your ability to discover relevant content.
For the benefit of anyone who might be following along and/or interested:
I adjusted the formula slightly to remove wasted white space. The original formula resulted in "blank" lines where there were no milestones. I removed the title "column" and added a space after the entry that only comes into play if there is a line added.
column.4.sharecol=true
column.4.textmode=true
column.4.value= [removed this title line]
column.4.valueformat=HTML
column.5.displayname=Next Quarter (Q+1)
column.5.listdelimiter=<div>
column.5.listmethod=nested(tasks).lists
column.5.sharecol=true
column.5.textmode=true
column.5.type=iterate
column.5.valueexpression=IF({plannedCompletionDate}>$$TODAYbq+1q,IF({plannedCompletionDate}<=$$TODAYbq+2q,IF({numberOfChildren}=0,IF({duration}=0,(CONCAT('<b>Q+1: </b>',IF({condition}=0,'<b><font color=03a219> [Green] </font></b>',IF({condition}=1,'<b><font color=e19503> [Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519> [Red] </font></b>',""))), {name}," (Planned: ",{plannedCompletionDate},")",'<br>'))))))
column.5.valueformat=HTML
