Report of the open issues - how to?

Hi All,

our project leads need a report which show a graph of the number of open and in progress issues. They want to proof to the customer that there are less and less open issues. Note, that it's not the created vs resolved report: actually they need a report which makes a snapshot of each day and shows how many Open and In Progress issues were on that moment.

Is there a way to get such a report? Maybe in SQL?



5 answers

Maybe a pie chart report grouped by status of the issues could help. The report looks like this:

Pie chart

You have both the visual representation and the numbers in the data table. If you don't want to show the resolved issues, you can use a filter as a base for the report and filter out the resolved issues form JQL.

Note: you won't be able to see the report for a past date

There is an issues statistics gadget for the dashboard that you can use. You can create custom JQL filters.

You can write JQL using

status was in (Open, "In Progress") on <date1> for a particular date


status was in (Open, "In Progress") during (<date1> , <data2>) for a date range

You could also use the changed operater as well

status changed to Open during (startOfMonth(), now()) OR status changed to "In Progress" during(startOfMonth(), now()))

I would achieve as follows:

1. Create a filter which gives only Open and 'In Progress' records.

2. Use 'Issue Statistics' gadget to get the statitics in percentage as well as in count.


the problem is that we need this info for past dates like snapshots of a given date in the past. Your suggestion is the current status.


You need to:
- "Natural Searchers for JIRA" plugin (it is free plugin)
- Scriptrunner plugin to create two Scripted fields (e.g. Open Date and In Progress Date)

It is important to set
•Template: Text Field (multi-line)
• Configured searcher: Exact Text Searcher (natural)
You have to reindex jira.

- One gadget in dashboard (e.g. Two dimensional filter statistics)
and related filter
You can use one dimension for prepared scripted field (e.g. Open Date and In Progress Date) and another for something else (e.g. project).
It is important to set
• Show Totals to Yes in gadget to Count information of each date issues

Code for scripted field (e.g. In Progress Date):

import com.atlassian.jira.ComponentManager
def componentManager = ComponentManager.getInstance()
def changeHistoryManager = componentManager.getChangeHistoryManager()
// changeHistoryManager.getChangeItemsForField(issue, "status").find {it.toString == "Closed"}?.getCreated()
Date d1 = changeHistoryManager.getChangeItemsForField(issue, "status").reverse().find{it.toString == "In Progress"}?.getCreated()
Integer cyear = d1.year + 1900
Integer cmonth = d1.month + 1
Integer cdate =

String cinfo = cyear.toString() + "-" + cmonth.toString() + "-" + cdate.toString()
return cinfo


I would like to notice the difference among fetching data and viewing data.

As Norman has mentioned, the right way to fetch such data would be via SQL.

Regarding the visualization, fortunately, there is an add-on on the Markeplace (SQL for JIRA) which brings you the freedom to choose the data visualization tool and use JQL at the same time:

Taking the 3rd query provided by Norman (which is the more complex), you might want to run:


WHERE JQL = 'status changed to Open during (startOfMonth(), now()) OR status changed to "In Progress" during(startOfMonth(), now()))'


and view the results on BIRT, Jasper Resport or whatever tool supporting JDBC.


Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,932 views 12 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot