In my previous post I described how using some readily available tools and some simple conventions for work item creation can equip yourself with some excellent information about how your team works. In this post I intend to explore some of the custom queries that I have been asked to create in day to day life as a TFS consumer.
One such request was for a query that will tell you, of all the work items you have delegated to others, which ones are outstanding. To achieve this follow these steps to create a TFS query. Having done that you will want to provide the following filters:
By changing the "AND State = Active" to be "AND State <> Active" one could query the completed and closed tasks. Optionally you could filter out Scenarios using the 'Work Item Type' field. Don't forget that team explorer can easily dump into and out of Excel which can be handy if you need an earbashing list. The query options straight out of the box are 'good enough' for most situations, and you don't have to write any code if that's not your thing.
For those of you wanting a little more than a simple query, e.g. you want parameterisation, I did find that creating your own TFS reports was a reasonably straight forward experience as well. The first thing to do is to create a report model on the TFS warehouse objects (TfsReportDS and TfsOlapReportDS by default). TfsOlapReportDS points to a SQL Analysis Services cube which the standard reports all use. One can spend more than a few hours just looking around in there, so consider yourself warned ;)
Creating the report model is easy enough, just browse to your TFS Sharepoint portal, and find the link to your Report server web interface (typically http://TFSServerName/Reports) and click on the TfsOlapReportDS of TfsReportDS link (the docs say you should favour the TfsOlapReportDS for reporting). Once there you can hit the generate model button and have it generate a reporting model for you. Then just click the link to report builder and start going nuts. I whipped up a non-parameterised report in under an hour that distilled the check-in history of an Area to look like an activity report, using all the wonderful comments our team had provided on each check-in, as a result of the check in policy.
As you can see I pulled in the change set details, the number of lines changed. Using a quick function on the max and min check in date would show how long the entire project took at a glance. Obvious improvements to this would be grouping by scenario and iteration. This report was brought to you courtesy of the Code Churn entity, which is useful for more than just this simple activity report. You can use code churn to ascertain the quality of your code as well.
By using the report designer in Visual Studio, you can start leveraging your SQL skills (of which I have very little) to create more elaborate reports, although I think I shall leave that as an exercise for the reader, rather than going into it here as the principle is largely the same. For further information on using the report services I can highly recommend Bob Meyers SQL Reporting Services blog and a book by Paul Turley, Todd Bryant, James Counihan, Dave DuVarney entitled Professional SQL Server 2005 Reporting Services for those who don't mind reading after a hard days coding.
In summary, developers often concentrate on making sure that each line they write is well crafted, and that's a good thing, so why not make check in time another opportunity to be efficient and useful instead of an inconvenience? There are benefits for developers and project leaders alike, and the key to finding those benefits is by having good data collected at check in time. The analysis tools provided by TFS are powerful enough to satisfy most needs, and if a SQL neophyte like me can whip up a semi-useful report in an hour, imagine what a SQL Jedi could achieve?
Subscribe