Thanks!
==========================================
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 thechange 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 “completed 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.
oIf we add a field we would then have to create some function to add the “current week” hours to the “Completed 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 “Completed Work” field into another table.
oI 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"
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 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
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.
SELECT
NON
EMPTY {
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
} ON COLUMNS,
NON
EMPTY (
[Assigned To].[Person].[Person]
) ON ROWS
FROM [Team System]
WHERE
(
[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
I have similar problem now. I have found some solution, but I`m not sure that is optimal or perfectly correct. Nevertheless this solution works.
WITH
MEMBER [Measures].[Completed Work by WI on dt1] AS (
[Assigned To].[Person].
CurrentMember, [Work Item].[System_Id].
CurrentMember, [Date].[Year Week Date].[Date].&[20060105],
[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].[Date].&[20060331],
[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
{
[Measures].[Completed Work]
}
ON
COLUMNS, NON
EMPTY {
[Assigned To].[Person].[Person]
}
ON
ROWS FROM
[Team System]
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, [Date].[Year Week Date].[Date].&[2006-04-23T00: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].[Date].&[2006-04-29T00:00:00],
[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 {
([Assigned To].[Person].[Person],[Work Item].[System_Title].[System_Title])
}
ON
ROWS FROM
[Team System]
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, [Date].[Year Week Date].[Date].&[2006-04-23T00: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].[Date].&[2006-04-29T00:00:00],
[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]
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 (
[Date].[Year Month Date].[Date].&[20051226],
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER
[Measures].[Completed Work On Period End] AS (
[Date].[Year Month Date].[Date].&[20051227],
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER
[Measures].[Completed Work] AS (
[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]
)
SELECT
NON
EMPTY (
FILTER (
NONEMPTYCROSSJOIN (
[Team Project].[Team Project].[Team Project],
[Assigned To].[Person].[Person],
[Work Item].[System_Id].[System_Id],
[Work Item].[System_Title].[System_Title],
[Work Item].[System_WorkItemType].[System_WorkItemType],
[Measures].[Current Work Item Count],
5
),
[Measures].[Completed Work] > 0
)
)
ON
ROWS, NON
EMPTY {
[Measures].[Work Item Url],
[Measures].[Completed Work]
}
ON
COLUMNS FROM
[Team System]
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
wxt at 2007-9-9 >
I am asking for your help solving a problem.I am very new to MDX and OLAP in general.
Here is my statement of the problem:Changing ownership of Bugs in Team System results in incorrect reporting of Completed Work.
I have crafted the following MDX , which works fine if Ownership of a Bug or Work Item is not changed.
=================================================================================
WITH
MEMBER [Measures].[Completed Work On Period Start] AS
(
(STRTOMember(@prmStartDate)),
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed Work On Period End] AS
(
(STRTOMember(@prmEndDate)),
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed_Work] AS
(
[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]
)
SELECT NON EMPTY
(
FILTER
(
NONEMPTYCROSSJOIN
(
[Assigned To].[Person].[Person],
[Work Item].[System_Id].[System_Id],
[Work Item].[System_Title].[System_Title],
[Work Item].[System_WorkItemType].[System_WorkItemType],
[Measures].[Current Work Item Count],
4
),
[Measures].[Completed_Work] > 0
)
)
ON ROWS,
NON EMPTY
{
[Measures].[Work Item Url],
[Measures].[Completed_Work]
}
ON COLUMNS
FROM
[Team System]
=================================================================================
The problem arises when anyone changes ownership of a bug (or work item).
I have used [Changed By].[Person].[Person] and[Assigned To].[Person].[Person] as the first element in the NONEMPTYCROSSJOIN, and neither return the proper result.
BTW: I know that you prefer we not use the NONEMPTYCROSSJOIN, but I am unsure how to replace it.
Thanks in advance.
