JIRA Reporting - how to include "Execution Times" for a specific Transition in JIRA search?

greeppl

I would like to be able to track the number of iterations (re-works) for each issue logged in JIRA.

The iteration/re-work is the number of occurrences of the following Transitions:

from: RESOLVED to IN PROGRESS  (e.g. when Issue Reporter is not happy with the solution applied by Developer).

The below Transition table in the attached screenshots & especially the "Execution Times" field does provide the necessary information at a Issue Level.

However, what I would like is to include the "Execution Times" field for reporting purposes, in JIRA search/filter & have it available for each issue in a project.

For some reason however, the "Execution Times" field is not an available field in the Searches/Filters, so I cannot not add it from there (it seems to be available at Issue level).

I understand the whole Activity data is stored in a separate database table in JIRA & therefore it is not directly available for reporting.

However, there must a way to integrate this information in order to achieve the results I expect.

Would anyone know how to achieve the above or what any other workaround/solution can be applied to achieve the same?

Thanks in advance

 

 

 

Mesut Yilmazyildirim

It is not easy with JQL commands without writing new JQL function.

But you can query from DB via below SQL

SELECT p.pkey || '-' || i.issuenum AS jira_id,
 res_sum.Resolved_count Resolved_count
 FROM jirasd.jiraissue i,
 jirasd.project p,
 ( SELECT bl.issueid,
 SUM (CASE WHEN new_status LIKE 'Reopened' THEN 1 ELSE 0 END)
 Resolved_count
 FROM (SELECT cg.issueid,
 TO_CHAR (ci.newstring) new_status,
 TO_CHAR (ci.oldstring) old_status
 FROM jirasd.changeitem ci, jirasd.changegroup cg
 WHERE ci.field = 'status' AND ci.groupid = cg.id) bl
 WHERE bl.new_status IN ('Reopened')
 AND bl.old_status IN ('Resolved')
 GROUP BY bl.issueid) res_sum
 WHERE p.id = i.project AND res_sum.issueid = i.id