Jira time in status, lead time, cycle time, assignee time reporting in JXL sheets

[#1]

Hello community!

There is a wide range of apps with varying complexity and price points on the Atlassian Marketplace that can help track metrics like lead time, cycle time, SLA and OLA performance, assignee time, and other analyses. Now the wait is over, and these features are also available in JXL for Jira, the all-in-one issue editor and organizer.

In one of the recent releases of the app, the concept of history columns was introduced. In addition to smart columns, these incredibly versatile and customisable helpers can surface all sorts of hidden Jira data and transform it into actionable information and essential knowledge. If you use Jira for agile work and project management, dev ops, IT service management, manufacturing, product management, business processes, or anything else under the sun, your teams should be empowered to continuously learn from their past performance by answering questions such as:

• How long does it take for work to get picked up? Triaged? Reviewed? Completed? Shipped?

• How much time do specific assignees spend on their tasks and jobs?

• Which teams or team members could use some support?

• What are the bottlenecks they’re struggling with and how can they get resolved?

In this post, we’re giving a quick intro to using issue history data columns in your sheets. To get started, a quick recap of how JXL works: You create sheets, i.e. issue tables with a scope that can be freely defined with a filter or JQL statement. Table columns and layout can be effortlessly arranged to suit whatever is important in your use case. Applications are practically endless.

A JXL sheet is a highly customisable table view of the Jira data that matters most to you and your teams

By adding powerful history columns such as Time in status, Time between statuses, or Time with assignee to your sheets, you can extract historical issue-related data and compile metrics like Lead time or Cycle time in a breeze. It has never been easier and more convenient to put together reports about how long issues have spent in specific status/es or with particular assignee/s on average, in total, etc.

1. Create a sheet with the set of issues you need.

2. Add the column you need to the table.

3. Configure the column to define the measurement you need and give it a fitting title.

History columns offer various configuration options to ensure you get to measure exactly what you need. As an example, in a Time in status column you specify one or multiple statuses that you'd like to be triggers for the measurement. Then you specify which occurrence/s of triggers you'd like the measurement to entail, in case there are multiple occurrences, i.e. in case an issue spends more than one time in the defined status or statuses:

• Latest - Capture the last occurrence

• Earliest - Capture the first occurrence

• Shortest - Capture the shortest occurrence

• Longest - Capture the longest occurrence

• Average - Calculate the average length of all occurrences

• Median - Calculate the median length of all occurrences

• All - Capture and sum up all occurrences

• All including gaps - Capture and sum up all occurrences and include gaps between them

Configuring a "Time between statuses" history column

In a Time between statuses column, for example, you can additionally specify the exact trigger points when you'd like the measurement to begin:

• Transition to or creation in - When the issue is transitioned into or created in the specified status or statuses

• Transition to - When the issue is transitioned to into the specified status or statuses

• Creation in - When the issue is created in the specified status or statuses

• Transition from - When the issue is transitioned away from the specified status or statuses

And when it should end:

• Transition to - When the issue is transitioned into the specified status or statuses

• Transition from or now in - When the issue is transitioned away from or is currently in the specified status or statuses

• Transition from - When the issue is transitioned away from the specified status or statuses

• Now in - When the issue is currently in the specified status or statuses

History columns in a JXL sheet

All settings work with useful defaults of course, so you only have to think about them if you really need to. History columns are designed to be extremely powerful yet quick and easy to set up. No tedious report setups, no clumsy exports necessary, just add columns to your sheets. So far, the wide range of JXL history columns includes the following:

• Time in status – The amount of time the issue spent in specified status or statuses.

• Time between statuses – The amount of time the issue spent between specified statuses.

• Time with assignee – The amount of time the issue spent with specified assignee or assignees.

• Time in status with assignee – The amount of time the issue spent in specified status or statuses, with specified assignee or assignees.

• Time between statuses with assignee – The amount of time the issue spent between specified statuses, with specified assignee or assignees.

• Date of status – The time and date when the issue was in specified status or statuses.

• Time since status – The amount of time since the issue was in specified status or statuses.

• Date of status change – The time and date when the issue was transitioned to specified status or statuses.

• Time since status changed – The amount of time since the issue was transitioned to specified status or statuses.

• Number of statuses – The number of times the issue was in specified status or statuses.

• Number of status changes – The number of times the issue was transitioned to specified status or statuses.

• and many more

We are adding more history columns on an ongoing basis and welcome any suggestions. Is there any particular time and date related data in the lifecycle of an issue that you’d like to unearth? Let us know

What’s JXL again? A Jira Cloud, Data Center and Server app that combines the power of Jira with the simplicity of spreadsheets. Features include creating and inline editing issues in highly customizable tables, copying and pasting fields individually or in bulk, grouping and structuring issues in custom hierarchies, summing up fields across levels, ranking issues, applying conditional formatting rules, and much more. It’s the all-in-one issue editor and organiser that saves you countless clicks and hours. Easier faster Jira.

If you haven’t already, take it for a free test drive for a month. It’s also free for up to 10 users. Chances are you’ll never want to go back to vanilla Jira. Secure your free trial here on the Atlassian Marketplace.

I hope this was a useful tutorial. If anyone has any questions, please get in touch or comment. We’d love to hear from you. Stay safe. ✌️

---

Disclaimer: The author is on the JXL team ;)