EffortQueries
This feature is available in Enterprise and Ultimate editions.
Effort Trend
declare @startDate datetime, @days int select @startDate='2009-08-22', @days=60 select Date.DateKey, isnull(sum(Effort.Effort), 0) EffortToDate from Dim.Date left join ( select DateKey, Effort from Fact.Effort join Tree.ProjectParent T on T.DescendantKey=Effort.ProjectKey join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey where RootProject.ProjectName='Call Center' ) Effort on Effort.DateKey<=Date.DateKey where Date.Date>=@startDate and Date.Date<DATEADD(D, @days, @startDate) group by Date.DateKey order by Date.DateKey
Effort Trend For a Specific Member
declare @startDate datetime, @days int select @startDate='2009-08-22', @days=60 select Date.DateKey, isnull(sum(Effort.Effort), 0) EffortToDate from Dim.Date left join ( select Effort.DateKey, Effort.Effort from Fact.Effort join Tree.ProjectParent T on T.DescendantKey=Effort.ProjectKey join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey join Dim.Member on Member.MemberKey=Effort.MemberKey where RootProject.ProjectName='Call Center' and Member.MemberName='Alfred Smith' ) Effort on Effort.DateKey<=Date.DateKey where Date.Date>=@startDate and Date.Date<DATEADD(D, @days, @startDate) group by Date.DateKey order by Date.DateKey
Effort Trend for a Member Group
declare @startDate datetime, @days int select @startDate='2009-08-22', @days=60 select Date.DateKey, isnull(sum(Effort.Effort), 0) EffortToDate from Dim.Date left join ( select Effort.DateKey, Effort.Effort from Fact.Effort join Tree.ProjectParent T on T.DescendantKey=Effort.ProjectKey join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey join Bridge.MemberGroup B on B.MemberKey=Effort.MemberKey join Dim.MemberGroup on MemberGroup.MemberGroupKey=B.MemberGroupKey where RootProject.ProjectName='Call Center' and MemberGroup.MemberGroupName='Local Developers' ) Effort on Effort.DateKey<=Date.DateKey where Date.Date>=@startDate and Date.Date<DATEADD(D, @days, @startDate) group by Date.DateKey order by Date.DateKey
Effort Trend for a Sprint
declare @startDate datetime, @days int select @startDate='2009-08-22', @days=60 select Date.DateKey, isnull(sum(Effort.Effort), 0) EffortToDate from Dim.Date left join ( select DateKey, Effort from Fact.Effort join Tree.ProjectParent T on T.DescendantKey=Effort.ProjectKey join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey join Dim.Iteration on Iteration.IterationKey=Effort.IterationKey where RootProject.ProjectName='Call Center' and IterationName='Month A 1st Half' ) Effort on Effort.DateKey<=Date.DateKey where Date.Date>=@startDate and Date.Date<DATEADD(D, @days, @startDate) group by Date.DateKey order by Date.DateKey
Effort Trend for a Team
declare @startDate datetime, @days int select @startDate='2009-08-22', @days=60 select Date.DateKey, isnull(sum(Effort.Effort), 0) EffortToDate from Dim.Date left join ( select DateKey, Effort from Fact.Effort join Tree.ProjectParent T on T.DescendantKey=Effort.ProjectKey join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey join Dim.Team on Team.TeamKey=Effort.TeamKey where RootProject.ProjectName='Call Center' and TeamName='Team A' ) Effort on Effort.DateKey<=Date.DateKey where Date.Date>=@startDate and Date.Date<DATEADD(D, @days, @startDate) group by Date.DateKey order by Date.DateKey
Current Member Load
declare @lastDateKey int select @lastDateKey=max(DateKey) from Fact.Workitem select isnull(X.MemberKey,Y.MemberKey) MemberKey, AllocatedToDo, Done = isnull(Done, 0), MemberLoad = (isnull(AllocatedToDo,0) + isnull(Done, 0)) from ( select isnull(MemberKey, -1) MemberKey, IterationKey, sum(AllocatedDetailEstimate) AllocatedDetailEstimate, sum(AllocatedToDo) AllocatedToDo from ( select Workitem.DateKey, Workitem.WorkitemKey, IterationKey, AllocatedDetailEstimate=DetailEstimate/isnull(OwnerCount, 1), AllocatedToDo=ToDo/isnull(OwnerCount, 1) from Fact.Workitem left join ( select DateKey, WorkitemKey, count(MemberKey) OwnerCount from Bridge.WorkitemOwner group by DateKey, WorkitemKey ) Owners on Owners.DateKey=Workitem.DateKey and Owners.WorkitemKey=Workitem.WorkitemKey ) Workitem left join Bridge.WorkitemOwner on WorkitemOwner.WorkitemKey = Workitem.WorkitemKey and Workitem.DateKey = WorkitemOwner.DateKey where Workitem.DateKey = @lastDateKey group by WorkitemOwner.MemberKey, Workitem.IterationKey ) X full join ( select MemberKey, IterationKey, sum(Effort) Done from Fact.Effort group by MemberKey, IterationKey ) Y on X.MemberKey=Y.MemberKey and X.IterationKey=Y.IterationKey join Dim.Iteration on Iteration.IterationKey=isnull(X.IterationKey,Y.IterationKey) where IterationAssetState='Active'