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