Teams WIT Tools

How Microsoft/DevDiv uses TFS - Chapter 6 (Addendum)

In my previous post, some people asked how we went about creating the report I talked about.

I asked the fellow who created this report internally. Here was his response, which I'm passing on to you. (Thanks Doug!)
NOTE: I've also attached the .RDL file he forwarded along.

being quote>

Here is the query which gets current values along with the value of completed & remaining work from some prior date (and the prior date is parameterized).   The reason for the .[All] on the custom measures which get the work from N days ago is to deal with the scenarios where title or product unit or state etc had a different value on the prior date.  In effect we are saying:  give me the value of completed & remaining work from N days ago without regard to whether the value of those other fields match the filter we are applying to the current values (e.g. current state etc).

WITH

MEMBER [Measures].[ValueOfCompletedWorkAsOfNDaysAgo] AS

(

   [Measures].[Microsoft_VSTS_Scheduling_CompletedWork],

   [Work Item].[Microsoft_DeveloperDivision_Classifications_Group].[All],

   [Work Item].[Microsoft_DeveloperDivision_Classifications_Project].[All],

   [Work Item].[System_Title].[All],

   [Work Item].[System_State].[All],

   [Work Item].[Microsoft_DeveloperDivision_Features_RiskLevel].[All],

STRTOMEMBER(@MDXDateForWorkCompletedSinceDate)

)

MEMBER [Measures].[ValueOfRemainingWorkAsOfNDaysAgo] AS

(

   [Measures].[Microsoft_VSTS_Scheduling_RemainingWork],

   [Work Item].[Microsoft_DeveloperDivision_Classifications_Group].[All],

   [Work Item].[Microsoft_DeveloperDivision_Classifications_Project].[All],

   [Work Item].[System_Title].[All],

   [Work Item].[System_State].[All],

   [Work Item].[Microsoft_DeveloperDivision_Features_RiskLevel].[All],

STRTOMEMBER(@MDXDateForWorkCompletedSinceDate)

)

MEMBER [Measures].[FeatureEndDate] AS

EXTRACT(

NonEmpty(

        [Microsoft_DeveloperDivision_Features_DateEnd].[Date].[Date] *

        [Work Item].[System_Id].CurrentMember,

        [Measures].[Current Work Item Count]

    ),

    [Microsoft_DeveloperDivision_Features_DateEnd].[Date]

).Item(0).Member_Value

SELECT

Non Empty

{

   [Measures].[FeatureEndDate],

   [Measures].[Current Work Item Microsoft_VSTS_Scheduling_CompletedWork],

   [Measures].[Current Work Item Microsoft_VSTS_Scheduling_RemainingWork],

   [Measures].[ValueOfCompletedWorkAsOfNDaysAgo],

   [Measures].[ValueOfRemainingWorkAsOfNDaysAgo]

} ON COLUMNS,

NonEmpty(

STRTOSET(@WorkItemMicrosoftDeveloperDivisionClassificationsGroup, CONSTRAINED) *

STRTOSET(@WorkItemMicrosoftDeveloperDivisionClassificationsProject, CONSTRAINED) *

    [Work Item].[System_Id].[System_Id] *

    [Work Item].[Microsoft_DeveloperDivision_Features_RiskLevel].[Microsoft_DeveloperDivision_Features_RiskLevel] *

    [Work Item].[System_Title].[System_Title],

    [Measures].[Current Work Item Count]

) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [Current Work Item]

WHERE

(

   [Work Item].[System_WorkItemType].&[Orcas Feature],

STRTOSET(@WorkItemSystemState, CONSTRAINED)

)

<end quote