TFS Reporting Cube Burndown Chart Customization - Filter Tasks only linked to a User Story

424 views Asked by At

I'm trying to customize TFS 2013 SSRS Burndown chart. While customizing I realized that Burndown chart doesnt only shows Tasks linked to a User Story but it also shows Tasks linked to a Bug too.

How can I change this MDX query that burndown chart uses, so that it excludes Tasks linked to a Bug ?

WITH
    SET [Date Range] AS
        Filter(
            [Date].[Date].[Date],
            [Date].[Date].CurrentMember.Member_Value >= CDate(@StartDateParam) AND
            [Date].[Date].CurrentMember.Member_Value <= CDate(@EndDateParam)
        )
    MEMBER [Measures].[DateValue] AS
        [Date].[Date].CurrentMember.Member_Value

    MEMBER [Measures].[RemainingWorkLine] AS
        LinRegPoint(
            Rank([Date].[Date].CurrentMember, [Date Range]),
            {[Date Range]},
            [Measures].[Microsoft_VSTS_Scheduling_RemainingWork],
            Rank([Date].[Date].CurrentMember, [Date Range])
        )

    MEMBER [Measures].[CountLine] AS
        LinRegPoint(
            Rank([Date].[Date].CurrentMember, [Date Range]),
            {[Date Range]},
            [Measures].[Work Item Count],
            Rank([Date].[Date].CurrentMember, [Date Range])
        )

SELECT
{
    [Measures].[DateValue],
    [Measures].[Work Item Count],
    [Measures].[Microsoft_VSTS_Scheduling_RemainingWork],
    [Measures].[Microsoft_VSTS_Scheduling_CompletedWork],
    [Measures].[RemainingWorkLine],
    [Measures].[CountLine]

} ON COLUMNS,
{
    CrossJoin(
        [Date Range],
        EXCEPT([Work Item].[System_State].[All].Children, StrToMember("[Work Item].[System_State].&[" + @RemovedName + "]")) 
    )
} ON ROWS
FROM
(
    SELECT
        CrossJoin(
                                                [Work Item Linked].[System_WorkItemType].&[User Story],
            StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"),
                                                StrToSet(@AreaParam),
            StrToSet(@StateParam),          
            StrToSet(@IterationParam),
            Except(
                Descendants(StrToSet(@WorkItemTypeParam)),
                [Work Item].[System_WorkItemType].[All] + StrToSet(@WorkItemsToExclude)
            )
        ) ON COLUMNS
    FROM [Team System]
)
0

There are 0 answers