Roll-up reporting for Initiatives

Rob Falla November 22, 2017

Hi all, I am trying to create a jql query that I can use in Confluence to report on progress with an initiative.

Has anyone else tried this sort of thing? If so, did you have any luck with it?

Outcome required:

List stories that belong to all epics that are linked to an initiative.

Objective:

Want to enable roll-up reporting. For that to happen we need to see a list of the Jira Child stories for all Epics that are linked to an Initiative story.

Background:

The (INI) initiative is a kind of story that allows analysis, review, validation, sizing, etc, of new initiatives (projects). As early refinement on the initiative progresses Epics begin to emerge that capture the requirements as features or capabilities. These Epics and their stories form the basis for sprint backlogs that will deliver the implementation of the initiative.

Following are some queries I ran and my edited results.

+++++++++++++++++++++++++++++++++++++++++++++++++++++
Query: issuetype = Epic AND issue in linkedIssues(INI-97)
Returns: "success" - A list of epics that are 'linked' to the identified issue
Result:
--------------------------------
Sprint Assignee T Key Sub-Tasks Summary Story Points Status Resolution Epic Link Days since last comment Last Documented Status
Unassigned Epic TEST-1645 Backoffice Implementation OPEN Unresolved 15 weeks ago
Unassigned Epic DEMO-681 Gateway Implementation OPEN Unresolved 15 weeks, 6 days ago
--------------------------------

+++++++++++++++++++++++++++++++++++++++++++++++++++++
Query: "Epic Link" = TEST-1645
Returns: "success" - A list of stories created to implement the requirements captured by the Epic.
Result:
--------------------------------
Sprint Assignee T Key Summary Story Points Status Resolution Epic Link
Team1-S17-0920 Ash Story TEST-1676 This is another test 1 DONE Done Compliance: Stored Cards
Team1-S17-1004 Louise Story TEST-1677 SPIKE: This is a test too? 1 DONE Done Compliance: Stored Cards
Team1-S17-1115 James Story TEST-1659 SPIKE: More test stories 1 UNDER INVESTIGATION Unresolved Compliance: Stored Cards
Team1-S17-1115 Andrew Story TEST-1687 This is a test story 3 IN PROGRESS Unresolved Compliance: Stored Cards
--------------------------------

I appreciate any thoughts any of you may have to contribute to this issue.

3 answers

3 votes
Mahidhar kodam July 25, 2020

Can you instead create a Jira portfolio plan , Use a filter query which pulls that initiative, epics , stories and subtasks within the Initiative. You would get a nicd treeview with dropdown and progress bar , fix version, status and so on for view/reporting. 

This we have implemented in our projects.

Ali Hunt November 5, 2020

This issue I am experiencing with trying this, is that I end up having to type out 20+ Epic ticket numbers in order to get this report.  This is not feasible (super manual). 

and also doing it this way does NOT capture and newly added EPICs, unless we remember to go edit the filter to manually add the additional EPIC number.

want to be able to simply use the INitiative Jira number in a filter so that it will list all the Stories and EPics.

This way I can export the Stories to CSV and total up all the STORY Points to begin predicting the available Velocity for our specific initiatives, to build a roadmap.  Right now this is super manual, not scalable.

.....because the Advanced Roadmap / Portfolio / Plan feature does not TOTAL Story POINTS specific to INitiatives..     Nor am I able to create a dashboard for an Initiative that will total Story Points... somehow the filter will NOT recognize the Initiative Jira ticket key.       

 

Please let me know if there is a way to use Jira to total all Story Points related to an Initiative, or even related to several specific Initiatives. 

THIS WOULD BE AWESOME TO BE ABLE TO DO - :

TOTAL STORY POINTS RELATED TO AN INITIATIVE  would give us the capability to predict/estimate how many sprints it will take our team to complete the initiative(s)

Rahul_RVS_Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
November 6, 2020

Hi @Ali Hunt 

If you are using Jira server, you might to try out our addon

Agile Tools - Epic Tree and Time in Status 

It will provide you the below features

  • Sum up your Epic's Time Spent, Original Estimate, Time Remaining, and Percent Complete
  • Display and manage Epics/Issues of your project
  • Edit Issue summary, time estimates, story points and assignee on the tree with real time updates in the progress
  • Rolled up percentage completion and at issue level as well
  • JQL based Time in Status Report

Epic Tree Screen_Hierarchy_New.PNG

 

Thanks

Ali Hunt November 6, 2020

Really attempting to continue to keep the tools super simple so that leadership can quickly glance and get the clue.

and I am perplexed at  the use of "Time" (i know some teams use this), when the framework we wish to stick with is Story Point velocity. 

Can we keep this simple?    Just STory Point totals per Sprint...   this way we can see how many story points and Epic will eat up in a sprint (for future planning) , and be able to predict the room  (story points) the team will have leftover for operational work and maintenance, or for unexpected curveballs (we like to have a buffer left for these) that we know is the nature of our work?

Thank you for the prompt response!

1 vote
Aaron Mosny January 31, 2019

Did you ever get anywhere with this?

Rob Falla September 12, 2019

No

I tried a few things with Perl too but just didn’t have the time to work this through. I’d be happy to work with anyone else on the problem. If anyone reading this wants to try to crack this nut please reply so we can discuss 

0 votes
Patrick June 7, 2022

Old question but I just stumbled upon it. I would create a few automation rules which automatically updates a custom field with the initiative key when the epic is linked to an initiative or an issue is linked to an epic

Then you can query for all the issues with that custom field initiative key.

The only caveat is you would have to edit the rule or create a new rule for all new initiatives. This shouldn't be too cumbersome since initiatives tend to only come out quarterly or even less often.

Suggest an answer

Log in or Sign up to answer