CustomMeasureQueries

This feature is available in Enterprise and Ultimate editions.

Custom Measure on Task

SELECT DateKey, SUM(TaskCustom_HighDetailEstimate) [HighDetailEstimate]
FROM Fact.Workitem WI
    JOIN Dim.AssetState S on WI.SecondaryAssetStateKey = S.AssetStateKey
    JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
    JOIN Dim.Iteration I ON WI.IterationKey = I.IterationKey
    JOIN Dim.SecondaryWorkitem SWI ON WI.SecondaryWorkitemKey = SWI.SecondaryWorkitemKey
WHERE IterationName='Month C 1st Half' and AssetStateName = 'Active' and SecondaryWorkitemType = 'Task' and ProjectName = 'Release 1.0'
GROUP BY DateKey
ORDER BY DateKey

Custom Measure on Primary Workitems

SELECT D.Datekey, SUM(PrimaryWorkitemCustom_BusinessValue) [Business Value]
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 A ON A.AssetStateKey = WI.PrimaryAssetStateKey
    JOIN Dim.Date D ON D.DateKey = WI.DateKey
WHERE AssetStateName = 'Closed' AND ProjectName = 'Call Center'
GROUP BY D.DateKey
ORDER BY D.DateKey