Skip to main content
System StatusContact Support
VersionOne Community

EstimateQueries

This feature is available in Enterprise and Ultimate editions.

Project Burndown

This section contains queries used to retrieve Project Burndown data.

Here's the query to get all open estimates for a project called 'Call Center' and all it's child projects.

SELECT D.Date, SUM(WI.Estimate) [Open Estimate]
FROM Fact.Workitem WI
    JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
    JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
    JOIN Dim.AssetState S ON S.AssetStateKey = WI.PrimaryAssetStateKey
    JOIN Dim.Date D ON D.DateKey = WI.DateKey
WHERE S.AssetStateName = 'Active' AND P.ProjectName = 'Call Center'
GROUP BY D.Date
ORDER BY D.Date

 

Project Burndown filtered to a specific Iteration

To filter these results by iteration, you can join the Dim.Iteration table to the query.

SELECT D.Date, SUM(WI.Estimate) [Open Estimate]
FROM Fact.Workitem WI
    JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
    JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
    JOIN Dim.AssetState S ON S.AssetStateKey = WI.PrimaryAssetStateKey
    JOIN Dim.Date D ON D.DateKey = WI.DateKey
    JOIN Dim.Iteration I ON WI.IterationKey = I.IterationKey
WHERE S.AssetStateName = 'Active' AND P.ProjectName = 'Call Center' AND I.IterationName = 'Month A 1st Half'
GROUP BY D.Date
ORDER BY D.Date

 

Project Burndown filtered to a specific Theme

Here's another example where burndown data is filtered to a specific theme:

SELECT D.Date, SUM(WI.Estimate) [Open Estimate]
FROM Fact.Workitem WI
    JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
    JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
    JOIN Dim.AssetState S ON S.AssetStateKey = WI.PrimaryAssetStateKey
    JOIN Dim.Date D ON D.DateKey = WI.DateKey
    JOIN Dim.Theme T ON WI.ThemeKey = T.ThemeKey
WHERE S.AssetStateName = 'Active' AND P.ProjectName = 'Call Center' AND T.ThemeName = 'Customer Management'
GROUP BY D.Date
ORDER BY D.Date

 

Project Burndown that does not include child projects

Finally, If you are not interested in including child projects, you can remove the JOIN to Tree.ProjectParent and change the Dim.Project join.

SELECT D.Date, SUM(WI.Estimate) [Open Estimate]
FROM Fact.Workitem WI
    JOIN Dim.Project P ON P.ProjectKey = WI.ProjectKey
    JOIN Dim.AssetState S ON S.AssetStateKey = WI.PrimaryAssetStateKey
    JOIN Dim.Date D ON D.DateKey = WI.DateKey
WHERE S.AssetStateName = 'Active' AND P.ProjectName = 'Call Center'
GROUP BY D.Date
ORDER BY D.Date

 

Program Burndown

SELECT D.DateKey, SUM(Estimate) AS [Open Estimate]
FROM Fact.Workitem WI
    JOIN Dim.Date D ON WI.DateKey = D.DateKey
    JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
    JOIN Bridge.ProjectProgram BPP ON WI.ProjectKey = BPP.ProjectKey
    JOIN Dim.Program P ON BPP.ProgramKey = P.ProgramKey
WHERE ProgramName = 'Next Release'
    and AssetStateName = 'Active'
GROUP BY D.DateKey
ORDER BY D.DateKey

 

Project Burndown with a Custom Checkbox

SELECT  WI.DateKey, SUM(Estimate) AS [Open Estimate]
FROM Fact.Workitem WI
    JOIN Dim.Date D ON WI.DateKey = D.DateKey
    JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
    JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
    JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
    JOIN Dim.RoadmapItem R ON WI.RoadMapItemKey = R.RoadMapItemKey
WHERE ProjectName = 'Call Center' and AssetStateName = 'Active' and RoadMapItemName = 'Yes'
GROUP BY WI.DateKey
Order By WI.DateKey

 

Project Burndown with a Custom Dropdown

SELECT  WI.DateKey, SUM(Estimate) AS [Open Estimate]
FROM Fact.Workitem WI
    JOIN Dim.Date D ON WI.DateKey = D.DateKey
    JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
    JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
    JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
    JOIN Dim.ShirtSize ON WI.ShirtSizeKey = ShirtSize.ShirtSizeKey
WHERE ProjectName = 'Call Center' and AssetStateName = 'Active' and ShirtSizeName = 'Large'
GROUP BY WI.DateKey
Order By WI.DateKey
{sql}

h6. Alternate Project Burndown Query
{code:sql}SELECT  Date,
        Project.ProjectName,
        SUM(Workitem.Estimate) AS [Open Estimate]
FROM    Tree.ProjectParent AS T,
        Dim.Project,
        Fact.Workitem
        INNER JOIN Dim.Date ON Workitem.DateKey = Date.DateKey
        INNER JOIN Dim.AssetState ON Workitem.PrimaryAssetStateKey = AssetState.AssetStateKey
where   T.AncestorKey = Project.ProjectKey
        AND T.DescendantKey = Workitem.ProjectKey
        AND AssetStateName = 'Active'
GROUP BY Date,
        Project.ProjectName

 

Create an Epic Burndown

Here's an example of how to get open estimates for an epic:

SELECT D.Date, SUM(WI.Estimate) [Open Estimate]
FROM Fact.PrimaryWorkitem WI
    JOIN Tree.EpicParent ET ON ET.DescendantKey = WI.EpicKey
    JOIN Dim.Epic E ON E.EpicKey = ET.AncestorKey
    JOIN Dim.AssetState A ON A.AssetStateKey = WI.PrimaryAssetStateKey
    JOIN Dim.Date D ON D.DateKey = WI.DateKey
WHERE A.AssetStateName = 'Active' AND E.EpicName = 'Order Check'
GROUP BY D.Date
ORDER BY D.Date

 

Create a Goal Burndown

SELECT D.DateKey, SUM(Estimate) AS [Open Estimate]
FROM Fact.Workitem WI
    JOIN Dim.Date D ON WI.DateKey = D.DateKey
    JOIN Dim.AssetState A ON WI.PrimaryAssetStateKey = A.AssetStateKey
    JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
    JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
    JOIN Bridge.PrimaryWorkitemGoal PWIG ON WI.PrimaryWorkitemKey = PWIG.PrimaryWorkitemKey AND WI.DateKey = PWIG.DateKey
    JOIN Dim.Goal G ON PWIG.GoalKey = G.GoalKey
WHERE AssetStateName = 'Active'
    and ProjectName = 'Call Center'
    and GoalName = 'Increase Customer Satisf. by 10%'
GROUP BY D.DateKey
ORDER BY D.DateKey

 

Cumulative Flow

select DateKey, WorkitemStatusName,  sum(Estimate) Estimate
from Fact.Workitem WI
    join Dim.WorkitemStatus S on WI.WorkitemStatusKey=S.WorkitemStatusKey
group by DateKey, WorkitemStatusName
ORDER BY DateKey

 

Using Custom Measures

The following query shows Business Value being added over time.

SELECT D.Date, SUM(E.PrimaryWorkitemCustom_BusinessValue) [Business Value]
FROM Fact.Workitem E
    join Tree.ProjectParent ET ON ET.DescendantKey = E.ProjectKey
    join Dim.Project ED ON ED.ProjectKey = ET.AncestorKey
    join Dim.AssetState A ON A.AssetStateKey = E.PrimaryAssetStateKey
    join Dim.Date D ON D.DateKey = E.DateKey
WHERE A.AssetStateName = 'Closed' AND ED.ProjectName = 'Call Center'
GROUP BY D.Date
ORDER BY D.Date

 

Theme Roadmap

declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

select ThemeName, IterationName, sum(Estimate) Estimate
from Fact.Workitem WI
    join Dim.Iteration I on I.IterationKey=WI.IterationKey
    join Dim.Theme T on T.ThemeKey=WI.ThemeKey
where WI.DateKey=case when I.IterationEndDateKey < @lastDateKey then I.IterationEndDateKey else @lastDateKey end
group by ThemeName, IterationName

 

Total Estimate Trend

select DateKey, AssetStateName, sum(Estimate) Estimate
from Fact.Workitem
    join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
    join Dim.Project on Project.ProjectKey=T.AncestorKey
    join Dim.AssetState on AssetState.AssetStateKey=Workitem.PrimaryAssetStateKey
where  ProjectName='Release 1.0'
group by DateKey, AssetStateName
order by DateKey

 

Total Estimate Trend for a Team

select WI.DateKey, sum(Estimate) as [Open Estimate]
from Fact.Workitem WI
    join Dim.AssetState on Dim.AssetState.AssetStateKey = WI.PrimaryAssetStateKey
    join Dim.Team on Team.TeamKey=WI.TeamKey
where AssetStateName = 'Active' and TeamName='Team A'
group by DateKey
order by DateKey

 

Estimate Trend for a MemberGroup

SELECT Date,  SUM(Estimate) AS [Open Estimate]
FROM Fact.Workitem WI
    JOIN Dim.Date D ON WI.DateKey = D.DateKey
    JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
    JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
    JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
    JOIN Bridge.WorkitemOwner O ON O.WorkitemKey = WI.WorkitemKey AND O.DateKey = WI.DateKey
    join Bridge.MemberGroup BMG on BMG.MemberKey = O.MemberKey
    join Dim.MemberGroup DMG on BMG.MemberGroupKey = DMG.MemberGroupKey
WHERE ProjectName = 'Call Center'
    and AssetStateName = 'Active'
    and MemberGroupName = 'Local Developers'
GROUP BY Date
ORDER BY Date

 

Estimate trend for a Member

SELECT  D.DateKey, SUM(Estimate) AS [Open Estimate]
FROM Fact.Workitem WI
    JOIN Dim.Date D ON WI.DateKey = D.DateKey
    JOIN Dim.AssetState S ON WI.PrimaryAssetStateKey = S.AssetStateKey
    JOIN Dim.AssetType AT ON WI.AssetTypeKey = AT.AssetTypeKey
    JOIN Tree.ProjectParent PP on PP.DescendantKey=WI.ProjectKey
    JOIN Dim.Project P on P.ProjectKey=PP.AncestorKey
    JOIN Bridge.WorkitemOwner O ON WI.WorkitemKey = O.WorkitemKey AND WI.DateKey = O.DateKey
    JOIN Dim.Member M on O.MemberKey = M.MemberKey
WHERE ProjectName = 'Release 1.0'
    and AssetStateName = 'Active'
    and AssetTypeName in ('Story', 'Defect')
    and MemberName = 'Andre Agile'
GROUP BY D.DateKey
ORDER BY D.DateKey

 

Velocity

declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

select IterationName, sum(Estimate) Estimate
from Dim.Iteration I
    join Dim.Date D on D.DateKey=I.IterationEndDateKey
    join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
    join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
    join Dim.Project on Project.ProjectKey=T.AncestorKey
where ProjectName='Call Center'
group by IterationName

 

Velocity by Priority

declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

select IterationName, WorkitemPriorityName, sum(Estimate) Estimate
from Dim.Iteration I
    join Dim.Date D on D.DateKey=I.IterationEndDateKey
    join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
    join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
    join Dim.Project on Project.ProjectKey=T.AncestorKey
    join Dim.WorkitemPriority on WorkitemPriority.WorkitemPriorityKey=Workitem.WorkitemPriorityKey
where ProjectName='Call Center'
group by IterationName, WorkitemPriorityName

 

Velocity by Project

declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

select IterationName, Project.ProjectName, sum(Estimate) Estimate
from Dim.Iteration I
    join Dim.Date D on D.DateKey=I.IterationEndDateKey
    join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
    join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
    join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey
    join Dim.Project Project on Project.ProjectKey=Workitem.ProjectKey
where RootProject.ProjectName='Company'
group by IterationName, Project.ProjectName

 

Velocity by Status

declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

select IterationName, WorkitemStatusName, sum(Estimate) Estimate
from Dim.Iteration I
    join Dim.Date D on D.DateKey=I.IterationEndDateKey
    join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
    join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
    join Dim.Project on Project.ProjectKey=T.AncestorKey
    join Dim.WorkitemStatus on WorkitemStatus.WorkitemStatusKey=Workitem.WorkitemStatusKey
where ProjectName='Call Center'
group by IterationName, WorkitemStatusName

 

Velocity by Story Complexity

declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

select IterationName, StoryRiskName, sum(Estimate) Estimate
from Dim.Iteration I
    join Dim.Date D on D.DateKey=I.IterationEndDateKey
    join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
    join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
    join Dim.Project on Project.ProjectKey=T.AncestorKey
    join Dim.StoryRisk on StoryRisk.StoryRiskKey=Workitem.StoryRiskKey
    join Dim.AssetType on Dim.AssetType.AssetTypeKey = Workitem.AssetTypeKey
where ProjectName='Call Center' and AssetTypeName = 'Story'
group by IterationName, StoryRiskName

 

Velocity by Story Type

declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

select IterationName, StoryTypeName, sum(Estimate) Estimate
from Dim.Iteration I
    join Dim.Date D on D.DateKey=I.IterationEndDateKey
    join Fact.Workitem Workitem on Workitem.IterationKey=I.IterationKey and Workitem.DateKey=case when D.DateKey < @lastDateKey then D.DateKey else @lastDateKey end
    join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
    join Dim.Project on Project.ProjectKey=T.AncestorKey
    join Dim.StoryType on StoryType.StoryTypeKey=Workitem.StoryTypeKey
    join Dim.AssetType on Dim.AssetType.AssetTypeKey = Workitem.AssetTypeKey
where ProjectName='Call Center' and AssetTypeName = 'Story'
group by IterationName, StoryTypeName

 

Details Roadmap

declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

select P.ProjectName, IterationName, PrimaryWorkitemName, Estimate
from Fact.Workitem WI
    join Dim.PrimaryWorkitem PWI on PWI.PrimaryWorkitemKey=WI.WorkitemKey
    join Dim.Project P on P.ProjectKey=WI.ProjectKey
    join Dim.Iteration I on I.IterationKey=WI.IterationKey
    join Tree.ProjectParent PP on PP.DescendantKey=WI.ProjectKey
    join Dim.Project RP on RP.ProjectKey=PP.AncestorKey
where
    WI.DateKey=@lastDateKey and RP.ProjectName='Company'

 

Project Roadmap

declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem

select ProjectName, IterationName, sum(Estimate) Estimate
from Fact.Workitem WI
    join Dim.Iteration I on I.IterationKey=WI.IterationKey
    join Dim.Project P on P.ProjectKey=WI.ProjectKey
where WI.DateKey=case when IterationEndDateKey < @lastDateKey then IterationEndDateKey else @lastDateKey end
group by ProjectName, IterationName

 

Parking Lot Report

declare @lastDateKey int; select @lastDateKey=max(DateKey) from Fact.Workitem
declare @project nvarchar(4000); select @project='Release 1.0'

select ThemeArea, ThemeName, StoryCount, DefectCount, ActiveEstimate, ClosedEstimate
from Dim.Theme
left join (
    select ThemeKey, Story StoryCount, Defect DefectCount
    from (
        select ThemeKey, AssetTypeName
        from Fact.Workitem
        join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
        join Dim.Project on Project.ProjectKey=T.AncestorKey
        join Dim.AssetType on AssetType.AssetTypeKey=Workitem.AssetTypeKey
        where Workitem.DateKey=@lastDateKey and ProjectName=@project
    ) X
    pivot (count(AssetTypeName) for AssetTypeName in (Story, Defect)) pvt
) Counts on Counts.ThemeKey=Theme.ThemeKey
left join (
    select ThemeKey, Active ActiveEstimate, Closed ClosedEstimate
    from (
        select ThemeKey, AssetStateName, Estimate
        from Fact.Workitem
        join Tree.ProjectParent T on T.DescendantKey=Workitem.ProjectKey
        join Dim.Project on Project.ProjectKey=T.AncestorKey
        join Dim.AssetState on AssetState.AssetStateKey=Workitem.PrimaryAssetStateKey
        where Workitem.DateKey=@lastDateKey and ProjectName=@project
    ) X
    pivot (sum(Estimate) for AssetStateName in (Active, Closed)) pvt
) Estimates on Estimates.ThemeKey=Theme.ThemeKey
  • Was this article helpful?