CONCAT function and If then expressions

[HTSchlegel]

Hi,

I'm just getting started with custom expressions in text mode so my questions may be basic.

I have 2 fields on an hour report that I am having trouble with.

The first is that I have a project # column and a project name column that I would like to concat into one column that displays the project number and the name together.  I feel like this should be fairly simple but I must have part of my syntax incorrect.  It just comes up blank. I'm struggling with what the listmetod and value format should be and wonder if that is where I am going wrong.

displayname=Project 
listdelimiter=<div>
listmethod=nested(projectUserRoles).lists
type=iterate
valueexpression=CONCAT({project:Project #},":",{project:name})
valueformat=customDataLabelsAsString

The text mode of the 2 fields I am trying to concat are:

linkedname=project
namekey=view.relatedcolumn
namekeyargkey.0=project
namekeyargkey.1=Project #
querysort=DE:project:Project #
valuefield=project:Project #
valueformat=customDataLabelsAsString

linkedname=project
namekey=view.relatedcolumn
namekeyargkey.0=project
namekeyargkey.1=name
querysort=project:name
valuefield=project:name
valueformat=HTML

The second thing I am wanting to do I think would involve an if..then.  There is an hour type column that either shows an hour type name or "Project Time" or "Task Time".  

I would like a column will show the hour type name unless hour type is "Project Time" or "Task Time". If it is project time or task time, it would display the concat Project # and Project name that I am trying to do above. 

The text mode for the hour type field is:

listsort=nested(hourType).HTML(name)
namekey=hourtype
querysort=hourType:name
valuefield=hourType:name
valueformat=HTML
width=95

I appreciate any guidance!

Thanks

Heather

[Madalyn_Destafney]

Hi there, you actually don't need a value expression in a column at all to combine columns. You just need to add a bit of text mode to one of the columns to share the columns so that the values can be presented together in 1 column.

Put the project # and the project name columns side by side in your report. Then in the left-side column between the two, go into text mode of the column and add:
sharecol=true
If you want to take it further and have them together with a linebreak in between, check out this article.

Regarding the hour types, you could simply add a filter to your hour report (assuming you're making an hour report to remove the hour types you don't want to see. If you don't want to weed them out from the report, then you could just sort the report by hour type and for all the project/task time rows you refer to your project name/# column?

[HTSchlegel]

Hi Madalyn,

Thanks so much.  The merging of columns works but I will be exporting this to excel and it separates once it is exported.  That is why I was thinking concat may work better.

We will be using this report to populate a  PBI dashboard so I'm trying to get as much information setup in the export itself.

For the Hour type, we won't be filtering by that.  I do currently have it sorted by type but the information that I am trying to get is currently in 3 different columns.  The hour type will show the 3 types I mentioned, then the source shows the actual task if it is 'Task Type' or the project name if it is 'Project Type'.  I then have to use the project name field if it is a task type to get that information.  I'm just trying to piece those 3 options into one for the export.  I'm not sure if that makes sense without seeing it.

I appreciate the suggestions!

Heather

[Madalyn_Destafney]

You could have a calculated field that lives in a custom form that's on all your projects (or at least the projects you're capturing - if you already have an existing project custom form on all of them, add this field to it) but simply concats the project name + project number (assuming the reference number, otherwise you'd use your custom # field in place of the ref #). Use the text format and it would look like this:

 Then you'd use this calc field as your field of your column in your report. You won't be able to add hour type in this way bc unfortunately there isn't a custom form option on hour types. But this can get you 2 of the 3 combined, then you use that as your field that shows them together and won't break when exporting to excel bc WF sees it as 1 field (try testing it on 1 project first and see if it handles the export ok, don't quote me on that).

[KellieGardner]

@HTSchlegel -

For your first formula to combine the name and reference number it should be formatted like this:

valueexpression=CONCAT({project}.{DE:Project #}," : ",{project}.{name})

For your second formula you can use this:

valueexpression=IF({hourType}.{name}="Task Time" || "Project Time",CONCAT({project}.{DE:Project #}," : ",{project}.{name}),{hourType}.{name})

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

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

[HeatherSc2]

Thanks so much.   That works to add the project if project or task but the hour type is showing blank if it is not project time or task time.  Any ideas there?

Thank you!

Heather

[KellieGardner]

Sorry about that, I left off part of the formula when I typed it out.

valueexpression=IF({hourType}.{name}="Task Time" || {hourType}.{name}="Project Time",CONCAT({project}.{DE:Project #}," : ",{project}.{name}),{hourType}.{name})