Reporting on inforamtion in work item history?  
Author Message
Ed Abshire





PostPosted: Team Foundation Server - Reporting & Warehouse, Reporting on inforamtion in work item history? Top

Is there a way to do a report against information contained in a workitem's history. For example, can we do a report of how many priority 1 bugs were opened that eventually got changed to a priority 2 or 3

-Ed



Visual Studio Team System18  
 
 
Nick Ericson - MSFT





PostPosted: Team Foundation Server - Reporting & Warehouse, Reporting on inforamtion in work item history? Top

Some work item history is stored in the cube and additional information is stored in the Warehouse. What question are you trying to answer with your report

There may be a better way of doing this - and I haven't fully tested these, but for your example the queries below should get you started:

"How many priority 1 bugs were opened that eventually got changed to a priority 2 or 3 "
I translated to:
How many bugs are there which are revised and do not currently have priority 1 - and the 1st revision of the bug had priority 1.

Two Relational queries:

1) Using intersect:
use
[TfsWarehouse]
select
[Work Item].[System_Id]
from
[Work Item]
where
[Work Item].[Microsoft_VSTS_Common_Priority] = 1 and
[Work Item].[System_Rev] = 1
intersect
select
[Current Work Item].[__TrackingId]
from
[Current Work Item] join
[Work Item] on
[Current Work Item].[Work Item] = [Work Item].[__ID]
where
[Work Item].[Microsoft_VSTS_Common_Priority] <> 1

2) Using subquery:

use
[TfsWarehouse]
select
[Work Item].[Microsoft_VSTS_Common_Priority],
[Work Item].[System_Title]
from
[Current Work Item] join
[Work Item] on
[Current Work Item].[Work Item] = [Work Item].[__ID]
where
[Work Item].[System_Rev] <> 1 and
[Work Item].[Microsoft_VSTS_Common_Priority] <> 1 and
[Current Work Item].[__TrackingId] in
(
select
[Work Item].[System_Id]
from
[Work Item]
where
[System_Rev] = 1 and
[Microsoft_VSTS_Common_Priority] = 1
)

These do not exclude closed bugs.