Confluence has been updated to version 6.15.9

Page tree
Skip to end of metadata
Go to start of metadata

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