Grouping: display the result of aggregating multiple calculated values in a grouping
You can use text mode in a column to display a calculation between two fields in the view of a report or list. Each line displays the calculation for each object in the report or list.
For example, you can display the difference between Actual Hours and Planned Hours in a third column called Work Balance for each task in a task report. For more information about calculated data expressions, see Overview of calculated data expressions.
You can display the aggregated value of multiple calculated view items in the same column in a grouping by adding a calculation to the aggregator
line of the column that contains the calculated value. For example, you can aggregate (display the sum of) the amount of Work Balance hours of all the tasks in the grouping of the report or the list for the Work Balance column. This article describes how to do this.
Access requirements
You must have the following access to perform the steps in this article:
*To find out what plan, license type, or access you have, contact your Workfront administrator.
Display the result of aggregating multiple calculated values in a grouping
-
Go to a task report, click Report Actions > Edit.
-
In the Groupings tab, click Add Grouping, and start typing Project Name in the Group your Report > First by field, then select it when it displays in the list.
-
In the Columns(View) tab, click Add Column, then start typing Planned Hours in the Show in this column field, then select it when it displays in the list.
note tip TIP Always start adding as much information using the Standard interface before you edit information in text mode. Add fields that are closest to or contain the most amount of information that for the calculation you are trying to make. -
In the Summarize this column by field, select Sum, then click Done.
-
Click Switch to Text Mode in the column you added.
-
Hover over the text mode area, and click Click to edit text.
-
Replace the
valuefield
and theaggregator.valuefield
lines with the lines highlighted in the following text mode example:code language-none valueformat=compound aggregator.displayformat=minutesAsHoursString aggregator.valueexpression=ROUND(({workRequired}-{actualWorkRequired}),2) aggregator.function=SUM aggregator.valueformat=val aggregator.namekey=workrequired linkedname=direct textmode=true valuefield=workRequired namekey=workrequired valueexpression=CONCAT(ROUND(({workRequired}-{actualWorkRequired})/60,2)," Hours") viewalias=workrequired displayname=Work Balance
note tip TIP In order to get the aggregated value in the grouping to display the aggregated difference between the Planned Hours and Actual Hours fields, input the same equation into the aggregator.valuefield
line. Theaggregator.displayformat
used for the Planned Hours column converts minutes to hours. Because the Planned Hours field was used as a placeholder, this line doesn’t need to be adjusted.The minutesAsHoursString
definition of theaggregator.displayformat
line means there is no need to divide each field by 60 as done on thevalueexpression
for the results. In thisaggregator.valuefield=workRequired
becomes:aggregator.valueexpression=ROUND(({workRequired}-{actualWorkRequired}),2
. -
Click Save+Close.