Number of issue types on certain dates in the past - how to?

[Rumceisz]

Hi All,

I have a weird question: some project leads approached me and they need a report which shows the number of several issue types on certain dates in the past. Specifically they need info of particular projects from the last 12 months and these "snapshots" would show 15th day of each month: X issue types number of 200, y issue types number of 150, etc.

Is it possible to make this report somehow? Theoretically the JIRA stores every single information to make these reports. But how to get?

Thanks in advance!

Regards,

Rumi

[Vidic Florjan · Accepted solution]

The easiest way (prepared in 5 minutes by normal user) is to prepare filter and use this data to prepare report in excel (or Microsoft Access).

Procedure:

1. Prepere JQL ( they need info of particular projects from the last 12 months )
For example: project in (JIRA, CCONF, PROJX, PROJY) AND created >= -365d

2. Save JQL as a filter and add some fields to filter (button for adding Columns on the upper right side of the filter)
You need to have Created, Issue Type, Project, Status Columns in filter.

3. Export filter data to excel (select Export on the upper right side of the filter, and select Excel (Current fields)) and open excel file with prepared data.

4. Add new Column (e.g. Month) after Create column to calculate month ("snapshots" would show 15th day of each month).

Calculate month with formula like this: =YEAR(F5) & " " & IF(DAY(F5)>15;MONTH(F5)+1;MONTH(F5))
In this example is Create field in column F.

5. Prepare Pivot Table
- Click on Insert, Pivot Table.
- In create Pivot table select Table/Range of the exported data from jira, click on Ok.
- Add Issue Type field in Row Labels area, Sum of Key to Values and Month to column labels.
That's all :-)

6. Present data in Confluence
If you are using Confluence you can also display excel on for example "Reports for managers" page.
(and you can update data from filter each month).

---------------------------------------------------------------------

Another aproach is to use aplications to design and generate reports from a wide range of data sources, for example Crystal Reports:
- In that case you need access to jira database.
- You can get data from jiraissue table (see Mehmet Kazgan's answer).
- Approach to prepare report is "similar" as for Pivot Table (add data from jiraissue table, add Month field,...)
- You can also schedule preparation of reports ("snapshots"). Crystal Reports can send reports to selected managers by mail on 15th day of each month.

Regards,
Florjan

[Vidic Florjan]

I have another suggestion :-) :

1. Instal Scriptrunner plugin - if you don't have it installed already(https://marketplace.atlassian.com/plugins/com.onresolve.jira.groovy.groovyrunner)

2. Prepare Month Script field with scriptrunner (
https://jamieechlin.atlassian.net/wiki/display/GRV/Scripted+Fields) for in my answer mentioned Month field

3. Create Filter as mentioned in my answer. Add Month script field to filter.

4. Create Two dimensional table in dashboard as report

(use Month script field for rows and Issue Type field for Columns)

5. Give managers access to filter and dashboard.

[Vidic Florjan]

// Groovy code for Script field
import com.atlassian.jira.issue.Issue

myIssue = issue
Date created = issue.getCreated()
Integer cyear = created.year + 1900
Integer cmonth = created.month + 1
Integer cmonth15 = created.month + 1
Integer cdate = created.date
if (cdate>15){
	        	cmonth15 = cmonth15 + 1
                }
cinfo = cyear.toString() + " " + cmonth15.toString()
return cinfo

[Rumceisz]

Hi,

thanks for the script!

I created a script field and applied the script but it only gets the year: 2,012 or 2,013

It should display the month as well?

Thanks again for your help!

Rumio

[Vidic Florjan]

Hi,

We have the same script and it works as expected. Is the code exactly the same?

Variable cmonth has a value of the month. Variable cmonth15 has a value of the month raised by 1 if the date number of the month is larger than 15.

Do you have selected "Text field (multi-line)" Template at the end of script file?

Regards,
Florjan

[Mehmet Kazgan]

Hi Rumi,

You cannot do that in JQL search but could do that by querying directly to JIRA DB:

1. Go to Admin>Issue Types> Scroll over at "edit" for the issue type you need the report for and get the ID (For bug id = 1)

2. Go to Admin>Projects> Scroll over at "edit" for the project you need the report for and get the ID

3. Your query would look like this:

select count(jiraissue.ID) from jiraissue
where issuetype = '1' and CREATED > 2014-01-01

You can use this for any issue types and you can change the query for in between days like:

select count(jiraissue.ID) from jiraissue
where issuetype = '1' and CREATED > 2014-01-01 and CREATED < 2014-05-01

[Mehmet Kazgan]

This is for SQL DB, query strings might change for different DB's