CountQueries
This feature is available in Enterprise and Ultimate editions.
Story Trend
Trend the number of stories that meet some criteria
Story Trend by Priority
select DateKey, WorkitemPriorityName as [Priority], count(*) as [Count] from Fact.Workitem WI join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey join Dim.AssetState S on WI.PrimaryAssetStateKey = S.AssetStateKey join Dim.Project P on P.ProjectKey=T.AncestorKey join Dim.AssetType AT on WI.AssetTypeKey = AT.AssetTypeKey join Dim.WorkitemPriority WIP on WI.WorkitemPriorityKey=WIP.WorkitemPriorityKey where AssetTypeName ='Story' and ProjectName='Call Center' and AssetStateName = 'Active' group by DateKey, WorkitemPriorityName order by DateKey
Story Trend by Team
select DateKey, TeamName as [Team], count(*) as [Count] from Fact.Workitem WI join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey join Dim.AssetState S on WI.PrimaryAssetStateKey = S.AssetStateKey join Dim.Project P on P.ProjectKey=T.AncestorKey join Dim.AssetType AT on WI.AssetTypeKey = AT.AssetTypeKey join Dim.Team on Team.TeamKey=WI.TeamKey where AssetTypeName ='Story' and ProjectName='Call Center' and AssetStateName = 'Active' group by DateKey, TeamName order by DateKey
Task Trend
Task Trend by Status
select DateKey, TaskStatusName, count(*) ActiveTaskCount from Fact.Workitem WI join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey join Dim.Project P on P.ProjectKey=T.AncestorKey join Dim.TaskStatus TS on TS.TaskStatusKey=WI.TaskStatusKey join Dim.AssetState S on S.AssetStateKey=WI.SecondaryAssetStateKey join Dim.AssetType AT on WI.AssetTypeKey = AT.AssetTypeKey where AssetTypeName='Task' and ProjectName='Release 1.0' and AssetStateName = 'Active' group by DateKey, TaskStatusName order by DateKey
Test Trend
Test Trend by Status
select DateKey, count(*) TestCount, TestStatusName from Fact.Workitem WI join Dim.SecondaryWorkitem on WI.SecondaryWorkitemKey = SecondaryWorkitem.SecondaryWorkitemKey join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey join Dim.Project on Project.ProjectKey=T.AncestorKey join Dim.Team on Team.TeamKey=WI.TeamKey join Dim.AssetState on AssetState.AssetStateKey=WI.SecondaryAssetStateKey join Dim.TestStatus TS on WI.TestStatusKey=TS.TestStatusKey where SecondaryWorkitemType='Test' and ProjectName='Release 1.0' and AssetStateName = 'Active' group by DateKey, TestStatusName order by DateKey
Test Trend using a Custom Checkbox
select DateKey, DiscoveredAfterPlanningName [DiscoveredAfterPlanning], count(*) TestCount from Fact.Workitem WI join Dim.SecondaryWorkitem SWI on WI.SecondaryWorkitemKey = SWI.SecondaryWorkitemKey join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey join Dim.Project P on P.ProjectKey=T.AncestorKey join Dim.DiscoveredAfterPlanning DAP on DAP.DiscoveredAfterPlanningKey=WI.DiscoveredAfterPlanningKey join Dim.AssetState S on S.AssetStateKey=WI.SecondaryAssetStateKey where SecondaryWorkitemType='Test' and ProjectName='Release 1.0' and AssetStateName = 'Active' group by DateKey, DiscoveredAfterPlanningName order by DateKey
Defect Priority Trend
select DateKey, WorkitemPriorityName as [Priority], count(*) as [Count] from Fact.Workitem WI join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey join Dim.Project P on P.ProjectKey=T.AncestorKey join Dim.AssetType AT on WI.AssetTypeKey = AT.AssetTypeKey join Dim.AssetState S on WI.PrimaryAssetStateKey = S.AssetStateKey join Dim.WorkitemPriority WP on WI.WorkitemPriorityKey=WP.WorkitemPriorityKey where AssetTypeName='Defect' and ProjectName='Call Center' and AssetStateName = 'Active' group by DateKey, WorkitemPriorityName order by DateKey