Planning a sprint in JIRA Agile involves assigning issues. You want to spread the load fairly, not overburdening or underburdening workers. To complicate matters, some issues take longer than others (with more Story Points or greater Time Estimate), and the capacity of each worker varies per week, as they may have time off or other responsibilities.
To meet this planning need in a simple way, we have developed the Sprint Capacity Report, a Confluence-based SQL report showing issues grouped per assignee, showing time estimates vs. assignee capacity:
The sprint to show is selected in a drop-down. Here we can see, for instance, that Dave is able to work 10 hours this sprint, is assigned one issue (SKP-14), which is estimated to take 30h, so Dave would be at 300% capacity unless we change things.
Implementation
The report is implemented as a giant parametrized SQL query running within a Confluence page, with the SQL running against the JIRA database.
User capacities are stored as user properties in JIRA. E.g. clicking 'Edit' in the Capacity column for Dave brings up:
Warning |
---|
NOT YET FINISHED |
Install the SQL plugin
Install the SQL plugin in JIRA.
Implementation
Define a Datasource to access JIRA
First we need to allow Confluence access to our JIRA database, via a datasource configured in Confluence's conf/server.xml
file.
Code Block | ||||
---|---|---|---|---|
| ||||
<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource" username="redradish_jira" password="fsw0823l23rs" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost/redradish_jira" maxActive="20" validationQuery="select 1"/> |
Restart Confluence. Afterwards, a simple query like select count(*) from jiraissue
against the JiraDS
database should work.
Sprints drop-down
Create a macro listing all Sprints in JIRA, most recent first:
The contents of this macro can be found on Bitbucket.
Define a Param user macro
Our query must be parametrized with the user's selection. Do this by creating a 'param' macro that uses $req.getParameter()
to fish out the sprint parameter.
TODO: link to Velocity definition.
Define a SQL wrapper macro
We can't just use the SQL macro here, as we want to embed our Param macro in it. See Allow macro content inside any other macro for a SQL wrapper macro that gets around this.
Query SQL
Create a page in Confluence called Sprint Capacity Planner, with contents:
TODO: clean up and put SQL on bitbucket