Austin Agile DevOps

DevOps in the Cloud
posts - 70 , comments - 7 , trackbacks - 0

TFS 2010 Web Part for Data Warehouse Views

Team Foundation Server 2010 includes support for Data Warehouse and Analysis Cube Views.

The company, Amrein Engineering, sells a web part that uses SQL statements to source data.

You can implement this web part very easily to do some very nice reporting of TFS data.

For example, you cannot use "time" as a query criteria in Team Explorer. You can only use the date. That is, you can ask for all work items changed by Joe Black on 9/11/2011. But you cannot ask for changes between 3 and 4 PM on that date. However, you can qualify SQL queries by date and time.

Using the web part from Amrein I was able to:

  1. Develop a SQL query for work items changed between two timestamps
  2. Implement the query in the web part to display on the TFS team portal
  3. Add filters and formating to the web part

I was able to display the work item ID as a hyperlink that brought up the work item in the browser or in the Team Web Access editor.

Here's a couple examples of SQL queries against the TFS_Warehouse DB, dbo.WorkItemHistoryView view. Note: using ASOF queries with this view requires the use of the "System_RevisedDate > [ASOF Date]" and the "RecordCount > 0" conditions because of compensating records. "Whenever a work item is updated, a pair of records is added to the warehouse. The first record negates the previous record. This makes querying faster." See book 'Professional Team Foundation Server 2010'

SELECT	 [System_ChangedDate] as Changed
		,[System_ChangedBy] as ChangedBy
		,[System_WorkItemType] as WIT		
		,[Microsoft_VSTS_Common_StackRank] as Seq
		,[System_Id] as ID
		,[System_Title] as Title
		,[System_State] as Ste
		,[System_Reason] as Reason
		,[System_AssignedTo] as Assigned		
		,[Microsoft_VSTS_Scheduling_StoryPoints] as Points
		,[Microsoft_VSTS_Build_FoundIn] as FoundIn
		,[Microsoft_VSTS_Build_IntegrationBuild] as FixedIn
		,[Microsoft_VSTS_Common_Priority] as Pri
		,[Microsoft_VSTS_Common_Severity] as Sev
		,[Microsoft_VSTS_Scheduling_OriginalEstimate] as Est
		,[Microsoft_VSTS_Scheduling_RemainingWork] as Rem
		,[Microsoft_VSTS_Scheduling_CompletedWork] as Act
		,[IterationPath] as iPath
		,[AreaPath] as aPath
  FROM [Tfs_01Warehouse].[dbo].[WorkItemHistoryView]
  WHERE [AreaPath] LIKE '\Cw%'
  AND	([System_ChangedDate] BETWEEN DATEADD([DAY], -1, GETDATE()) AND GETDATE())
  AND	RecordCount > 0
  AND	[System_ChangedBy] <> 'Build Admin'
  ORDER BY 	[System_ChangedDate] DESC

/****** WORK ITEMS CHANGED HISTORY ******/ SELECT System_Id as ID ,max(System_WorkItemType) as WIT ,max(System_Title) as Title ,max(System_ChangedBy) as ChangedBy ,max(System_ChangedDate) as Changed ,max(AreaPath) as aPath FROM [Tfs_01Warehouse].[dbo].[WorkItemHistoryView] WHERE [AreaPath] LIKE '\Cw%' AND [System_ChangedBy] = 'Joe Black' AND ([System_ChangedDate] BETWEEN '9/1/2011 15:00' AND '9/1/2011 16:00') AND [System_RevisedDate] >= '9/1/2011 16:00' AND RecordCount > 0 GROUP BY [System_Id] ORDER BY [System_Id]

syntax highlighted by Code2HTML, v. 0.9.1

Now using the following URL syntax I was able to format the ID column in the web part as a link to the work item:

  • Display work item in browser: http://[app server name]:[port number]/tfs/[collection name]/WorkItemTracking/Workitem.aspx?artifactMoniker=[work item id number]
  • Display work item in Team Web Access editor: http://[app server name]:[port number]/tfs/[collection name/web/wi.aspx?id=[work item id number]

Print | posted on Friday, September 9, 2011 2:33 PM | Filed Under [ Agile SCM Talk Blog ]


No comments posted yet.
Post A Comment

Powered by: