Report of the open issues - how to?

[Rumceisz]

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?

Thanks,

Rumi

[Pablo Beltran]

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:

SELECT *
FROM ISSUES
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.

[Markus Pöhler]

This looks like a combination of a database query and a JQL query. Please clarify how this works. The db dows neigher know a coplumn called JQL nor a table called ISSUES.

how does this work?

Thanks.

[Vidic Florjan]

Hi,

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 = d1.date

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

Regards,
Florjan

[Vishnukumar Vasudevan]

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.

[Rumceisz]

Hi,

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.

[Norman Abramovitz]

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

[Norman Abramovitz]

You can write JQL using

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

or

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()))

[Florin Manaila]

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

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.

[Florin Manaila]

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