Need help with Sub formula in Project Level Report

[mvsteep]

Hi Friends!

In a project level report, we are trying to create a custom column that portrays the difference between 2 custom fields. The below are the custom fields (their names in bold, sentence below is how the custom fields are calculated):

PIM | Task Cycle Time to In Market Date  - Work Min Diff

ROUND(WORKMINUTESDIFF({project}.{program}.{DE:PIM | Program In Market Date},{handoffDate})/480)

PIM | Program SLA Sum

SUM({DE:RED | T-Shirt SLA},{DE:PIM | T-Shirt Deployment},{DE:PIM | Engagement SLA},{DE:PIM | Creative Brief SLA},{DE:PIM | Strategy SLA})

Below was our proposed difference column between these two fields:

displayname=Cycle Time Variance from SLA
valueexpression=SUB({DE:project:program:PIM | Program SLA Sum},{DE:PIM | Task Cycle Time to In Market Date - Work Min Diff})
valueformat=HTML

When this runs, it only recognizes the value that is in the "DE:PIM | Task Cycle Time to In Market Date - Work Min Diff" field. So it feels like something is off with the "DE:project:program:PIM" portion.

Please let me know your thoughts!

[skyehansen]

I think possibly what you need is in the first table of this page, and the short example under the table:

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/calculated-custom-data/...

i.e. You're working with valueexpressions, so use periods and curly brackets instead of colons and parens.

[mvsteep]

@skyehansen thanks for your reply! I took your advice and adjusted the formula to the following, but this time it's generating no results. thoughts?

displayname=Cycle Time Variance from SLA
valueexpression=SUB{{DE.project.program.PIM | Program SLA Sum},{DE.PIM | Task Cycle Time to In Market Date - Work Min Diff}}
valueformat=HTML

[skyehansen · Accepted solution]

I can see why you went there, but read the table and example more closely, as it specifically talks about the syntax for field names, not your entire valueexpression. It will be really valuable if you get full understanding from the link, since many admins spend most of their time in crafting valueexpressions and this link is a critical building block to their knowledge.

your valueexpression should break down to something more like this:

1) Any function you use, will follow the syntax shown on the link I provided. In many cases, this looks like "FUNCTIONNAME(operator1, operator2)" 

2) Any custom field located on the object you are reporting on, will follow the syntax "{DE:YourFieldName}"

3) Custom fields located on other objects, will follow the syntax "{object}.{DE:YourFieldName}"

A sample syntax would look like FUNCTIONNAME({object}.{object2}.{DE:YourFieldName},{DE:YourFieldName2})