How to configure a column which lists all the users who have a role in the containing project

Introduction

The default activity list, lists all the users that can access JIRA. If you want to restrict this list to the users who have a specific role in the JIRA project, you will have to do some SQL plumbing

Solution

The column specification is as follows

col.responsible                  = Responsible
col.responsible.type             = list
col.responsible.query.ds         = jira


#
# The query is using project:key to select the users from the projectroleactor table
# who have a role in the project
# Only lists explicit configured users.
# 
col.responsible.query.parameters = project:key

col.responsible.query   = SELECT cu.display_name, cu.id \
							from cwd_user cu \
					  inner join projectroleactor pa on cu.user_name = pa.roletypeparameter and pa.roletype = 'atlassian-user-role-actor' \
			          inner join project pr on pa.pid = pr.id \
						   where pr.pkey = {0}

The *project:key* is one of the issue values that can be used to construct a query. The resulting grid looks as follows (the drop down is listing only the members which are
currently working on this project)

The complete picture

gd.columns=milestone,ainitial,acurrent,completeness, responsible
gd.ds = jira
gd.tablename = milestones

col.milestone=Key Milestone
col.milestone.type=String
col.milestone.width = 300

col.ainitial=Initial Delivery
col.ainitial.type = Date

col.acurrent=Current Delivery
col.acurrent.type = Date

col.completeness=%Compl
col.completeness.type=integer
col.completeness.min = 0
col.completeness.max = 100

col.responsible = Responsible
col.responsible.type = list
col.responsible.query=SELECT cu.display_name, cu.id \
                        from cwd_user cu \
                        inner join projectroleactor pa on cu.user_name = pa.roletypeparameter and pa.roletype = 'atlassian-user-role-actor' \
                        inner join project pr on pa.pid = pr.id \
                        where pr.pkey = {0}
col.responsible.query.ds=jira
col.responsible.query.parameters = project:key