You can query for links between bugs, tasks, and other types of work items by using FactWorkItemLinkHistory and the associated dimension tables. To include details about the linked work items, you join SourceWorkItemID and TargetWorkItemID to Dim.System_ID.
For information about the measures and dimensions that are associated with these tables in the SQL Server Analysis Services cube, see Analyze and report on work items and test case data using the Work Item perspective.
FactWorkItemLinkHistory is associated with the following dimension tables:
DimTeamProject
DimPerson
DimWorkItem
 Note |
|---|
This table contains links that have been removed. Links that have not been removed have the RemovedDate set to Jan 1, 9999. When a link is removed, the removed date is set to the date and time when it was removed. You can use RemovedDate > GetDate() to filter out links that have been removed. |
You can use the following sample query to find the following types of information:
total number of hours for completed work
original estimated work
remaining work
total story points for each user story in a team project under a specified area path
For information about the Coalesce function that is used in the sample query, see the following page on the Microsoft Web site: COALESCE (Transact-SQL).
| Note |
|---|
This query assumes that a user story is linked to other work items through Child links. |
 Copy Code | |
|---|---|
declare @TeamProjectNodeSK int select @TeamProjectNodeSK = ProjectNodeSK from GetProjectNodeInfoFromReportFolder(N'/TfsReports/VSTSDF/ProcessDev10') -- This table-value function returns the ProjectNodeSK: the Surrogate Key of a team project under a certain area path. declare @TeamProjectCollectionGuid nvarchar(36) select @TeamProjectCollectionGuid = pc.ProjectNodeGUID from DimTeamProject p inner join DimTeamProject pc on p.ParentNodeSK = pc.ProjectNodeSK where p.ProjectNodeSK = @TeamProjectNodeSK -- This query finds the team project collection GUID by joining TeamProject.ParentNodeSK to TeamProject.ProjectNodeSK select wi.System_Title ,wi.System_Id ,coalesce(sum(cwi_child.Microsoft_VSTS_Scheduling_CompletedWork), 0) as Total_CompletedWork -- Finds the total number of hours of completed work. ,coalesce(sum(cwi_child.Microsoft_VSTS_Scheduling_OriginalEstimate), 0) as Total_OriginalEstimate --Finds the total number of hours of original estimate. ,coalesce(sum(cwi_child.Microsoft_VSTS_Scheduling_RemainingWork), 0) as Total_RemainingWork --Finds the total number of hours of remaining work. ,coalesce(sum(cwi_child.Microsoft_VSTS_Scheduling_StoryPoints), 0) as Total_StoryPoints --Finds the total story points. from DimWorkItem wi cross apply GetWorkItemsTree(@TeamProjectCollectionGuid, wi.System_Id, N'Child', DEFAULT) wit left join FactCurrentWorkItem cwi_child on cwi_child.WorkItemSK = wit.ChildWorkItemSK where wi.TeamProjectSK = @TeamProjectNodeSK and wi.System_WorkItemType = N'User Story' and wi.System_RevisedDate = CONVERT(datetime, '9999', 126)--The revised date of the work item is equal to today. and wi.System_State = N'Active' group by wi.System_Id, wi.System_Title order by wi.System_Id | |
See Also
Concepts
Other Resources
