==========================================

Update: Please see the post dated 9/28/2006 for a new request for help.

=========================================

I have a need to report developer hours by work item by week.

I am attempting to use the warehouse filed "Current Work Item Microsoft_VSTS_Scheduling_CompletedWork" and add a "Date Week" perspective to it, but obviously that will only return the value of the "Completed Work" field at the end of the week (and is actually a sum of all Completed Work thus far.).  I need to report the change in completed work by developer by work tiem.

I attempted to enter a formula in the report using Report Designer to subtract the current  value of "Completed Work" from the "history" of "Completed Work", but Report Designer's formula edit won't let me use a value from another perspective.

I see several possible solutions:

·        A formula that calculates and displays the weekly difference in ??ompleted work??  But I ran into the problem with the formula editorI mentioned above. 

·        Add a field to the work item definition to hold hours worked in the current week.

o       If we add a field we would then have to create some function to add the ??urrent week??hours to the ??ompleted Work??field.  We would also have to ensure this field appears blank to the user at the beginning of each week.

·        Write a database procedure that runs weekly that extracts the difference in the ??ompleted Work??field into another table.

o       I want to avoid this, as it seems we are ignoring the capability of the data warehouse cubes provided by Team Foundation Server.

I am using SQL Server 2005 Standard Edition.

Hi Doug,

To capture the totals as they appeared at a point in time, you should use the "Cumulative" measures. For instance, "Cumulative Count", "Cumulative Remaining Work" and "Cumulative Completed Work". (If you are using SQL STD, the names for these measures will be the reference names, as you have describe, i.e. Cumulative Microsoft_VSTS_Scheduling_CompletedWork).

When using the cumulative measures, and "slicing" by the date dimension, you will see totals the way that they appeard at the end of the time period you are using. For example, in report designer, if you put "Date.Year Month Date" in the filter section of your query, and set it to, say, "Jan, Feb, March 2006", and then, in the data section of the query if you drag out the "Year Week Date" hierarchy and the "Cumulative Completed Work" measure, you will see results like:

Week Of Jan 1 100

Week of Jan 7 200

etc

You can add additional filters, like the current iteration, work item types, area paths, etc to the filter section to constrain the values, or add additional dimensions to the body of the query to break the totals down further. For instance, if you added "Priority" to the data section, then you'll see the total Remaining Work for work items of each priority at the end of each week.

Hope that helps,

tom

Tom:

Thanks for the reply.  I stilll have had no success.

I created a new report based on the tfsOlapReportDS.

I added the following fields:

Changed By

Date Week

A new formula field I created (CurrentWork Item Microsoft Scheduling Completed Work with a filter of "Date Week = March 26").

Current Work Item Microsoft VSTS Scheduling Completed Work with no filter.

What I expected was for the formula field to hold the value of the Completed Work as of the the filter date.  However, it has a null value, even though a value had been entered in the "Completed" of some work items for the week ending March 26.  

What am I missing?  Possibly I can send a copy of my .rdl file for further assistance.

I also have another concern:  I want to replace the hard coded filter with a relational reference to "previous week"

Doug,

The Current Work Item measures would only give you the latest value as of now. If you are trying to get the point in time completed work, you should use the Cumulative Completed Work measure under Work Item History.

As for your second question, you can use the PrevMember function in MDX to get the previous week. For example:

[Date].[Year Week Date].[Week].CurrentMember.PrevMember

Would give you the week prior to the current week.

-Jim

For some reason Business Intelligence Development Studion did not install with SQL Server 2005 when we set up Team Foundation Server, do I am trying to solve this without the benefit of customizing the default TFS models.

The following query works, with one problem.... there might NOT be a row for the item in WorkItemsWere table, which fouls up my soltion... Any thoughts>>

use tfsworkitemtracking

select ID,title,[Changed by], CurrValue, isnull(PrevValue, 0) as PrevValue from (

select ID,title, [Changed by],fld10021 as CurrValue,

(

select max(fld10021)

FROM WorkItemswere

where DATEPART(week, [Changed Date]) = 14

and id = WorkItemsare.ID

)as PrevValue

from WorkItemsare

where fld10021 > 0

) as mytable

Running queries against the operational stores for reporting purposes is not recommended due to the perf impacts they potientially have on other Tfs operations. The relational warehouse and cube does have the necessary info to answer your question. Here's a sample MDX query against the cube to return the completed work for people as of the previous week of the week of 4/9, which is the week of 4/2.

NON EMPTY

{

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

} ON COLUMNS,

NON EMPTY

(

[Assigned To].[Person].[Person]

) ON ROWS

FROM [Team System]

(

[Date].[Year Week Date].[Week].&[2006-04-09T00:00:00].PrevMember

)

Jimmy:

Thanks for your reply. What I actually am trying to get is the change in CompletedWork form one week to the next. It looks like the MDX query posted returns the value of CompletedWork as of a particular week. I am trying to get the change in CompletedWork.

Thanks again for your help.

Doug

Would adding a "period over period growth" calculation to the Team System cube be a solution?

I am following the instructions found at ms-http://msdn2.microsoft.com/en-us/library/ms169748(SQL.90).aspx

I am new to warehousing.

Thanks

WITH

MEMBER [Measures].[Completed Work by WI on dt1] AS

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,

MEMBER [Measures].[Completed Work by WI on dt2] AS

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,

MEMBER [Measures].[Completed Work] AS

ON COLUMNS,

NON EMPTY

ON ROWS

FROM

Stanislav:

Thanks! My date time format is a little different. I have modified your query to include the task title. All I have to do now is somehow add a sum by [Person].

This is all new to me, so thanks for your help!

WITH

MEMBER [Measures].[Completed Work by WI on dt1] AS

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,

MEMBER [Measures].[Completed Work by WI on dt2] AS

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,

MEMBER [Measures].[Completed Work] AS

NON EMPTY

ON COLUMNS,

NON EMPTY

ON ROWS

FROM

Here is my improved code.. it includes the task ID and filters out results that have Work Completed of 0

I hope this is of use to someone.

Here to help, Doug

WITH

MEMBER [Measures].[Completed Work by WI on dt1] AS

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,

MEMBER [Measures].[Completed Work by WI on dt2] AS

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,

MEMBER [Measures].[Completed Work] AS

NON empty

ON COLUMNS,

NON EMPTY

Filter(

ON ROWS

FROM

Thanks for "Filter" idea...

I work on almost same task in my company, and i have some progress.

I had improved code sample, that i posted before. My final version looks like code below. I have added filter to it, and I must note that my date format differs from yours.

WITH

MEMBER [Measures].[Completed Work On Period Start] AS

MEMBER [Measures].[Completed Work On Period End] AS

MEMBER [Measures].[Completed Work] AS

NON EMPTY

FILTER

NONEMPTYCROSSJOIN

ON ROWS,

NON EMPTY

ON COLUMNS

FROM

I posted a question on another Microsoft forum asking how to filter using parameters for the dates... please see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=396980&SiteID=1

Edited: That post now contains a code for a solution that takes input parameters and passes them to the query.  I still have a few issues to solve, but at least I now have a report that let's the user specify the period of the report.

Thanks Stanislav and all others who chimed in!

 Doug

I am trying to create a similar report for my company.

This report does not satisfy me, because one task may be done by more than 1 person. And the completed hours are totally associated with the last assigned to person.

I have a task that was assigned to user1 and he spent 5 hours on it, then it was assigned to user2 and he spend 3 hours on it. The report grants 8 hours to user2.

Besides I would rather associate the number of hours to the user changing the completed hours number. So if user1 changes the number of completed hours form 0 to 5 then he is granted 5 hours despite later changes or the field assigned to. It happens people forget to update that field.

I suspect the information about each change in completed hours does not exist in the cube, but I hope some Microsoft guy will post here and answer this question.

WojtekS

ps. See also my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=433433&SiteID=1

I am asking for your help solving a problem.

DougInGeorgia wrote:

I am asking for your help solving a problem. DougInGeorgia Can you tell us what you expect the result to be and what you are seeing - before and after you change the work item ownership. Ameya Bhatawdekar I expected the report to show increases in CompletedWork for the specified time period by the person who added hours. The actual result is that the report improperly shows all CompletedWork under the owner (Assigned To) of the task.  Using "Changed By" Example:  A bug is created and assigned to Developer A.  Developer A fixes the bug, increases the value if "Completed Work" from 0 to 3, and changes "Assigned To" to the tester.  Running the report at this time will show 3 hours of "Completed Work" as being done by the tester.  My intent was to show the 3 hours under Developer A since he fixed the bug and is the person who entered the 3 hours of completed work. DougInGeorgia

reply 18

You can use google to search for other answers Enter your search terms Submit search form   Web www.bokebb.com

More Articles

·Reporting on inforamtion in work item history?

·Not all work items are displayed.

·How do i modify the filter on a report

·SQL SP1 killed some reports

·Printing Reports from within Team Explorer

·Having trouble deploying to the report server

·Report with item description

·urgent: Connection Problems

·Unplanned work report incorrect

·Using Excel pivot charts, can I filter on creation dates within t...