Creating a column in a task report that shows the users job role on a task

[SethPorrazzo]

I've created a task report that uses a $$USER.ID filter to only show the user which tasks they are assigned.  However, I'm running into a bit of a hurdle when I'm trying to display their role on the tasks.  It doesn't always show the correct role. I'm using the following line to display the correct role, but it isn't working as planned.

displayname=Job Role
linkedname=assignedTo
namekey=view.relatedcolumn
namekeyargkey.0=assignedTo
namekeyargkey.1=role:name
querysort=assignedTo:role:name
textmode=true
valuefield=assignedTo:role:name
valueformat=HTML

would anyone be able to assist me, or explain to me what I'm missing here?

[GrahamJarrett · Accepted solution]

If you filter not the AssignedTo, but the Assignments...

assignmentsUsersMM:ID=$$USER.ID
assignmentsUsersMM:ID_Mod=in

and then have the collections column...

link.linkproperty.0.name=ID
link.linkproperty.0.valuefield=ID
link.linkproperty.0.valueformat=val
link.lookup=link.view
link.value=val(objCode)
listdelimiter=<br>
listmethod=nested(assignments).lists
name=Assigned
stretch=0
textmode=true
type=iterate
valueexpression=IF({assignedToID}=$$USERID,CONCAT({assignedTo}.{name}," [",{role}.{name},"]"),"")
valueformat=HTML
width=300

this will show just the User that is the loggedIn User and their role... even if there are other people also assigned to the task

[SethPorrazzo]

thanks for the help Graham.  A couple of quick questions, the following is referencing the report filter yes?  

assignmentsUsersMM:ID=$$USER.ID
assignmentsUsersMM:ID_Mod=in

This report works great, but I have noticed a few rows where the user's name is displayed but no role.   I checked each task and they are indeed assigned to a role but nothing showing up in the report (occurs on 25% of rows).

[GrahamJarrett]

yes that is correct. I just switched to Text Mode on that just so that you could grab that part of the filter more easily.

If you look at this example, on the first row, I was assigned the role, but on the 2nd, the role was on the task but I was not assigned the role. Thus in the final column on the 2nd row, it shows just the square brackets without the role in it.

Perhaps this is also the case in your report?

(As an aside you can see on the first row there was also someone else on the task, but they do not show on the final column)

In the valueexpression you could also mark whether you are the primary for the task by adding in an IF({isPrimary}=1," [Primary]","")

[SethPorrazzo]

please disregard my previous update... went into each task and made sure each user was assigned a role then recalculated expressions got everything to work properly.