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