Test steps and results in TFS 2010

All SSRS reports you get out of the box with TFS 2010 are trend reports and they don’t show any details. I was recently asked to build a custom report listing all test cases in a test plan and for each test case every step should be listed with action, expected result ant test outcome. Basically the same kind of info you can get for a single test case when you view the test results in Microsoft Test Manager 2010. This turned out to be quite a bit trickier than expected but after getting some much needed info from a developer at Microsoft (thank you Sriram) I was able to get it all together.

Getting test steps and results from Tfs_DefaultCollection

Test steps are stored as XML and are found in table WorkItemLongTexts. You can use this query to get them.

declare @fldIdSteps int = (Select top 1 fldid from Fields where ReferenceName = ‘Microsoft.VSTS.TCM.Steps’)
select * from WorkItemLongTexts where FldID = @fldIdSteps

Test results for each step are found in table tbl_TestActionResult. You can use this query to get them.

select * from tbl_TestActionResult

Now, you’ll notice there are a couple of issues popping up. You’ll need to join a XML result with a table result, there might be several revisions of the test steps and there are probably many results saved for each and every test case. But most of all, there is no obvious way to link steps to results. Every test step has an ID but tbl_TestActionResult does not contain a “test step ID” column.

ActionPath explained

This is where the column ActionPath in tbl_TestActionResult comes in. This column will typically contain an empty string, 8 chars string or 16 chars string. For a specific test result in a test run there will be one line with the empty string (this line is the over all test result) and then one line for each step, containing 8 or 16 chars. These ActionPath chars are hierarchical hexadecimal representations of test step ids. The first 8 chars is the step id and the next 8 chars (if they exist) is a shared step id.

And now you have the knowledge needed to pair up test results with corresponding test steps!

Some issues to consider…

There are a few more issues you’ll have to tackle as well.

  • Step ID != Sequence number. The test step id is not the same as the numbers you see when viewing a test case in a GUI. The numbers you see in the GUI are generated in the GUI to clarify the step sequence. The step id is never shown and the sequence number is not stored in the database. You’ll have to rely on the order of the <step> elements in the XML to figure out the step sequence.
  • Revisions and result. There can be several revisions of the test steps stored in table WorkItemLongTexts and there can be several test results stored for each revision. You’ll have to compare timestamps for revisions and results when joining them to avoid errors.
  • Performance. The Database Tfs_DefaultCollection is the production database and you should always take performance hits into consideration before deploying reports that read directly from your production database.
More details

When I first tried to figure out how this all worked I put a question up at the MSDN forums. For SQL and code samples you can go to my post on MSDN and get some more details.

Areas and Iterations

I’ve built a couple of custom reports for a client, and in doing so I’ve been forced to dive deep into the TFS 2010 database, which sometimes leads to interesting findings.

This time I found a strange approach to Areas and Iterations. For all work items areas and iterations are stored in the table xxTree. But then there are two other tables named tbl_Areas and tbl_Iterations, and they also contain all areas and iterations. But without all the other stuff found in xxTree. Now, one might be tempted to believe that these tables are somehow connected and perhaps contain a foreign key pointing to the  xxTree table. But no… As far as I can tell, xxTree is for work items while tbl_Areas and tbl_Iterations is used by MTM2010 for mapping test plans to areas and iterations.

So for anyone  writing queries using these tables, make sure you use the right ones or you will end up with some very strange results.

Misleading error message when setting up TFS Warehouse

Encountered a stupid error message yesterday. Had a TFS 2010 instance where reporting services had been disabled. When trying to enable it again we got an error message saying “Object reference not set to an instance of an object”. SQL instance was correct, database names (Tfs_Warehouse and Tfs_Analysis) were correct and I had a valid username and password for the TFSService account. However, as the original installation was done properly, using the TFSReports account for reporting services, we got this error message when trying to re-enable reporting services.

Now, even though the error was “correct”, I would have appreciated an error message that was a little bit more informative…

TFS Azure

Yesterday at the PDC keynote it was announced that a CTP of TFS on Windows Azure will be avilable in early 2011. From what I’ve seen soo far this looks really interesting. All the new Azure stuff should provide som very interesting benefits. I’m envisioning a really simple way to handle virtual machines for Lab Management, build servers, load test etc. And no more worries about single or multi server deployment. Cant wait to hear more about this!

Work Item Link Filters

Interesting fact regarding work item link filters when working with the Process Editor to create a new process template:

“Include all” does not equal “Include” + selecting all link types.

  • “Include all”: link types from all installed process templates will be shown to the users.
  • “Include” + check all boxes: Only the links types in the current process template will be shown to the user.

In my experience, “Include all” is never a good option for a work item link filter. It will only serve to confuse users and break reports. But when working with the Process Editor “Include” restricts your selection of link types to only include those specified in you process template. If you want to use link types from your template and some of the system defined link types (like parent/child) you will have to edit the XML markup manually.

TFS Backup Plan

Brian Harry recently blogged about a cool new feature in the next version of TFS Power Tools (no date yet). Backing up and Restoring your TFS Server is the most exhaustive post and makes for some interesting readin while Backing up your TFS Server with Sharepoint and Reporting more or less just states that you can backup the Sharepoint and Reporting parts of TFS as well.

Now, lets hope they add a tool for restoring the backups as well Ler