Visualize Your Sprints with this Project Roll-Up

Overview

Being able to see sprints from multiple projects as they're scheduled to complete on a timeline can help coordinate team resources. We needed a script that could give us the status of active sprints, when they were scheduled to complete, and the count of issues in each sprint along with their status. I used a VisualScript timeline to show this information. I added a timeline event for each sprint and inserted a table to help organize the counts for issues as rows in that table. I also added a hyperlink, so the user could easily click-through to get a list of the issues for that sprint.

Project roll-up example

The user interface I created for end-users is extremely simple. The user just has to provide the list of project names to get the current sprints for them.

User interface for project roll up script

How I Implemented It

Obtaining the Data

The first step to visualizing data is to grab the relevant data from Jira using VSCallFunctionForJiraQuery to query the Jira REST API. The only parameter to pass in this case is the list of projects defined by the user. This is defined as "project in (" + params + ")".

VSCallFunctionForJiraQuery(gSDJSLocalHost, "project in (" + params + ")", handleAllIssues);

I also call the function handleAllIssues to get a list of all issues that match the list of projects. Unfortunately, this isn't all the information I need to build our model. The sprint information in each issue is kept in a custom field, but I need to figure out what that custom field name is. It can be different for every Jira installation. I issue a Jira REST API call to get information about the first issue in my list of issues, and I tack on the "expand=names" parameter. This will cause the response to include custom field information I can use to discover the name of the custom field being used for sprint information.

// Retrieve all (field) names. Query the first issue... to obtain names only VSCallFunctionForJiraIssue(gSDJSLocalHost, issues[0].key + "?expand=names", processQueryData);

When the response is returned, I use the returned "expand=names" data to discover the name of the custom field used for the sprint by calling function getSprintCustomFieldName:

// Find the custom field containing sprint information function getSprintCustomFieldName(response) { for (var keyname in response.names) { if (response.names[keyname] === "Sprint") { return keyname; } } return null; }

At this point, I have all the information I need to build my model.

Building the Model

Once I have the issues list for each project and the name of the custom field being used to hold sprint information, I can build my model. The model consists of a "sprint list", a collection of objects representing sprints with some properties.

// Handle response for a request for names. This parameter, plus the issues list we already obtained provide enough info // to build the model function buildModel(sprintCustomFieldName) { var sprintInfo, sprints = null, issuesLen, id, numProjects = 0, projectIndex; var colors = ["#FBD585","EEEEEE", "D5D5D5"], projectList = []; if (!sprintCustomFieldName) { callback(null); return null; } // For each sprint, build a list of issues issuesLen = issues.length; for (var i = 0; i < issuesLen; i++) { sprintInfoField = issues[i].fields[sprintCustomFieldName]; if (!sprintInfoField) { continue; } // Parse out the sprint info sprintInfo = getSprintInfoFromCustomField(sprintInfoField); // Sprint info not parsed, or is not in active state.... skip if (!sprintInfo || (sprintInfo.state.toUpperCase() != "ACTIVE")) { continue; } // If this is the first time we've found an issue with this sprint, then add the sprint sprints object if (!sprints) { sprints = {}; } if (!sprints[sprintInfo.name]) { // Get a color for the project. Keep a list of projects and use its index in the list as an index to the color to use projectIndex = projectList.indexOf(issues[i].fields.project.name); if (projectIndex < 0) { projectList.push(issues[i].fields.project.name); projectIndex = projectList.length - 1; } projectColor = colors[projectIndex % colors.length]; sprints[sprintInfo.name] = {projectName: issues[i].fields.project.name, color: projectColor, start: sprintInfo.start,end: sprintInfo.end, statuses: [], issues: []}; } // Maintain a count of each sprint's issue statuses var statusIndex = sprints[sprintInfo.name].statuses.findIndex(s => s.name == issues[i].fields.status.name); if (statusIndex < 0) { sprints[sprintInfo.name].statuses.push({name: issues[i].fields.status.name, id: issues[i].fields.status.statusCategory.id, count: 1}); } else { sprints[sprintInfo.name].statuses[statusIndex].count++; } // Add the issue to the sprint's issues list sprints[sprintInfo.name].issues.push(issues[i]); } // Now sort the sprints status arrays for (var sprint in sprints) { sprints[sprint].statuses.sort((a,b) => {return (a.id < b.id) ? -1 : 1;}); } return sprints; }

A sprint model will store the sprint's start and end date, the project name, and a unique color (all sprints from the same project get the same color). For each issue in the list returned by Jira, I get its sprint information. If it's not assigned to a sprint (or is assigned to a sprint that is not currently active), I have no use for that issue and move on to the next issue. If the issue is assigned to an active sprint, I'll find that sprint in the sprints model (creating an entry if it's the first time we've seen an issue assigned to that sprint).

As issues are added to each sprint, I keep a count of the issue statuses (i.e., 4 "to do", 3 "in progress"). These statuses are sorted from lowest-to-highest so that they appear in the same order on each sprint.

Constructing the VisualScript from the Model

Now that the model is built, I use it to construct VisualScript SDK objects. After setting up the document, I add a timeline to the "main shape". I then add a timeline event for each sprint. Within each sprint event, I add a table to hold the name of the sprint in the first row, and then add a row for each of the statuses in the sprint's status list. Each status is hyperlinked to a Jira query that will show the list of issues in that sprint.

// Build VisualScript from the sprints (model) function buildVisualScript(sprints) { var vs = new VS.Document(); var mainShape = vs.GetTheShape(); var timeline = mainShape.AddTimeline() .SetEventType(VS.Timeline_EventTypes.Bubble) .SetUnits(VS.TimelineUnits.Month); //.SetAuto(true); timeline.AddDefaultShape() .SetShapeType(VS.ShapeTypes.RoundedRectangle) .SetFillColor("#EEEEEEEE") .SetTextSize(8); for (var sprint in sprints) { var firstStatusRow = 0; var event = timeline.AddEvent(sprints[sprint].end.toISOString().split("T")[0]); event.SetFillColor(sprints[sprint].color); var eventTable = event.AddTable(sprints[sprint].statuses.length + 1, 2) .JoinAcross(1, 1, 2); eventTable.AddCell(1, 1) .SetLabel(sprints[sprint].projectName + " - " + sprint); firstStatusRow = 2; for (var i = 0; i < sprints[sprint].statuses.length; i++) { eventTable.AddCell(firstStatusRow + i, 1) .SetLabel(sprints[sprint].statuses[i].name); eventTable.AddCell(firstStatusRow + i, 2) .SetLabel(sprints[sprint].statuses[i].count.toString()) .SetTextHyperlink(localhost + "/secure/IssueNavigator.jspa" + "?jql=" + encodeURIComponent("sprint=\"" + sprint + "\" AND status=\"" + sprints[sprint].statuses[i].name + "\"")); } } return vs; }

Rendering the VisualScript

The last step is to generate the visual using VisualScript callback function.

// Render the VisualScript
callback(vs.toJSON());

Creating the UI

After the script is complete, I set up the UI, so users can interact with my script and have a way to provide a list projects they want to see data for. In the top section of the VisualScript edit page, you'll see a field labeled "Parameter Template". The specification string for the UI Builder's "Parameter Template" is:

New parameter

I override the displayed name to a more user-friendly string, and I add some description text.

New string

More Information

This is just one example of the useful visuals that can be produced using VisualScript with a minimum of development effort.

The full source for this script can be seen here. To make the code easier to understand, I highlighted the part used for querying the data in green, the part for building the model in orange, and the part that creates VisualScript from the data in blue.

function VisualScript(localhost, params, callback) { var ID = 1, shapeToShapeID = {}, cellShapeContainerLookup = {}; VSCallFunctionForJiraQuery(gSDJSLocalHost, "project in (" + params + ")", handleAllIssues); // Retrieve all issues function handleAllIssues(issues) { if (!issues) { alert("No issues selected"); callback(); } // Retrieve all (field) names. Query the first issue... to obtain names only VSCallFunctionForJiraIssue(gSDJSLocalHost, issues[0].key + "?expand=names", processQueryData); // Handle response for a request for names. This parameter, plus the issues list we already obtained provide enough info // to build the model function processQueryData(response) { var vs, sprints = {}; var sprintCustomFieldName = getSprintCustomFieldName(response); sprints = buildModel(sprintCustomFieldName); if (sprints) { vs = buildVisualScript(sprints); } // Render the VisualScript callback(vs.toJSON()); }
// Handle response for a request for names. This parameter, plus the issues list we already obtained provide enough info // to build the model function buildModel(sprintCustomFieldName) { var sprintInfo, sprints = null, issuesLen, id, numProjects = 0, projectIndex; var colors = ["#FBD585", "EEEEEE", "D5D5D5"], projectList = []; if (!sprintCustomFieldName) { callback(null); return null; } // For each sprint, build a list of issues issuesLen = issues.length; for (var i = 0; i < issuesLen; i++) { sprintInfoField = issues[i].fields[sprintCustomFieldName]; if (!sprintInfoField) { continue; } // Parse out the sprint info sprintInfo = getSprintInfoFromCustomField(sprintInfoField); // Sprint info not parsed, or is not in active state.... skip if (!sprintInfo || (sprintInfo.state.toUpperCase() != "ACTIVE")) { continue; } // If this is the first time we've found an issue with this sprint, then add the sprint sprints object if (!sprints) { sprints = {}; } if (!sprints[sprintInfo.name]) { // Get a color for the project. Keep a list of projects and use its index in the list as an index to the color to use projectIndex = projectList.indexOf(issues[i].fields.project.name); if (projectIndex < 0) { projectList.push(issues[i].fields.project.name); projectIndex = projectList.length - 1; } projectColor = colors[projectIndex % colors.length]; sprints[sprintInfo.name] = { projectName: issues[i].fields.project.name, color: projectColor, start: sprintInfo.start, end: sprintInfo.end, statuses: [], issues: [] }; } // Maintain a count of each sprint's issue statuses var statusIndex = sprints[sprintInfo.name].statuses.findIndex(s => s.name == issues[i].fields.status.name); if (statusIndex < 0) { sprints[sprintInfo.name].statuses.push({ name: issues[i].fields.status.name, id: issues[i].fields.status.statusCategory.id, count: 1 }); } else { sprints[sprintInfo.name].statuses[statusIndex].count++; } // Add the issue to the sprint's issues list sprints[sprintInfo.name].issues.push(issues[i]); } // Now sort the sprints status arrays for (var sprint in sprints) { sprints[sprint].statuses.sort((a, b) => { return (a.id < b.id) ? -1 : 1; }); } return sprints; }
// Build VisualScript from the sprints (model) function buildVisualScript(sprints) { var vs = new VS.Document(); var mainShape = vs.GetTheShape(); var timeline = mainShape.AddTimeline() .SetEventType(VS.Timeline_EventTypes.Bubble) .SetUnits(VS.TimelineUnits.Month); //.SetAuto(true); timeline.AddDefaultShape() .SetShapeType(VS.ShapeTypes.RoundedRectangle) .SetFillColor("#EEEEEEEE") .SetTextSize(8); for (var sprint in sprints) { var firstStatusRow = 0; var event = timeline.AddEvent(sprints[sprint].end.toISOString().split("T")[0]); event.SetFillColor(sprints[sprint].color); var eventTable = event.AddTable(sprints[sprint].statuses.length + 1, 2) .JoinAcross(1, 1, 2); eventTable.AddCell(1, 1) .SetLabel(sprints[sprint].projectName + " - " + sprint); firstStatusRow = 2; for (var i = 0; i < sprints[sprint].statuses.length; i++) { eventTable.AddCell(firstStatusRow + i, 1) .SetLabel(sprints[sprint].statuses[i].name); eventTable.AddCell(firstStatusRow + i, 2) .SetLabel(sprints[sprint].statuses[i].count.toString()) .SetTextHyperlink(localhost + "/secure/IssueNavigator.jspa" + "?jql=" + encodeURIComponent("sprint=\"" + sprint + "\" AND status=\"" + sprints[sprint].statuses[i].name + "\"")); } } return vs; } // Find the custom field containing sprint information function getSprintCustomFieldName(response) { for (var keyname in response.names) { if (response.names[keyname] === "Sprint") { return keyname; } } return null; } // Parse the sprint info from the custom field and return the name function getSprintInfoFromCustomField(sprintInfoArray) { var i, sprintInfo = {}; try { if (!sprintInfoArray || sprintInfoArray.length === 0) { return null; } var infoTokens = sprintInfoArray[0].split(","); for (i = 0; i < infoTokens.length; i++) { if (infoTokens[i].toLowerCase().indexOf("name=") === 0) { var nameTokens = infoTokens[i].split("="); sprintInfo.name = nameTokens[1]; } if (infoTokens[i].toLowerCase().indexOf("startdate") === 0) { var startTokens = infoTokens[i].split("="); sprintInfo.start = new Date(startTokens[1]); } if (infoTokens[i].toLowerCase().indexOf("enddate") === 0) { var endTokens = infoTokens[i].split("="); sprintInfo.end = new Date(endTokens[1]); } if (infoTokens[i].toLowerCase().indexOf("state") === 0) { var stateTokens = infoTokens[i].split("="); sprintInfo.state = stateTokens[1]; } } } catch (e) { return null; } return sprintInfo; } } }