How can I create a report that shows how many people are assigned to each Component?

[Brian Barnes]

I'm looking for an answer on if I can generate more numbers based / analytical reports.  I've only been able to figure out how to basically display issues in different ways, based on filters.

What I want to do, for example is have a graph that shows how many people on my team are assigned tasks that are tagged with each Component value.

For example:

Component 1 - 3 people

Component 2 - 6 people

Component 3 - 1 people

I'm open to displaying this in different ways, but some kind of graph would be nice.

[Natalie Paramonova _Stiltsoft_]

Hi Brian,

If you also use reporting in Confluence Server, you can work with the Jira Issues macro in combination with the Table Filter and Charts app.

Using the Table Transformer macro, you can count the number of people per project:

Use the following SQL query:

SELECT 'Component', COUNT (DISTINCT 'Assignee') AS 'Number of People' FROM T* GROUP BY 'Component'

[Brian Barnes]

That looks neat.  Thanks!  I guess there would also be a way to make a chart out of that data as well?  Also, is there a way to display the data in a JIRA dashboard, or would it only work in Confluence? 

[Natalie Paramonova _Stiltsoft_]

Yes, sure you can make charts out of that data and actually use the Jira Issues macro to create almost any reports and charts in Confluence.

To display Confluence pages in Jira you can use Confluence Page Gadget (here you can find information about its adding).

[Antoine Berry]

Hi,

I do not know about plug-ins that would do this, but since you are using jira-server, maybe you could consider a SQL request ?

Antoine

[Brian Barnes]

I do have some experience with SQL, so yeah that would be great!  I don't have any experience with running an SQL query in JIRA though, so anything you can do to point me in the right direction there would be awesome.  Thanks.

[Antoine Berry]

Hi @Brian Barnes ,

If you are using MySQL you could use something like : 

SELECT 
component.cname as 'Component name',
COUNT(DISTINCT cwd_user.ID) 'Number of people'
FROM jiraissue 
JOIN project ON project.ID = jiraissue.PROJECT
JOIN nodeassociation ON jiraissue.id = nodeassociation.source_node_id
JOIN component ON nodeassociation.sink_node_id = component.id
JOIN cwd_user ON jiraissue.ASSIGNEE = cwd_user.user_name
WHERE project.pkey = 'Your project key'
GROUP BY (component.ID)

If you want to go further you can use a reporting tool such as SSRS.

Antoine