11

Is there a way in TFS's reports that you can get the amount of time taken for a work item to transition between states?

Looking at the data in the Tfs_Warehouse database I can see that there is a record of every revision of a work item, and using TSQL, I could write a query to get the ChangedDate of each state change and diff them. Sadly I lack any know how of SSAS and we don't have a BI specialist at the moment.

Can this be done in a way that I can produce a Report out of TFS without writing TSQL / API queries?

To clarify I'd like to know how long it took for a Bug (or an average for a subset of bugs) to move from Active -> Resolved (for example). We have more states than the MSF Agile Template so I cannot just use the Microsoft ActivatedDate / ResolvedDate fields for this.

Edit: I've added a bounty to try and get an answer.

DaveShaw
  • 52,123
  • 16
  • 112
  • 141
  • Are you still interested about getting and answer on this? – Nock May 15 '12 at 15:37
  • @Nockawa - Yes please, the "management" might be hiring a BI developer soon, so this will no longer be my responsibility, but I don't count my chickens before they hatch :). – DaveShaw May 15 '12 at 16:29

1 Answers1

1

Basically what you want is retrieve the ActivatedDate and ResolvedDate fields (or other date based ones) info for every bug then compute the timespan for each Work Item and do some per Work Item display or average. Am I right?

So, you have fields in the OLAP Cube for ActivatedDate and ResolvedDate, the reportable type is set to Dimension, which means you can't do what you want with these fields because they can't be used as values in Excel (which is what you'd need to do the maths). To make the matter more complex it looks like you only have the date and not the time, which would be not precise enough for you.

In short: SSAS/Olap/SSRS won't help you in this case...

Well, SSRS could help you but you would have to develop an TFS warehouse adaptor to inject the data you want in the TFS Warehouse: good luck with that! (it can be done, but the first time will be painful).

If you really need this info:

You can write a little program that will use the TFS API to make a Work Item Query then get the value of the fields you want and develop the logic you want. It can be an excel add-in if you want to populate a grid and generate a graph from it.

If you're interested in TFS and plan to keeping working with it: it's worth the investment thousand times. Writing apps is easy and the good thing is once you've made one you can easily tweak it for new needs.

Nock
  • 6,561
  • 1
  • 28
  • 27
  • Thanks, but the Resolved and Activated Date won't help in all cases. I was hoping to find the date difference between ChangedDate's for different revisions of the same work item - only when the state changes. As I mentioned at the end, I know how to do this in T-SQL and via the API. I was hoping someone could do just using the out of the box features of TFS. Cheers – DaveShaw May 15 '12 at 20:02
  • If you want to know how long it took to resolve a bug those two fields are the ones you should use, right? ChangedDate is modified each time a revision is created, you can get the ChangedDate for all revision using the API, but not with SSAS. All in all, you'll have to rely on the API for sure... – Nock May 15 '12 at 20:20
  • We have a custom workflow and the powers that be would like to be able to report on how long some work items spent at each stage - i.e. how long awaiting testing etc. If it's not possible with SASS, fair enough, I was only asked to look at it as a side job, and no-ones prompted me in a while. – DaveShaw May 15 '12 at 20:27
  • If you are doing it manually you can look at the WIT history and see the actual changes with dates. None of the out of the box ssrs reports will help. Best solution is to query the db for the revisions you care about and calculate from there. – Paul Swetz Jun 20 '16 at 17:43