nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Issue statistics per Sprint.

Miro_x0b8ww

Hi All,

I have received he following requirement: create a dashboard, which will display selected Issue stats for a number of past Sprints. E.g. number of Issues in each Fix Version for, say, the last 5 Sprints. More specifically, I would need this at a given point in time - say, at the closure of each Sprint.

I would need this both in tabular form and as a chart.

I don't mind using SQL. I was wondering if it is possible to get this kind of data snapshot from the database and how to approach it?

Then, once I've written the query, which plugin/gadget would be best to visualize it? I would prefer a dashboard gadget, which would take the query and display the results in a table and as a chart.

TIA for any pointers.

Accepted answers

Mohamed Benziane

Hello @Miro

You can take a look a this

https://community.atlassian.com/t5/Jira-Software-questions/How-to-get-Sprint-issue-list-by-SQL/qaq-p/320487

https://community.atlassian.com/t5/Jira-questions/Get-issues-by-sprint-from-database/qaq-p/422957

Regards

All comments

Mohamed Benziane

Hello @Miro

You can take a look a this

https://community.atlassian.com/t5/Jira-Software-questions/How-to-get-Sprint-issue-list-by-SQL/qaq-p/320487

https://community.atlassian.com/t5/Jira-questions/Get-issues-by-sprint-from-database/qaq-p/422957

Regards

Miro_x0b8ww

Thank you, I will play with these queries a bit.

Miro_x0b8ww

OK, so it works nicely. Here is the prototype query:

SELECT sp.NAME, COUNT(*) AS 'Retest' FROM customfieldvalue c
LEFT OUTER JOIN jiraissue i ON (i.ID=c.ISSUE)
LEFT OUTER JOIN AO_60DB71_SPRINT sp ON (sp.ID=c.STRINGVALUE)
WHERE c.STRINGVALUE IN
(SELECT CAST(s.ID AS nvarchar) spId from AO_60DB71_SPRINT s WHERE NAME LIKE '%xxx%')
AND SUMMARY LIKE '%RETEST%'
GROUP BY sp.ID, sp.Name
ORDER BY sp.ID DESC

The selection of Sprints based on a string match is quite ugly and error-prone, so in the next iteration I will change that to the selection of sprints from a project / board.

I also don't quite like the tagging of an issue in the Summary field (this should be done by a label, ideally).

As for visualizing the results, the following plugin seems to be doing a nice job:

https://marketplace.atlassian.com/apps/1211199/pocketquery-for-confluence?hosting=server&tab=overview

Former user

Hi @Miro,

good job figuring out this query!

Should you need any help with writing your templates or PocketQuery in general please feel free to contact us at our ServiceDesk for PocketQuery. You are always welcome!

Best regards,
Sven (Scandio)

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-software

dashboard

gadget