I need to search for open tickets during a given month.

[Elvis]

I need to search for the number of open tickets by ticket type during a given month/year which I know I can use the created <= function for that part. However since we allow tickets to be reopened I cannot just use the resolved date as that date can be in the past and the ticket still be open/reopened. When the ticket is reopened in our system the resolved date does not empty back out. I can use the status of the ticket but unsure on how to capture the date of the ticket status.

Thanks in advance

[Bill Sheboy · Accepted solution]

Hi @Elvis -- Welcome to the Atlassian Community!

For the scenario you describe, you want a re-opened issue to accurately report its status condition at a point in time, regardless of its current / changed status over time.

I do not believe that is possible with the built-in reporting or JQL features.

The information to do such reporting is stored in the issue history, and so you could:

• investigate marketplace apps which can perform this type of reporting
• using the REST API, export all of the issue history data into an external database which would support the reporting needed

Kind regards,
Bill

[Petru Simion _Simitech Ltd__ · Accepted solution]

Hi @Elvis ,

Deppending on what your company considers a re-opened ticket, it would mean transitioning the ticket back into a specific status. Probably the first status in the workflow regardless of how it is named: To Do, Open etc.

You could search each issue history and try to see if the ticket was transitioned back into such a status. Such an operation would be time consuming and error prone.

If you are open to using apps you can use Issue History Dashboard for Jira , an app released by our company.

As per the attached image, you can filter to find only history records about status being changed. You do this by using "Field Updated" with value "Status". 

You also filter for "Value To" to be "To Do", meaning the issue was transitioned back into To Do.

The attached img. shows only one such result. However you will be able to retrieve any number paginated. This issue was moved back to from status Done into status To Do.

You can view the date of change  and the user that performed the operation (Updated by).

If you want to restrict your search for a month, you can use the fields Date of Change, which are working in a from to manner, to do that. 

You actually can customize your search by various other fields.

You can export your search result to csv for storage and further analysis.

[Hannes Obweger - JXL for Jira]

Hi @Elvis

welcome to the community!

If you're open to solutions from the Atlassian Marketplace, I believe that your use case would be easy to solve using the app that my team and I are working on: JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called history columns that aren't natively available, including the date/time of [first/last] transition to [status(es)]. This column can be configured to consider both transitions to a certain status, as well as creation in a certain status.  

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting. Of course, you can also export your data to Excel or CSV in just two clicks.

Any questions just let me know,

Best,

Hannes