CountQueries

This feature is available in Enterprise and Ultimate editions.

Story Trend

Trend the number of stories that meet some criteria

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

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

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

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

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