Adding users assigned to specific roles within a task list to a project report

[MikeMcGovern]

My client is interested in understanding who is assigned to specific roles within a project, but within a project report. See an example below:

Project Name	Designer Assigned	Writer Assigned	Editor Assigned
2024 Adobe Summit Creative	Joe Smith	Wanda Whitmore	Mike McGovern

The two options I presented was to query the list of project team members and populate the cell with users on the project with job roles that match the column OR query the nested task list for specific tasks assigned to that job role and pull the assignments in those columns. Both comes with their own collection of benefits and drawbacks.

How have you achieved this for your own reporting needs?

[skyehansen]

We have one additional workaround that I don't necessarily recommend... but here it is: We add one more task to every project that lists the roles we're planning to assign to that project and assign the people we're planning to use in the project, to that task. I was thinking, maybe it would be easier for you to query that task and those people -- might have fewer drawbacks than your other two options. The downside of course is, now you have an additional task to keep track of and an additional process within each project. 

[MikeMcGovern]

Thanks Skye! Unfortunately, I think my client will be unwilling to add the additional task but I appreciate the idea and will add it to my "toolbelt".

[Sujatha_TS · Accepted solution]

Hi Mike, 

Here is what I have used. 

displayname=Designer Assigned
listdelimiter=
listmethod=nested(projectUsers).lists
textmode=true
type=iterate
valueexpression=IF(CONTAINS("Designer",{user}.{role}.{name}),{user}.{name})
valueformat=HTML

This will bring in all 'designers' assigned to complete that particular project. 

Hope this helps! 

[MikeMcGovern]

Thanks! I used your text mode to the create the one below so that I rely on job role IDs instead of names, since they can change.

displayname=Copywriter
listdelimiter=
listmethod=nested(projectUsers).lists
textmode=true
type=iterate
valueexpression=IF({user}.{roleID}="93ce4f44a920ca4de0530a093a0a54f7",{user}.{name})
valueformat=HTML