How to return group names to filter results on dashboard?

[mfrench_guardian]

Here is what I am trying to achieve:

I want a 2D Filter Results gadget on my dashboard which shows "Groups" on the Y-axis, and "Status" on the X-axis. Currently, I can *sort of* achieve this by setting up a filter that shows only issues that are assigned to members of a group. But when I use that filter in a 2D filter results gadget, it shows the assignee name rather than the group name.

I understand why this current gadget works this way, but is there a way to achieve this some other way? Even it requires some development, I'd like suggestions. The ultimate goal is to have a gadget on a dashboard that shows the status of issues for 6 major "Teams", regardless of which Jira project they are under.

[Adam Jacobson]

My approach this would be to use a hidden Groovy Runner custom scripted field to store the group name(s) associated with the assignee.

I'm not a developer, so I couldn't provide you with any code examples. Maybe something on these pages would help, and I'll re-tag this question to hopefully attract the attention of experts like Jamie Echlin & Co. I'm a bit more comfortable with SQL, so in my mind it would be a script populating the data equivalent to something like:

SELECT cwd_membership.parent_name
FROM jiraissue, cwd_membership 
WHERE jiraissue.issuenum = $issuenum
AND jiraissue.assignee = cwd_membership.child_name
AND cwd_membership.parent_name NOT IN ('jira-users', 'jira-administrators')

Hope that helps.

[mfrench_guardian]

Adam, thanks for spending some time on this- I tinkered around in SQL to get the results I am looking for (or close to it) and came up with:

select mycount, JiraGroup, JiraGroupID, issuestatus, pname from (

SELECT cm.parent_name as JiraGroup, issuestatus, pname, count(ji.pkey) as mycount, cm.parent_id as JiraGroupID

from jiraissue ji

left join cwd_membership cm on ji.assignee = cm.child_name

left join customfieldvalue cf on cf.ISSUE = ji.ID

left join issuestatus ist on ist.id = ji.issuestatus

WHERE cm.parent_id IN (10516, 10518, 10514, 10520, 10522)

and issuestatus in (1, 1004, 3)

and cf.CUSTOMFIELD = 10050

group by parent_name , parent_id, issuestatus, pname

) o

group by JiraGroup, mycount, JiraGroupID, issuestatus, pname

of course, you'd need to remove the custom field stuff to get this to return any results, but this is the data we're looking for. But I am not sure how to achieve this using JQL and/or REST services...