Issue statistics per Sprint.

[Miro]

 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.

[Mohamed Benziane · Accepted solution]

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]

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

[Miro]

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

[Sven Schatter _Lively Apps_]

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)

[Miro]

 Sven,

 I am already a great fan of PQ! ;-)

 Played with it a bit over the last few days and I am quite charmed, my team is also very impressed. We are definitely buying and including it in our set of standard add-ons.

 Keep up the good job - I wish more plugins were so simple, straight-forward and intuitive, while at the same time providing so much functionality.

 thanks,
 Miro.

[Sven Schatter _Lively Apps_]

Wow - I think that's probably one of the nicest reviews we ever got!

I think @Felix Grund (Scandio) would be really happy to read this - it would probably make him blush. ;)

Thank you so much!

[Felix Grund (Scandio)]

I blushed. Thanks @[deleted], glad our plugin if of help to you and you're a fan!

Maybe there's a way we could make you post this as a "real" review on the marketplace if you haven't already :-)

[Miro]

Sure, I will remember to write a review on the marketplace.