DougInGeorgia

The table of data at the bottom of the Project Velocity report in Agile process templete is not constrained gy the start and end date parameters of the report. The chart, however IS constrained by the start and end date parameters. This behaviour is somewhat unexpected.

Users expect all data displayed on the report to be contrained by the report parameters, not only some of the data.

Here is how I tested: Set start and end date parameters for the Project Velocity report and observe the # of Bug Resolved Transitions. Execute the report again, with a hughe difference between the state and end dates. In my experience, the number of Bug Resolved Transitions is the same in both instances, even though bugs were resolved.

I was cusious, so I examined the dataset (dsWorkItemHistoryTable), and there is no "slice" for dates.

BTW: What would I add the the WHERE clause to constrain dsWorkItemHistoryTable to show only transitions that ocurred between the start and end dates

----------------------------Pasting currenty query for dsWorkItemHistoryTable--------------------------------------

WITH

MEMBER [Measures].[Iteration Path] AS

Iif([Iteration].[Parent_ID].CurrentMember IS [Iteration].[Parent_ID].[All],

null,

([Iteration].[Parent_ID].CurrentMember.Parent, [Measures].[Iteration Path]) + "\" + [Iteration].[Parent_ID].CurrentMember.Member_Value

)

SELECT

{

[Measures].[State Change Count],

[Measures].[Iteration Path]

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

StrToMember("[Iteration].[Parent_ID].[" + @Project + "]").Children,

[Work Item].[System_WorkItemType].[System_WorkItemType],

[Work Item].[Microsoft_VSTS_Common_Discipline].[Microsoft_VSTS_Common_Discipline],

{[Work Item].[System_State].[System_State].[Resolved],

[Work Item].[System_State].[System_State].[Closed]},

[Measures].[State Change Count],

4

) ON ROWS

FROM [Team System]

WHERE

(

STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),

STRTOSET(@AreaParam)

)



Re: Team Foundation Server - Reporting & Warehouse Table in Project Velocity Is Not Constrained By the Parameters

Othmane Rahmouni-MSFT

I searched our bug history and I found a couple of bugs around this which were resolved "by design"; the report was meant to have this behavior in order to enable report users to compare Work Item transitions across iterations. The parameters in the reports then enable you to look at specific iterations and more focused dates to understand the trend of work item transitions which you might want to focus on.

If you would like to change the behavior of the table so it is filtered by date, add two new query parameters(StartDateParam & EndDateParam) to the dsWorkItemHistoryTable dataset and use this updated query:

WITH

MEMBER [Measures].[Iteration Path] AS

Iif([Iteration].[Parent_ID].CurrentMember IS [Iteration].[Parent_ID].[All],

null,

([Iteration].[Parent_ID].CurrentMember.Parent,[Measures].[Iteration Path]) + "\" +

Iif([Iteration].[Parent_ID].CurrentMember.UniqueName =

[Iteration].[Parent_ID].CurrentMember.Parent.DataMember.UniqueName,

"(" + [Iteration].[Parent_ID].CurrentMember.Member_Value + ")",

[Iteration].[Parent_ID].CurrentMember.Member_Value

)

)

SELECT

{

[Measures].[State Change Count],

[Measures].[Iteration Path]

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

StrToMember("[Iteration].[Parent_ID].[" + @Project + "]").Children,

[Work Item].[System_WorkItemType].[System_WorkItemType],

[Work Item].[Microsoft_VSTS_Common_Discipline].[Microsoft_VSTS_Common_Discipline],

{StrToMember("[Work Item].[System_State].[System_State].[" + @Resolved + "]"),

StrToMember("[Work Item].[System_State].[System_State].[" + @Closed + "]")}, Filter(

[Date].[Date].[Date],

CDate([Date].[Date].CurrentMember.Member_key) >= CDate(@StartDateParam) AND

CDate([Date].[Date].CurrentMember.Member_key) <= CDate(@EndDateParam)

),

[Measures].[State Change Count],

5

) ON ROWS

FROM [Team System]

WHERE

(

STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),

STRTOSET(@AreaParam)

)

Have a great day!

Thanks,

Othmane