Skip to main content
Version: Early Access

CustomMeasureQueries

This topic explains how to use the custom measure queries on a task.

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