Create table in JIRA dashboard of overdue vs ontime issues in Current Sprint

[Anushree Athawale]

Hi, 

I want to create a single table in a dashboard where I can get:
Total issues in each sprint 

Overdue issues in each sprint.

On time issues in each Sprint.

Please suggest possible solutions.

[Aron Gombas _Midori_]

There is the gadget that can calculate the number of issues per status, number of issues per priority, etc. and it allows you to select a saved filter as input. It also allows defining a "custom count":

You can probably write JQL queries that collect the "overdue" and "on time" issues.

[Mark Segall]

Hi @Anushree Athawale and welcome to the community!

The simple answer is that you'll likely need to leverage automation and custom fields to get the desired results. 

The foundation of all dashboards is in JQL.  JQL is rooted in searching against issues so getting specifics on sprint history has always been a challenge.  Because of this, even  marketplace apps providing JQL extensions struggle to deliver dynamic, historical listing of issues by sprint.  You may be able to hard code the sprint. However, even then you'll typically run into anomalies when issues have been carried forward from sprint to sprint as they'll count twice toward the results.

Can you please provide a little more context on this?

• Total issues in each sprint
  • Are you looking for a dynamic listing of previous sprints?
• Overdue Issues in each sprint
  • What is considered "overdue"?
    • Are issues being assigned due dates within a sprint?
    • Are you referring to issues that were not completed before end of sprint?
• Ontime issues in each sprint
  • Similar to above - What is considered "ontime"?

The only way to make something like this happen natively, is to have a mechanism for capturing the sprint metadata.  

1. Use automation to create a "sprint" issue for each sprint
2. Create two custom fields for Overdue and Ontime issues each sprint
3. Use automation again to populate the Overdue/Ontime issues as sprint issues are resolved

If you provide answers to my questions above, I can help clarify what the automation might look like.

[Anushree Athawale]

Hi @Mark Segall 

Thank you for the answer.

To answer your questions:
Overdue items: Items which have passed due date in current sprint.
On track items: Whose due date is yet to come in the current sprint.

I have already created a JQL for both the scenarios above, but the management needs a dashboard similar to below table:

Project	Sprint Name	overdue Issues	On track issues	Total issues in sprint.
Project 1	A	15	20	35
Project 2	B	20	30	50

Can this be achieved, if yes, how?
Thanks in advance.

[Mark Segall]

So reading through this, by "each sprint", you mean the current sprint across multiple projects? 

I would tackle it like this:

• Create a new issue type to capture the sprint metadata.  For my example, I'll simply call it "Sprint"
• Create custom numeric fields that will be added to the Sprint screen
  • Overdue Issues
  • Ontrack issues
  • Sprint Issues

From here, you'll need some automation:

1. Create Sprint issue - This will create a sprint issue when the sprint is started
2. Capture Overdue and ontrack issues - Performs the count of issues

Rule #1: Create Sprint Issue

NOTE - Since you're looking to accomplish this from multiple projects, you will want to ensure that the project scope for this rule is set to Multiple Projects.

• TRIGGER: Sprint Started
• ACTION: Lookup Issues
  Capture the count of issues in the sprint
  

  • sprint in openSprints()

• ACTION: Create Issue (Sprint)
  • Summary

    • {{sprint.name}}

  • Sprint Issues

    • {{lookupIssues.size}}

Rule #2: Capture Overdue and ontrack issues

To keep things simple, I would recommend recreating this on every project.

• TRIGGER: Scheduled (Your desired frequency)

  • sprint in openSprints() and type = Sprint

• ACTION: Lookup Issues
  This will return all on track issues
  

  • sprint IN openSprints() and due > {{now.jiraDate}}

• ACTION: Create Variable
  • varOnTrack
    {{lookupIssues.size}}
• ACTION: Lookup Issues
  This will return all past due issues

  • sprint IN openSprints() and due < {{now.jiraDate}}

• ACTION: Create Variable
  • varOverdue
    {{lookupIssues.size}}
• ACTION: Lookup Issues
  One last search to keep the total sprint count current

  • sprint IN openSprints()

• ACTION: Edit Issue
  • Sprint Issues
    • {{lookupIssues.size}}
  • Ontrack Issues
    • {{varOnTrack}}
  • Overdue Issues
    • {{varOverdue}}

This should get you set to where the sprint issue has captured all of the relevant issues.  From there, you would just use a Filter Results Gadget based upon the following JQL:

type = Sprint and sprint in openSprints()

Then select the following fields:

• Key
  NOTE - The key will provide the project delineation, but if you want actual project name, you'll want to create another custom field and populate that in rule #1
• Summary
• Overdue Issues
• Ontrack issues
• Sprint Issues

[Anushree Athawale]

Thanks @Mark Segall 

One more question arises.
This will create onetime issue with - Overdue Issues, On track issues etc but what if the count changes, it won't edit the issue then.

Thanks

[Mark Segall]

Rule #2 executes on a schedule so it will keep updating the sprint issue.