We have built Time in Status plugin for this exact purpose. Time in Status prepares a report that shows how much time each issue spent on each status or on each assignee.

Better Excel Plugin exports any sort of JIRA data, including status changes and workflow transitions, for custom Excel reporting.

For this use case, you should export the issue workflow transitions with:

• their exact date and time
• start and end status
• duration spent in the start status
• the user who took that transition
• etc.

Having that list of denormalized data in a worksheet, you can apply Excel's standard features to aggregate it to pivot tables and pivot charts or whatever reports you need.
Also, you can define a calculated column with Excel functions for SLA: "if issue type is ticket and time in open status is longer than 4 hours, then 0, otherwise 1".
Please note that all these will be part of your Excel template, which will be merged again and again with the JIRA data at the export time. So, you don't need to repeatedly re-create the pivot charts, but set them up once and fill them with the most current workflow data any time with a single click.

In fact, the plugin comes with a default "workflow / SLA analysis" template that takes this exact approach. It should be straight-forward to customize that to your needs after reading the customization guide.

JIRA Service Desk has a concept of SLAs that allow tracking issue lifecycle time exactly for the scenario mentioned in the issue. In addition, there are other plugins referenced above that can help with charting time taken on issue transitions. Closing this issue now.

Hi,

As native JIRA reporting is a kind of restricted, Valiantys Corporation proposes the PowerReport plugin for JIRA which allows you to make powerfull reporting on all JIRA data !

PowerReport uses the open source Eclipse-based reporting system BIRT to generate compelling and custom reports !

You can have a look at this plugin here => https://marketplace.atlassian.com/plugins/com.valiantys.jira.plugins.reporting

Hoping you would be interested in, I wish you a good day !

Brice

You should have look at the new JQL features of JIRA 5.0 (http://confluence.atlassian.com/display/JIRA/Advanced+Searching). The new JIRA version allows to execute queries like status changed AFTER startOfDay() and similar ones.

Additionally you should keep in mind JQL is a issue query language. Unlike SQL it is thought to find issues and not to select single fields of an issue.

Has any more thought been given on this feature? Not much progress has happened since '07.

We needed to extract this data urgently, in case others find it useful, here is a half baked, quick and dirty SQL query that does the job. You need to know the project id num which should be easy to find out and add to the query.

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_findStatusDate` $$
CREATE DEFINER=`yourJiraDBname`@`%` PROCEDURE `sp_findStatusDate`(
 IN p_project_num VARCHAR (45)
)
BEGIN

  SELECT
    a.`pkey`,
    a.`issuestatus`,
    b.`STEP_ID`,
    b.`START_DATE`,
    b.`FINISH_DATE`,
    b.`ENTRY_ID`,
    b.`CALLER`
  FROM os_historystep b
    INNER JOIN jiraissue a on a.`WORKFLOW_ID` = b.`ENTRY_ID`
  WHERE (a.`PROJECT` = p_project_num)
  ORDER BY a.`pkey`;

END $$

DELIMITER ;

The HP/Palm Jira Search Plugin has an option that will search the entire issue change history, including who changed the field and when it was changed.

+1 for this as well. I have 32 status along our workflow and have no idea the time between statuses. Help

+1 for this. Please consider for incorporation into work on the change history JQL functionality for Jira 4.4

I agree with Ian. This feature is most needed. For example, I would like to know how many tasks of a particular type (represented by "status") is complete every day, and over the last 7 days. There is no way to find it in JIRA. It is unfortunate, the data exists within JIRA but cannot be even exported to a spread sheet. This issue is not even assigned to any one, meaning, we cannot expect the fix in the near term.

Is there no way around at all? Can some one help please?

Thanks,
Prashanth

I have voted for this issue. Without this functionality, reporting in JIRA is not very effective.

We also have the requirement to report on status changes

The detail for the report is as follows:
For a selected date range, list the number of issues for each status change grouped by active JIRA user.

An example would be. 3 JIRA issues moved by 3 different JIRA users from Open to In Progress on Day 1. Issue 1 and 2 moved to Status QA on Day 2 and Closed on Day 3. Issue 3 moved into In Progress on Day 3 and Closed on day 4. User 1 Opened issues, User 2 Moved to In Progress and QA and User 3 Close Issues as detailed below.

Thus For Issue 1
Open, Day 1, JIRA User 1
In Progress, Day 1, JIRA User 2
QA, Day 2, JIRA User 2
Closed Day 3 JIRA User 3

Issue 2
Open, Day 1, JIRA User 1
In Progress, Day 1, JIRA User 2
QA, Day 2, JIRA User 2
Closed Day 3 JIRA User 3

Issue 3
Open, Day 1, JIRA User 1
In Progress, Day 3, JIRA User 2
QA, Day 3, JIRA User 2
Closed Day 4 JIRA User 3

Report run for Date Range 1 to 4

JIRA User ___ Status _ Number of issues
1 __________ Open __________ 3
2 __________ In Progress ____ 3
2 __________ QA ___________ 3
3 __________ Closed ________ 3

Report run for Date Range 1 to 2

JIRA User____ Status___Number of issues
1 __________ Open ________ 3
2 __________ In Progress ___ 2
2 __________ QA __________ 2

I came across couple of projects where this has been a key requirement. So definitely would love to see this feature available soon to the product.

I agree something like this is needed. My QA manager wants to know the time it takes issues to go from resolved-fixed to passed qa. I really want to keep random people out of the database level even at read-only level. The previous tool we used allowed this and it should be part of basic reporting tools without requiring java coding.
This has my vote.

It seems to me that these two could be bundled into one.
We desperately need something alike.

Not even being able to report on the Resolved date is really bad.
Reporting on the date of last Update for Resolved issues is NOT a solution when it is perfectly normal to update Resolved issues.

As this information already is in the system this is a shame that we cannot use it for reporting. That cannot be too difficult.

In our company we need to report on the time in between two statuses, list how many tickets we have had in a particular status for X days...

All this is required when you have decent Service Level Agreements with your customers so it makes perfect sense to get this capability out of the Jira standard.

Waiting for this, I'd be happy to find a suitable plugin if someone knows about it.

We are running into similar problems with JIRA at out site 300-700 engineers.

I was asked if it was possible to get the "Status Change time" or the "Last Execution Date" from the "Transition Tab on a bug, put into the configuration drop down box. This would allow the user to be able to add these fields to his query and then export them to and excel spread sheet to allow them to figure out how long a bug was in a transition state i.e. how long is it in reworks etc.

It seems that Jira is broken when it comes to trying to get any information from history, transition etc. with out having to resort to backend database manipulation to get it.

I have 160 plus engineering projects in JIRA and cannot open the database on the back end for everyone to access to get data.

Could you please revisit this and maybe push it up on the priority list.

Question: will any of these queries we are taking about be fixed with the new search engine etc in 4.0?

Thanks.

Hi,

Is there any plan to implement something like this?

Thx

Mel

This feature would be really important for us as well. We use JIRA as a Support System and need to see which Status change takes longer than it should be.

Mel,

Due to the number of open feature requests I think it will be a while until we implement this feature.

However, I believe this table already exists and is called OS_HISTORYSTEP. The ENTRY_ID column is the workflow_id of the issue. STEP_ID corresponds with a linked JIRA status. For more information on linked statuses please see:
http://www.atlassian.com/software/jira/docs/latest/workflow.html#workflow_steps

The table also stores for when the issue entered that step (status) and left it.

Cheers,
Anton

Hi Anton,

It is not what I am looking for. I would like to be able to have a table like this:

JRA257 incident in progress 19/03/2007 12:15
JRA257 incident resolved 19/03/2007 17:30
JRA257 incident closed 20/03/2007 8:50

The charts only show the average time in one status and what is crucial for us is something showing the time taken between one status to another like you see in the change history.

regards

Mel

Mel,

I think the functionality is available in the JIRA Charting Plugin which can be downloaded from our JIRA Extensions space:
http://confluence.atlassian.com/display/JIRAEXT/JIRA+Charting+Plugin

See the 'Average Time in Status' and 'Average Number of Times in Status' reports in particular.

Please let me know if this helps.

Cheers,
Anton