Is there a SIMPLE way to create a report that shows the number of tickets created per month for a specified time period?

Rob Horan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2015

Using relative time (last x days/months/millenia/etc) is not helpful.  I have been asked to generate a report in bar graph form (which I assume I will need to use a separate tool for once I have the numbers) of tickets created per month, by type.  I can't find any way to do this in Jira.

Before throwing Script Runner out there please understand I am not a programmer.

 


 

By the way, for any of you doing date based searches, you may want to look at this:

https://answers.atlassian.com/questions/30949194

6 answers

1 vote
Bryan McMillan August 28, 2015

Robert,

 

Yes there indeed is.  You can use the following JQL key words of:

  • currentLogin()
  • endOfDay()
  • endOfMonth()
  • endOfWeek()
  • endOfYear()
  • lastLogin()
  • now()
  • startOfDay()
  • startOfMonth()
  • startOfWeek()
  • startOfYear()

In a fashion similar to this using JQL from within the Advanced (vice Basic) view under Issues --> Search for Issues:

Project = MyProject AND resolutiondate > startOfMonth() ORDER BY updated

 

Where MyProject is the name of one of your specific projects in Jira.  You can also leave that off if you wish to pull that same information from all projects you have access to in Jira.

 

Hope this helps,


Bryan

Rob Horan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2015

I'll give this a try, thanks!

Rob Horan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2015

The only problem is that only works on a month at a time. I need to span a year, maybe two.

Rob Horan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 12, 2015

I just noticed the start of year and end of year key words. Look, it takes me a while sometime. There just isn't enough coffee on some days.... All kidding aside, thank you :)

Like Natalie Rossing likes this
1 vote
Scott Dudley [Inactive]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2015

If your budget does not limit you to only free options, and if you have a behind-the-firewall installation, the Arsenale Dataplane add-on can easily tackle this task using the "Issues Created by Date" report.

In this case, you would be able to get both the bar graph and the numbers without using any external tools (and your data can also be displayed in a dashboard gadget and/or exported to PDF/Excel/CSV).

Rob Horan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2015

Wow. Thank you but WOW. Not free is one thing, but that's... a lot :)

Like Carlos Otiniano likes this
Scott Dudley [Inactive]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2015

I would agree that buying a paid add-on is probably not a cost-effective option if this is a one-time task and you don't need any additional reports from JIRA. (Then again, it comes with a 30-day free trial, so this may in fact be both the fastest and cheapest option if you only need to do it once!) On the other hand, if your boss wants you to produce this type of data every week, automation could prove itself valuable in the long term. :-)

Mohammad_zulfikar April 16, 2020

Hi scott, thank you for your info, it's very interesting to know about Arsenale Dataplaner because I wanted to make a report based on an issue type on JIRA but it's confusing how to do that. And what have you said it's a good idea, I've tried to dig it but have some difficulty to apply on my JIRA, how do I add the arsenale dataplaner into my JIRA? 

0 votes
Midori
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 13, 2015

For non-programmers (and programmers, too), the Better Excel Plugin can generate this in a form of pivot table and the corresponding pivot chart

The created-vs-resolved-reported.xlsx template creates something that you can easily customize, grouping creations/resolutions by month and allowing grouping those by issue type, assignee, creator, project or whatever dimension you need:

created-over-month.png

0 votes
Bryan McMillan August 28, 2015

This gets the current YTD

project = MyProject AND createdDate > startOfYear()

For all of 2014 YTD:

project = MyProject AND createdDate > 2014

 

Regards,

 

Bryan

0 votes
GabrielleJ
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2015

Where will that report be? If it's in excel, exporting the JIRA issues (in JQL) then creating the bar graph in excel per se will be the simplest way to go. This is what I am doing with my other projects.

  • JQL: Project = ABC and created >= 2015-8-1 and created <= 2015-8-31
  • Export Issues into Excel (Current Fields) and leave the fields that you need
  • Create the Bar graph 

 

Rob Horan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2015

I assume I will use Excel. To be honest I'm figuring it out as I go along :)

GabrielleJ
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2015

Project = ABC and created >= 2015-1-1 and created <= 2015-12-31

Rob Horan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 12, 2015

Want to know something tragic? That JQL will not work. It will omit all tickets on 12/31/2015? Why? Because, as Atlassian pointed out to me "dates in JQL are calculated as data/time values and, when you don't inform a time value, JIRA considers time as 00:00." How did this come about? I submitted the following: So, lets say you want to find all the tickets created in september. You go to the calendar and you enter dates between sept 1 and sept 30th, right? Or use the JQL created >= 2015-09-01 AND created <= 2015-09-30 Surprise! Issues created on the 30th do NOT appear in the search results. This is a SERIOUS problem for me, as I need to run monthly reports, and since Jira offers no other month based search options I rely on this. I got back: This happens because dates in JQL are calculated as data/time values and, when you don't inform a time value, JIRA considers time as 00:00 So you can either write your query as {{created >= 2015-09-01 AND created <= 2015-10-01}} or {{created >= 2015-09-01 AND created <= "2015-09-30 23:59"}} to get also the issues created in 30th. Notice that you can also write a query like {{createdDate >= startOfMonth() AND createdDate <= endOfMonth()}} and save as a filter to give easy access to all the issues created in the current month. Another query that could be useful in your case to safe as a filter would be {{createdDate >= startOfMonth(-1) AND createdDate <= endOfMonth(-1)}} which shows all issues created in the last month.

Rob Horan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 12, 2015
0 votes
Bryan McMillan August 28, 2015

Robert,

 

My bad.  If you want to do created during the month then this will do:

project = MyProject AND createdDate > startOfMonth()

 

Regards,

 

Bryan

Suggest an answer

Log in or Sign up to answer