How do I report completed work for a given week in TFS 2008?
We are working on Team Foundation Server 2008 and I am trying to find a way to communicate the change in completed work from week to week at the issue level. The MDX query below works pretty well, but I would like to get rid of the need to hardcode this last week. I've tried using prevemember and parallelperiod with no success, but I'm not an MDX expert.
WITH
MEMBER [Measures].[Completed Work by WI on dt1] AS
(
[Assigned To].[Person].CurrentMember,
[Work Item].[System_Id].CurrentMember,
[Date].[Year Week Date].[Week].&[2008-12-07T00:00:00],
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed Work by WI on dt2] AS
(
[Assigned To].[Person].CurrentMember,
[Work Item].[System_Id].CurrentMember,
[Date].[Year Week Date].CurrentMember,
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed Work] AS
[Measures].[Completed Work by WI on dt2] - [Measures].[Completed Work by WI on dt1]
SELECT
NON EMPTY
{
[Measures].[Completed Work]
}
ON COLUMNS,
NON EMPTY
{
Filter(
([Assigned To].[Person].[Person],[Work Item].[System_Id].[System_Id],[Work Item].[System_Title].[System_Title]), [Measures].[Completed Work] >0 )
}
ON ROWS
FROM [Team System]
source to share
See the report provided by Work Completed. It automatically sets one of its date fields to today minus one month.
EDIT: Just log into my work system to double check this. The report is actually called "Work Remaining". Go to the SharePoint portal that was created for your Team Project and find the list of standard reports. It will be on this list. You can export this report to a file, open it in Visual Studio and see the logic of the date field.
EDIT2: For the MDX function to get the previous week try the option: http://social.msdn.microsoft.com/Forums/en-US/tfsreporting/thread/0a656453-eaf1-47a2-a376-cb6eaec0db51
source to share
@sliderhouserules - I looked at this report and it seems like it is just entering a date and using the strtomember function. In my request, it looks like it would be the equivalent of a hard-coded date string with:
StrToMember("[Date].[Year Week Date].[Week].&[" + Format(DATEADD("d", -7, "2008-12-21"), "s") + "]")
This works great. However, I really want to eliminate the need for hard code. I tried using the Now () function instead of the hardcoded date. I haven't been able to get it to work, but even if I did, it would still mean that I would have to change the number of days to subtract to get back on the Sunday of the previous week. There seems to be an MDX function that will do the job. If not, then maybe there is a way to modify the StrToMember string to get the previous Sunday date in the appropriate format.
source to share