Counting sub-tasks and records updated in JIRA

[Cynthia Newlin]

Manager wants to count the number of sub-tasks within each JIRA within a report or JQL.  How is that best done?

There are issues recorded, example: DFX-123 is one task.  There are daily data fixes until the issue is resolved, so sub-tasks are recorded under DFX-123 as: DFX-124 where 23 records were updated, DFX-125 where 45 records were updated the following day, DFX-126 where 10 records were updated the day after, etc.

The same would follow for similar issues: DFX-456, DFX-567, etc.

What is the best way to generate a report where, by issue, you can see what is affecting the most records and prioritize by such?

[Boris Berenberg]

You can't do this out of the box. Maybe using a reporting add-on like https://marketplace.atlassian.com/plugins/com.eazybi.jira.plugins.eazybi-JIRA ? Or you could write your own custom SQL queries to pull this data from the DB.

[Cynthia Newlin]

Please provide an example of how to pull this using Custom SQL from the DB.

At a glance, I don't see how the reporting add-on helps in this case.

[Boris Berenberg]

You would need to pull issues from the jiraissue table. Then link children to parents using the jira_subtask_link link type from the issuelink table. Then combine this with the customfield and customfieldvalue tables to count the number of records being updated. The reporting add-on is very complex, and powerful, and may be able to do this kind of number crunching for you without writing SQL, though I have never used it and can't be sure.

[Cynthia Newlin]

Would Confluence possibly be able to do any of this? 

He wants what most higher management wants, a nice report to look at for decision-making.

I find the BI lacking (in general) for Agile especially.

[Boris Berenberg]

If you do it in SQL, you could then use the SQL macro in Confluence to present the data there. Like I said, if you want to extend your reporting and BI functionality in JIRA, I believe that EazyBI is your best bet.

[Deleted user]

@Cynthia Newlin. Hi I hope you don't mind me contacting you directly but did you manage to resolve this. I am in need of the same and would like a solution. Are you using JIRA server or cloud

Thanks