TFS 2013 Kanban board Done column (multiple complete states)

4
votes

In TFS 2013 Microsoft "fixed" a bug which allowed to map a WorkItem's state to the "Done" state in the Kanban board. This feature was heavily used in our company. There is a petition to bring it back back but I don't think it will make it: http://visualstudio.uservoice.com/forums/121579-visual-studio/suggestions/5589316-allow-multiple-complete-meta-state-mapping-in-tfs

In order to migrate TFS2012 to TFS2013 I would like to know where the customized "Done" state columns in TFS 2012 is stored in the database to create a report which shows which team used which WorkItem state as their "Done" state.

TFS2012 Kanban Board looked like that (note the dropdown): TFS2012 Kanban Board

TFS2013 Kanban Board looks like that (note NO dropdown): enter image description here

I do have access to the TFS Collection database and I would like to create a SQL query which shows me all the customization of this column in TFS 2012.

  • How can I get for "My WorkItem" the for every Team Project and every Team the customized "Done" state in TFS2012 database?
  • What other tables do I need to link to in order to get those states?

So far I could only get the TeamId, Name, ColumnType ProjectId but not the effective WorkItem and the "Done" column customization. How can I do that?

SELECT
    tbl_Board.TeamId, 
    tbl_Board.Revision, 
    tbl_BoardColumn.Name, 
    tbl_BoardColumn.ColumnType, 
    tbl_WorkItemTypeExtensions.Description, 
    tbl_BoardColumn.[Order], 
    tbl_WorkItemTypeExtensions.ProjectId
FROM
    tbl_WorkItemTypeExtensions
    RIGHT OUTER JOIN tbl_Board ON 
    tbl_WorkItemTypeExtensions.Id = tbl_Board.ExtensionId
    LEFT OUTER JOIN tbl_BoardColumn ON 
    tbl_Board.Id = tbl_BoardColumn.BoardId
2
tsqltfskanban

2 Answers

2
votes

Experts do not recommend accessing TFS DB but you can use Tfs_WarehouseDatabase if Reporting is configured and Data from all project collections is collected and stored in tables that are optimized for reporting. I do not have a knowledge about the db structures of TFS but going through few important online articles I managed understood quiet a few about it and as I understood the information that is required for you is in WorkItemsAretable.

With those queries below you can get the state of a certain work item on the Kanban board:

USE Tfs_DefaultCollection
SELECT TOP(10)
MarkerField + 1 as FieldId,
* 
FROM tbl_WorkItemTypeExtensions with(nolock) 
JOIN tbl_projects on tbl_WorkItemTypeExtensions.ProjectId = tbl_projects.project_id
WHERE tbl_projects.project_name LIKE '%ProjectName%

Copy the result from "FieldId" column to below's query at position XXXXXXXX

SELECT TOP 1000 
wid.Id, 
wia.State, 
wid.StringValue as Kanban, 
wia.[Work Item Type], 
wia.Title, 
tn.Name as Iteration
FROM tbl_WorkItemData wid with(nolock)
JOIN WorkItemsAre wia on wia.ID = wid.Id
JOIN TreeNodes tn on wia.IterationID = tn.ID
WHERE FieldId = XXXXXXXX and RevisedDate = '9999-01-01 00:00:00.000'
ORDER BY Id

Hope the sources that I have provided above will help your problem!

2
votes

I contacted the Microsoft Support and they provided me the following answer to my question:

SELECT
       board.TeamId,
       boardColumn.Name,
       workItemTypeExtensions.Rules
FROM           
       tbl_Board board JOIN
       tbl_WorkItemTypeExtensions workItemTypeExtensions ON board.ExtensionId = workItemTypeExtensions.Id JOIN
       tbl_projects projects ON workItemTypeExtensions.ProjectId = projects.project_id  JOIN
       tbl_BoardColumn boardColumn ON board.Id = boardColumn.BoardId
WHERE       
       projects.project_name LIKE '%< ENTER YOUR PROJECT NAME HERE >%' AND
       boardColumn.ColumnType = 2
ORDER BY
       board.TeamId,
       boardColumn.[Order]

When I check XML in the "Rules" column there I can find exactly what I was looking for.