Get members from user groups

Hello!

I’m looking to get a list all JIRA groups that start with the term “TT” and beside it the number of people in that group. Something like:

Group # users
TT - ABS 25
TT - DO DU 1
TT - ODQ u6 36

Is this at all possible? If so, how would this be done?

Thanks!

Hi, Jacques

The eazyBI JIRA Issues cube has the predefined dimensions for the user groups (Assignee, Reporter etc.) but the data in those dimensions depends on the user activities performed with the JIRA issues and may not give the full information about the users in each JIRA user group. The solution to analyse the number of users per group would be by importing data from the JIRA database in a new cube.

You can define a new data source as the SQL select and import the information in a separate cube. The SQL statement could be like this:

SELECT m.parent_name group_name, u.user_name, u.display_name, 1 AS user_count
FROM cwd_membership m JOIN cwd_user u ON u.id = m.child_id
WHERE m.membership_type = 'GROUP_USER'

Then you can define the data mapping that would allow analysing user count per group:

To filter off the user groups by some name you can use the Filter by name option which appears when you click on the dimension member.

Thanks Janis! This is great!! The only issue is that I need this information to be available in the JIRA Issues cube and not a separate Cube as I need to correlate the information with the JIRA projects and such. If I remember correctly there is no way to create a new dimension in the JIRA issues cube. Is that still the case? If so, how would you suggest to import this information into the JIRA issues cube?

Thanks in advance!
Jacques.

1 Like

hi Janis,
I was wondering if you had any feedback on this?
Thanks!
Jacques

Jaques,

Sorry for the period of the silence.

Right, it is not possible to create additional dimensions in the issues cube. That limits the possibility to make reports with the full list of the user groups from JIRA in the issues cube.

However, a step towards this requirement could be creating the count of the users by the user groups using the dimensions Reporter Group, Assignee Group, Logged By Group, Transition Author Group.

For instance, the following measure would count the users in assignee groups:

Count (
 Filter([Assignee].[User].members,
 [Measures].[Issues created]>0
 )
)

You can create a report with the Assignee group dimension in the rows and the measure in columns showing the number of assignees in each group.

Please, note that in this case the user group list and the count of users will be limited to the users being assignees of some issues.

Hi Janis,
No problems about the silence… We all have busy lives :wink:

The suggested solution regretfully won’t achieve what I’m looking for. We have different departments in our company using a single JIRA instance and we would like to invoice them for the amount of licences they consume and as such each user is added to a TT group (as mentioned in the description of this question).

So I’m guessing this won’t be possible. That’s a shame.

Thanks anyway!
Jacques.