2

I am trying to create a report showing estimated time vs actual time using Team Foundation Server 2017 for User Stories and Bugs. If I execute the following query I get all of the tasks and bugs:

SELECT *  FROM [Tfs_Warehouse].[dbo].[DimWorkItem]

I understand that a User Story can have zero to many tasks and the same goes for a Bug. How do I find the parent User Stories and child tasks as well as the Bugs and their child tasks?

The end goal is a report something like the image below:

enter image description here

Bill Greer
  • 3,046
  • 9
  • 49
  • 80

2 Answers2

2

It's not hard to find all of the child tasks of User Stories and Bugs.

You can use work item query and linking filters to select Child and Related link types, such as below:

enter image description here

However, there is no build-in feature to sum child task hours to parent in TFS. And TFS is also not intended to be a time tracking tool. To achieve this, you could either use some sever side plug-in such as tfsaggregator or some 3-rd party extension such as Imaginet Time Sheet & Timetracker. More details please refer this similar question: TFS - how do I sum child task hours to parent

Agile projects don't focus on how long individual tasks take -- they focus on how much value the development team is providing over the course of a set period of time. One thing might be estimated low, one task might be estimated high, but it ultimately doesn't matter as long as the team delivers what they committed to deliver.

Also take a look at this question: Is It possible to write a TFS Query to get Actual Time Taken for a Tasks?

PatrickLu-MSFT
  • 49,478
  • 5
  • 35
  • 62
  • Thank you for the information. I realize Agile's focus is designed to focus on how much value the team is providing. We are using Timetracker by 7 Pace and are requirements entail estimating vs actual reports. What piece of software are you conducting your query in the above screenshot? – Bill Greer Mar 08 '18 at 12:32
  • @BillGreer i was using the build-in TFS work item query tool, for more detail info please take a look at this tutorial: [List items based on linked dependents](https://learn.microsoft.com/en-us/vsts/work/track/linking-attachments#list-items-based-on-linked-dependents) – PatrickLu-MSFT Mar 08 '18 at 12:54
  • @BillGreer Hi Bill, any update on this issue did my reply helped or gave a right direction? – PatrickLu-MSFT Mar 12 '18 at 15:13
  • I am still struggling. I am trying to use the Query tool and I am able to Query Work Items of type User Story for a specific project. In my TFS Query tool I do not see the Linking Filters section where you find the children. – Bill Greer Mar 12 '18 at 15:54
  • 1
    @ PatrickLu-MSFT I see I am able to change the type of query to get the child links. Thank you so much. Is there a way I can see the actually SQL that is taking place. I would like to pull data into SSRS using SQL queries and aggregating some data. – Bill Greer Mar 12 '18 at 16:08
  • 1
    @BillGreer I'm afraid there is no such corresponding value in warehouse. And it's not suggest to write queries directly against the TFS databases. If you really want to do this, take a look at this two tutorials, which maybe helpful: https://blogs.msdn.microsoft.com/granth/2010/05/09/tfs2010-how-to-query-work-items-using-sql-on-the-relational-warehouse/ & https://stackoverflow.com/questions/42277899/tfs-2017-how-to-query-work-items-using-sql – PatrickLu-MSFT Mar 13 '18 at 10:16
0

You also may use RDL report "Stories Progress" and customize it.

  1. Edit parameter "DeliverableCategory" to include bugs in your report.

Edit Parameter

Report: Report 2. Edit parameter "IncludeTasks" to show childs.

Edit Parameter

Report: Report

  1. Also you may find sql queries to see tables and views that you may use in your custom report:

SQL Query

Shamrai Aleksander
  • 13,096
  • 3
  • 24
  • 31
  • I noticed the link for "Stories Progress" talks about Agile projects. Do you know if it is the same for Scrum projects? – Bill Greer Mar 06 '18 at 21:02
  • You may use [Backlog overview](https://learn.microsoft.com/en-us/vsts/report/sql-reports/backlog-overview-scrum). Also you can customize the report "Stories Progress". Just change the parameter "DeliverableCategory" from User Story to Product Backlog Item and save your report to report folder of your scrum project. – Shamrai Aleksander Mar 06 '18 at 22:20