Looking for some text mode magic

[Heather_Kulbacki]

I have this text mode column in a report to give me an allocated cost

displayname=Allocated Cost

textmode=true

valueexpression=IF({project}.{DE:GL Code}="Strategic Marketing - 400.5304.8510.83326",{actualCost},IF(CONTAINS("50% 400.5304.8510.83326",{project}.{DE:GL Code}),({actualCost}*.5),IF(CONTAINS("52% 400.5304.8510.83326",{project}.{DE:GL Code}),({actualCost}*.52),IF(CONTAINS("34% 400.5304.8510.83326",{project}.{DE:GL Code}),({actualCost}*.34),IF(CONTAINS("54% 400.5304.8510.83326",{project}.{DE:GL Code}),({actualCost}*.54),IF(CONTAINS("50% Strategic Marketing - 400.5304.8510.83326",{project}.{DE:GL Code}),({actualCost}*.5)))))))

valueformat=currencyStringCurrency

yeah, that's a lot of IFs, but it works.

Is there any text mode magic I can do to aggregate this column with a SUM?

I have an Actual Cost column with a SUM and when I look at the text mode for that, there's a row:

aggregator.valuefield=actualCost

I feel like I should be able to replace that with:

aggregator.valueexpression=my big long IF statement

But I tried that awhile ago and it didn't work. Any thoughts on if this is even possible?

[AnthonyIm]

Hi - unfortunately (to the best of my knowledge) you cannot aggregate custom columns. Since the info is not living on a database anywhere, it is hard to get the SUM.

Do all the tasks in your projects have a custom form already? If so, you might want to take your IF statements and put them into a calculated field. Then you can use that to aggregate and get SUM.

[Doug_Den_Hoed__AtAppStore]

Ehm..."impressive" (question mark?) Heather,

Glad it works, but no, I don't believe the SUM function is capable of interpreting the valueexpression in that way (although I'd be happy to be proven wrong). Instead, noting that it would allow for other cool uses (PRO: filter, group, chart, etc.) but does have some some drawbacks (CON: stale data, requires recalculation), I'd suggest moving your IF statement into a calculated currency custom parameter, at which point SUM would then be trivial, off the valuefield.

Regards,

Doug

[Doug_Den_Hoed__AtAppStore]

Man, "Anthony" and "Doug" sure are going to a lot of trouble to make it appear that we are two separate people...

Regards,

"Doug"

[Heather_Kulbacki]

I was hoping the two of you would respond, or at least I think it's two separate people ;)

But was guessing I'd get the 'unfortunately, no' answer.

Currently we're pulling the report to Excel to get the sum, but I'd love to keep people right in Workfront.

I'll have to put some thought into a task and issue form. Time also gets billed direct to the project but adding another field to one of those forms is easy. And I could set up this group's templates to add that form on any new tasks and issues. But then I'd want a way to prevent project managers from creating a project without a template - pretty sure I've upvoted that idea in the Innovation Lab already.

I'm going to add this to my notes for my next 1:1 with the New Reporting team for now.

[ChrisBudgen]

If you are using Fusion or built some custom code to work with the API, you can force the recalc off those fields in automated intervals. This should at least remedy the "stale data" issue :-)