Work Item Linked Queries/Scripts for Reporting in Visual Studio Team Services (VSTS)

Question

I wanted to create a report on my VSTS Team Project which has the details of the Work Item Linking relationships in a Team Project. Ex: Epics→Features→UserStories. Since there are parent/child relationships between Epics & Features and also between Features & UserStories, I wanted to create a report (.csv or .xls) which has all the details of these workitems and their relationships.

Could someone let me know on what is the best and easy way to achieve this?

Answer 1

I suggest that you could check it via project with TFS add-in (included in VS or team explorer).

1. Create a query (Tree of work items)
2. Open Project
3. Click Team=>Choose Team project
4. Click Get Work Items=>Select that query=>Find=>Select All=>OK

After that you can check the result in project, you also can collapse parent item by clicking corresponding title.

You can achieve it programmatically by using TFS API with Excel add-in.

1. Create a document level excel add-in project
2. Install Microsoft Team Foundation Server Extended Client package
3. Run query by using Query.RunLinkQuery method.
4. Get test cases in requirement test suites
5. Check test results of these test cases
6. Get associated work item of test result
7. Save data to excel per your detail requirement.

Simple code:

var credentials = new NetworkCredential("[user name]", "[password]");
            TfsTeamProjectCollection tfsCollection = new TfsTeamProjectCollection(new Uri("https://XX.visualstudio.com"));
            ITestManagementService tms = tfsCollection.GetService<ITestManagementService>();
            ITestManagementTeamProject teamProject = tms.GetTeamProject("[team project name]");
            WorkItemStore workitemstore = tfsCollection.GetService<WorkItemStore>();
            QueryHierarchy queryRoot = workitemstore.Projects["[team project name]"].QueryHierarchy;
            QueryFolder queryFolder = queryRoot["Shared Queries"] as QueryFolder;
            QueryDefinition qd = queryFolder["[query name]"] as QueryDefinition;
            Dictionary<string, string> variables = new Dictionary<string, string>();
            variables.Add("project", "[team project name]");
            Query query = new Query(workitemstore, qd.QueryText, variables);

            var results = query.RunLinkQuery();
            foreach(var lr in results)
            {
                //check relationship according to sourceId, TargetId and LinkTypeId (2: child)
                int parentId = lr.SourceId;
                int currentId = lr.TargetId;
            }

            foreach (ITestPlan p in teamProject.TestPlans.Query("Select * From TestPlan"))
            {
                Console.WriteLine("Plan - {0} : {1}", p.Id, p.Name);

                foreach (var suite in p.RootSuite.SubSuites)
                {
                    IRequirementTestSuite requirementSuite = suite as IRequirementTestSuite;
                    if (requirementSuite != null)
                    {
                        var requirementId = requirementSuite.RequirementId;
                        //check requirementId with user story Id
                        foreach(var tc in requirementSuite.TestCases)
                        {
                            var lastResult = teamProject.TestResults.ByTestId(tc.Id).OrderByDescending(tr => tr.DateStarted).FirstOrDefault();
                            if(lastResult.Outcome== Microsoft.TeamFoundation.TestManagement.Client.TestOutcome.Failed)
                            {
                               int[] ids= lastResult.QueryAssociatedWorkItems();
                                foreach(int id in ids)
                                {
                                    var wit = workitemstore.GetWorkItem(id);
                                }

                            }

                        }


                    }
                }
            }