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

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!



2 answers

1 accepted

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


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.


I have another suggestion :-) :

1. Instal Scriptrunner plugin - if you don't have it installed already(

2. Prepare Month Script field with scriptrunner ( 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.

// 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 =
if (cdate>15){
	        	cmonth15 = cmonth15 + 1
cinfo = cyear.toString() + " " + cmonth15.toString()
return cinfo


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!



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?


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

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

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 ...

3,253 views 14 20
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