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

FC High Level Summary of Work.rdl

Comments (3)

  1. Kes veel ei tea, siis Workitem Tracking vahendite meeskond Visual Studio Team System -i arendusmeeskonnas

  2. A good read: Applying Value Up at Microsoft by Sam Guckenheimer (also available as 60-minute-webcast

  3. Part of my job is evangelizing Team Foundation Server adoption within Microsoft. A colleague recently