I'm using TFS 2012 and the Scrum process template.
I would like a way to see stories that were added to sprint after a certain point in the timeline of that sprint.
We typically have a mid-sprint "mini" planning session. This allows us to re-balance existing workload across the team, but it also allows us to add additional PBIs/User Stories to the sprint if we see we have finished more work than expected.
I can't see a way to query to see when a story's iteration path was changed. Is this possible?
I found it necessary to create a custom application using the TFS APIs to accomplish this.
First, you need to obtain the relevant work item list:
var tfs = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(new Uri(options.TfsUri));
var workItemStore = tfs.GetService<WorkItemStore>();
var project = workItemStore.Projects[options.ProjectName];
var projectIterationPath = string.Format("{0}\\{1}", project.Name, options.IterationPath);
var items = project.Store.Query(
"SELECT [System.Id] FROM WorkItems WHERE [System.WorkItemType] IN ('User Story', 'Product Backlog Item', 'Bug') AND [System.IterationPath] = '" +
projectIterationPath + "'");
Then you must sum up various point totals (total work vs completed work):
var pointTotal = workItems
.Where(
i =>
i.Fields[effortField].Value != null &&
decimal.TryParse(i.Fields[effortField].Value.ToString(), out temp))
.Sum(i => decimal.Parse(i[effortField].ToString()));
var pointsCompleted = workItems
.Where(
i =>
(string.Compare(i.State, "done", true) == 0 || string.Compare(i.State, "closed", true) == 0) &&
i.Fields[effortField].Value != null &&
decimal.TryParse(i.Fields[effortField].Value.ToString(), out temp))
.Sum(i => decimal.Parse(i[effortField].ToString()));
Then you need to obtain information about the start and end iteration dates:
var iterationSchedule = GetIterationSchedule(tfs, project.Uri.ToString(),
private static ScheduleInfo GetIterationSchedule(TfsTeamProjectCollection tfs, string projectUri, string iterationPath)
{
var css = tfs.GetService<ICommonStructureService4>();
var structures = css.ListStructures(projectUri);
var iterations = structures.FirstOrDefault(s => s.StructureType.Equals("ProjectLifecycle"));
if (iterations != null)
{
string projectName = css.GetProject(projectUri).Name;
XmlElement iterationsTree = css.GetNodesXml(new[] {iterations.Uri}, true);
return GetIterationDates(iterationsTree.ChildNodes[0], projectName, iterationPath);
}
return null;
}
private static ScheduleInfo GetIterationDates(XmlNode node, string projectName, string iterationPath)
{
var targetIterationPath = string.Format("\\{0}\\Iteration\\{1}", projectName, iterationPath);
XElement targetIteration = null;
if (node != null)
{
var iterations = XDocument.Parse(node.InnerXml);
targetIteration = iterations.Descendants("Node")
.Where(n => n.Attribute("Path") != null && !string.IsNullOrEmpty(n.Attribute("Path").Value))
.SingleOrDefault(n => string.Compare(n.Attribute("Path").Value, targetIterationPath, true) == 0);
}
if (targetIteration != null)
{
// Attempt to read the start and end dates if they exist.
string strStartDate = (targetIteration.Attribute("StartDate") != null)
? targetIteration.Attribute("StartDate").Value
: null;
string strEndDate = (targetIteration.Attribute("FinishDate") != null)
? targetIteration.Attribute("FinishDate").Value
: null;
DateTime? rStateDate = null, rEndDate = null;
if (!string.IsNullOrEmpty(strStartDate) && !string.IsNullOrEmpty(strEndDate))
{
DateTime startDate;
if (DateTime.TryParse(strStartDate, out startDate))
rStateDate = startDate;
DateTime endDate;
if (DateTime.TryParse(strEndDate, out endDate))
rEndDate = endDate;
}
return new ScheduleInfo
{
IterationPath = iterationPath,
StartDate = rStateDate,
EndDate = rEndDate
};
}
return null;
}
And finally you piece it all together to generate your summary for a sprint:
foreach (var item in workItems)
{
var postSprintStartRevisions = (from r in item.Revisions.Cast<Revision>()
where r.Fields.Cast<Field>()
.Any(f => f.Name == "Revised Date" && ((DateTime) f.Value) >= postStartDate)
&& r.Fields.Cast<Field>()
.Any(
f =>
f.Name == "Iteration Path"
&& string.Compare(f.OriginalValue.ToString(), projectIterationPath, true) != 0
&& string.Compare(f.Value.ToString(), projectIterationPath, true) == 0)
select r).ToArray();
if (postSprintStartRevisions.Any() && item[effortField] != null)
{
pointsPostSprintStart += decimal.Parse(item[effortField].ToString());
}
}
The code isn't pretty, but it does give me want I need.
Usage:
Usage: pbitracker -TfsUri [uri] -ProjectName [name] -IterationPath [path] -b 3
-u, --TfsUri Required. TFS Project Collection Uri. Ex:
http://tfs:8080/tfs/defaultcollection
-p, --ProjectName Required. TFS Project Name. Ex: MyProject
-i, --IterationPath Required. Iteration Path. Ex: 'GA\Sprint 3'
-b, --StartDateBuffer (Default: 3) Number of days after the start date to
consider the worked as added post-start
--help Display this help screen.
What I consider "added post-start" is based on an option passed into the app. I default it to 3 days, so if your sprint started on a Monday and you added work on a Thursday, that work is considered points added post-start. I'll try and get this up on GitHub or CodePlex and I'll update the answer.
Example output:
Querying with the following options...
TFS Uri: http://tfs:8080/tfs/DefaultCollection
Project Name: MyProject
Iteration Path: GA\Sprint 16
Please wait...
Iteration Start Date: 2/24/2014 12:00:00 AM
Iteration End Date: 3/7/2014 12:00:00 AM
Iteration Point Total: 168
Iteration Point Total Completed: 168
Iteration Points Starting Points: 162
Iteration Points Added Post-Start: 6