Summarize table with Jira proyects

[tpedriel]

Hello. I'm looking for a tool in order to create this type of dashboard/table:


Asignee(Responsible for the task)                             Total of story points

Sprint                                                                          Juan       Carlos      Sonia

---

Sprint1                                                                          10             9                6

Sprint2                                                                           7             10               5                          

Sprint3                                                                           6              6                5   

To summarize, I would like to have a summary where I can visualize by sprint, the number of story points that each team member made.
I was able to create a dashboard with 2 axes (asignee vs sprint), but it only counts the tasks per users, and there is no possibility tu sum the story points.
Can anybody help me?

Thank you

[Joseph Chung Yin · Accepted solution]

@tpedriel -

Welcome to the community.  Unfortunately using out of the box functionality (gadgets for dashboards/reports) in Jira/JSM is not possible to generate the reports that you wanted.  I would recommend you to search via Atlassian Marketplace for third party add-ons that provide the functionality.

Examples - (NOTE- I have not used or evaluated any of those add-ons mentioned below)

https://marketplace.atlassian.com/apps/1219429/status-progress-report-gadget-for-jira?hosting=cloud&tab=overview

https://marketplace.atlassian.com/apps/1220136/quick-filters-for-jira-dashboards?hosting=cloud&tab=overview&source=community24

Hope this helps.

Best, Joseph Chung Yin

Jira/JSM Functional Lead, Global Infrastructure Applications Team

Viasat Inc.

[Esther Strom]

Welcome to the Community, @tpedriel !

I agree with Joseph; it's not natively possible (if you have access to the beta Analytics product it might be possible at some point, but as of now sprint data isn't part of the data lake.)

You can pretty easily create a view like that in eazyBI if you own it, but even easier is sumUp. It's a set of dashboard gadgets that do on-the-fly calculations of things exactly like this.

[tpedriel]

Thank you!! that was really helpful, the only thing that is remaining is the order of the chart. 

After sorting and filtering it, I can't show it chronologically in the graph. (In the example, the sprint Camelot 2.5 is the newest, I should visualize it in the right of the bar chart).

Is there any way I can sort the bar chart without removing the filter of top 5 rows?

Thanks!

[Nauris Malitis]

Hi @tpedriel 

In eazyBI you can use the predefined Sprint scope measures together with Sprint and Assignee dimensions to achieve a report like this.

We have a similar sample report in our Demo account:

https://eazybi.com/accounts/1000/cubes/Issues/reports/149997-story-points-resolved-by-assignee

You can modify this report by switching to the Table view, removing the Time dimension, and adding the Sprint dimension to the Rows section:

After that, explore the different Story Point measures you can select in the Measures dimension.

Let me know if you have any additional questions on this, or feel free to reach out to us at support@eazybi.com

P.S. @Esther Strom thanks for the shout-out!

Best regards,

Nauris / eazyBI support

[tpedriel]

Is this tool integrated with Jira?

[Nauris Malitis]

Hi,

eazyBI is available in the Atlassian Marketplace as an app for Jira Cloud and Jira Server/Data Center: https://marketplace.atlassian.com/apps/1211051/eazybi-reports-and-charts-for-jira?hosting=cloud&tab=overview

Out of the box, eazyBI offers built-in integration with core dimensions and measures of Jira: https://docs.eazybi.com/eazybi/data-import/data-from-jira/jira-core-measures-and-dimensions

I would recommend checking out the short videos on eazyBI to get to know it better: https://docs.eazybi.com/eazybi/learn-more/training-videos#Trainingvideos-Let'sgetstarted

Let me know if you have any additional questions on this!

Best regards,

Nauris

[tpedriel]

Ok thank you. I will try it.

Another question, do you know if we can send automated reports when we close a sprint? Instead of doing it by a standard measure of time (e.g. daily, weekly, monthly), we do it when a sprint is close. We need this because many times the sprints doesn't have a strict standard duration, sometimes it last 17 days (because we have holidays in the middle) or it can even last for 20 days.

Thanks

BR

[Nauris Malitis]

For now, there is only the option to subscribe to regular emails in your set time intervals: https://docs.eazybi.com/eazybi/publish-reports/email-subscription

However, we are developing a feature to send a report by email whenever a condition you set has been reached in a report. You would be able to use this feature for your intended purpose. I don't have an estimated timeline for when the feature will be available, but it is being developed.

Cheers!

Nauris

[tpedriel]

Thank you for your answer.

I'm having troubles with the filters. I want to show for example, last 6 completed sprints. Do you know if it is possible?

I tried by using filters: last months/weeks, but I'm leaving information out.

Also, I added a calculated measure (story points completed/committed) and I don't know how to exclude errors (infinity %).

Can you help me?

Find attached evidence

[Nauris Malitis]

In the Sprint dimension, you can switch to the "By status" hierarchy. Note that if you have the Sprint dimension in both Rows and Pages, be sure to switch to the "By status" hierarchy in both of them.

Then, you can set the Page filter to "Closed" to only show you the closed sprints and add the "Sprint actual end date" measure to your report. Add the "Top rows" -> "N rows" -> "5" to display the 5 most recently ended sprints.

You can remove the "Sprint actual end date" measure from the report, and the filter will remain active until removed.

About the calculated measure- the "Infinity%" occurs when a value is divided by an empty value. The easiest way to escape this would be to introduce a Case statement:

CASE WHEN
[Measures].[Sprint Story Points committed] > 0
THEN
[Measures].[Sprint Story Points completed]
/
[Measures].[Sprint Story Points committed]
END

For why the "Camelot 2.1" sprint has an empty committed value there could be two reasons:

1. You have set the Time filter to "Last 12 weeks". If the "Camelot 2.1" sprint started (and the issues thus were committed) 13 weeks ago, these SP will not show up in this field (as they respond to the Time set in the filter).
2. You may have started the Sprint without committed issues and added issues later on. In this case, the Issues will show up under the "Sprint Story Points added" measure.

You can read more on the Sprint scope measures in our documentation: https://docs.eazybi.com/eazybi/data-import/data-from-jira/jira-software-custom-fields#JiraSoftwarecustomfields-StoryPointstotalmeasures

Best regards,

Nauris

[tpedriel]

Thank you!! that was really helpful, the only thing that is remaining is the order of the chart. 

After sorting and filtering it, I can't show it chronologically in the graph. (In the example, the sprint Camelot 2.5 is the newest, I should visualize it in the right of the bar chart). 

Is there any way I can sort the bar chart without removing the filter of top 5 rows?

And other question. Can I count people involved in every sprint? I need to count all of assignees in each sprint. I've been looking in the page you sent me and I can't find the information. 

Thanks!

[Nauris Malitis]

Hi!

The Top row filter will not allow rearranging the order, but no worries, there is also a different approach to getting the last 5 Sprints.

A more versatile way would be to define a new calculated member "Last 5 closed sprints" in the Sprint dimension. If you would like to see the last 5 closed sprints from all boards, please use this formula for the member:

Aggregate(
Generate(
-- get all boards
[Sprint].[Board].Members,
-- access all children - sprints from the board and get the last 5 closed
Tail(
Filter(
[Sprint].CurrentMember.Children,
[Sprint].CurrentMember.GetBoolean("Closed")),
5)
)
)

 If you would like to get the last 5 closed sprints from a specific board, you can change the 4th line to specify the board:

Aggregate(
Generate(
-- get all boards
[Sprint].[D1 board],
-- access all children - sprints from the board and get the last 5 closed
Tail(
Filter(
[Sprint].CurrentMember.Children,
[Sprint].CurrentMember.GetBoolean("Closed")),
5)
)
)

(if you would like to change the number of sprints returned, update the 10th line from 5 to whichever number is necessary)

In the Rows section, switch back to the regular hierarchy, and in the Pages section, select the newly defined member as a Page filter. Now you'll be able to order your sprints by whichever measure or property you wish!

About the people involved in Sprints, there is a great solution by my colleague Daina in the eazyBI Community: https://community.eazybi.com/t/number-of-sprint-users-participants/5891

Best regards,

Nauris

[tpedriel]

Hello Nauris, I did exactly what you told me (the first formula, for all boards), and the results are 6 sprints for one project (Camelot 8, 2.1, 2.2, 2.3..etc) and the rest, of other projects are still appearing (It seems that the filter of project (Lynx NEX) that I applied is not taking into account.

I need to maintain the filter for all boards because in the dashboard I will be switching between reports.

Also, I have other questions about the tool such us:

• Sending reports to other emails (Is it necessary for them to have an EAZYBI account?)
• Can we have the average Row (is a calculated measure) instead of having as a column? See the example

If you prefer, maybe we can have a quick call and you explain to me all of that, also the steps to adapt it to the company (users that we need and type of access to share the reports to all the stakeholders involved). Now I'm creating reports to show to the managers and if they find the tool useful we will implement it to the whole organisation.

Thank you very much!

[Nauris Malitis]

First, you can update the "Peopleinsprint" measure to only display when a Sprint in your current selection has any Story Points committed:

CASE WHEN
[Measures].[Sprint Story Points committed]>0
THEN
Count(
  Filter(
    Descendants([Assignee].CurrentMember,[Assignee].[User]),
    ([Measures].[Issues history],
     [Sprint Status].[Active]) >=0
  )
)
END

Next, you can enable the "Nonempty" option in your Rows section. This will filter out the irrelevant Rows when a specific project gets selected in the Pages filter.

For now, only the currently logged-in user can subscribe to receive a report in PDF format to the user's email. In short- each user can only subscribe themselves.

There is no option to input a custom mailing list (for security purposes). If you would like to share a report outside your organization, you can use the public access token, but please consider data safety first and do not enable public access with a token to highly sensitive data.

For the averages:

You can enable the "Total rows" option and define a new calculated measure in the Measures dimension that will display the values of the "Sprint Story Points completed" for the Sprint rows and the Avg of these values for the Total row with the following formula:

CASE WHEN
[Sprint].CurrentHierarchyMember.Name="$total_aggregate"
THEN
Avg(
VisibleRowsSet(),
[Measures].[Sprint Story Points committed]
)
ELSE
[Measures].[Sprint Story Points committed]
END

If you have any further questions, you can write to our support email support@eazybi.com with a link from this thread, and I'll pick up the conversation from there!

Best regards,

Nauris

[tpedriel]

Hello, I'm still having problems with the filter of: las 5 closed sprints. When I filter by other projects, I'm having more than 5 sprints

I sent an email but the they didn't help me so much.

Thanks!