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 Sign up to answer
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,313 views 12 19
Read article

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