Hi I have a team that works both in sprints for certain project work and outside of sprints (no sprint assigned to the issue) for other work.
I’m trying to build a report that can show how many story points were created and resolved by each team member. The report I have already built has ‘Sprint’ as a page filter so I can look at the creation/resolution for a given sprint period. This works fine for all work that’s assigned a sprint but I am missing any non-sprint assigned work a team member may take on during the sprint period.
Is there a way to have sprint as a page filter but show in the measures a total of the story points created and resolved for issues both assigned to the filtered sprint AND created and resolved within the sprint start and end date? This would capture total work done by the team while allowing me to filter time periods by sprint start/ends.
Hi @Sharon_Mui,
Please see an example measure for Work resolved in sprint in this eazyBI demo report: Story points burn-down by sprints for epic - Issues - Jira Demo - eazyBI.
The formula goes through issues to find the resolution dates between Sprint start and end dates and then sums their story points. You can further adjust the formula to take only issues created and resolved within the Sprint period.
Lauma / support@eazybi.com
Is there a more efficient way to display work completed? I’ve used the above example and created this calculated measure called “Work Completed During Sprint”:
NonZero(SUM(
{
[Time].Currenthierarchy.Levels('Day').DateMember(
[Sprint].CurrentMember.get('Start date'))
:
[Time].Currenthierarchy.Levels('Day').DateMember(
TimeStampToDate(IIF(isEmpty([Sprint].CurrentMember.get('Complete date')),
DateToTimeStamp([Sprint].CurrentMember.get('End date')),
DateToTimeStamp([Sprint].CurrentMember.get('Complete date')))))
},
([Measures].[Story Points Completed],
[Sprint].DefaultMember)
))
[Story Points Completed] is another calculated measure shown below:
Sum(
-- filter all issues which were completed
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issue status] = "Complete"
),
-- sum up completed SP
[Measures].[Story Points resolved]
)
However when I use Work Completed During Sprint with:
- Sprint as pages
- Assignees as rows
- A custom field on the issue denoting whether an issue is one of three values: KTLO (Keeping the Lights On), Support, Innovation as the columns
I get an error that the wait time is too long for the report to load. Error is: Query execution timeout of 180 seconds reached.
Please try to make query simpler.
@Sharon_Mui,
The Story Points Completed measure uses the Descendants function and iterates over all issues to find the completed ones and then sum the resolved story points for them. While this is not incorrect, the Descendants function is time-consuming. Instead, we could write this simple query to show resolved story points for completed Status by using tuples instead of iterating over all issues and that should work a lot faster:
(
[Measures].[Story Points resolved],
[Status].[Complete]
)
If this still does not help, please, export the report definition and send it to support@eazybi.com mentioning this community post.
Lauma / support@eazybi.com
1 Like
Great thanks, this worked well!
1 Like
I have one more issue, not sure if I should just send this to support@eazybi but I was just spot checking the formulas that have been created from above.
In the sample report below: I have Niamh showing 18 points in KTLO that were transitioned to the status “Complete” during the sprint start and end dates as per the report. But when I click “drill through issue” , I only see 8 story points across 6 tickets.
(Drill through issue page when clicking KTLO for Niamh)
However, when I write a JQL query to get the same data within Jira, I do actually see 18 points completed (see screenshot below). Is there a way that the drill through options show the tickets that were related to each category? Should I just make another report with issues as rows for this?
Hi @Sharon_Mui,
During Drill Thorugh Issue, there is a performance setting to run only for issues currently associated with all report selections. Some issues might not be in the Sprint; this we ignore in the original calculation with the [Sprint].DefaultMember, but not during the Drill Through.
To force Drill Through to ignore the report context, you can add the following special comment on top of Work Completed During Sprint measure:
--annotations.drill_through_non_empty = false
...
Lauma / support@eazybi.com
