TFS 2013 Kanban board Done column (multiple complete states)

Question

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):

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

score 2 · Answer 1 · edited May 23 '17 at 12:29

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

Create a Detailed Report using Report Designer

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

Thank you for your answer @Pathum. I was abroad on business and couldn't check your answer. I'm totally aware of not accessing TFS SQL directly but if you can provide me another way to get to the information I need let me know. It seems didn't get my question properly. What I get with the provided SQL queries are where certain work items are in the Kanban board. It's not what I'm looking for. But what I'm looking for is the mapping how work items are mapped in the Kanban board. And I need to know the mapping of the "Done" state (check my question). Please let me know for further ideas. — Bruno Bieri, Apr 27 '15 at 13:37
@BrunoBieri Sorry for late feedback,I do have some information Will go through them properly and get back to you :) rather than copy pasting the link. — Heisenberg, May 13 '15 at 08:57

score 2 · Accepted Answer · answered May 28 '15 at 05:56

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.

TFS 2013 Kanban board Done column (multiple complete states)

2 Answers2