How to include completed hours for Bugs?

Question

We have modified the WIT for Bugs and included completed and remaining hours (as in Tasks). But we are unable to show the hours in the Burndown and BurnRate charts. All the hours are showing against the "Assigned To" person.

Some folks may suggest to create a task for each bug, but that's a duplication of efforts. Why create an exact copy of something that already exists? Someone found a bug and now it's assigned to a developer. The work is completed by developer and bug is assigned back to tester. The hours in the chart need to reflect who updated the hours and not who the bugs is currently assigned to.

Any thoughts?

Vik

Answer 1

Have you created your own process template? You need to download the process template on the TFS server, modify all areas from work item type 'bug' and down to report to be able to get this done. When you are finished, just upload the new process template to your TFS server, create a new team project and create a bug....

---

Roar Jørstad
Senior Consultant
Work: EVRY as
Blog: Notebook, trick & tips

Answer 2

Yes, I have modified the xml file for bug work item. It's quite similar to Task now. That's not the issue. The issue is by default the burndown charts use the "Assigned To" field to show hours completed. So, if the developed resolves the bug and enters 20 hours in work completed field and assigns the bug to tester. The burndown chart shows tester's name with 20 hours.

Answer 3

If you look at the dsBurndown data set in the burndown report you will see in the MDX:

SELECT
   CrossJoin(
    StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"),
    StrToSet(@AreaParam),
    StrToSet(@SprintParam),
    StrToSet(@WorkItemSystemAssignedTo),
    StrToSet(@Disciplines),
    StrToSet("[Work Item].[System_WorkItemType].[Task]"))

That last line restricts the hours included in the burndown to Task work items only. I am not an MDX expert but this seems to get hours from both Defect and Task work items:

SELECT
   CrossJoin(
    StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"),
    StrToSet(@AreaParam),
    StrToSet(@SprintParam),
    {[Work Item].[System_WorkItemType].[Defect],[Work Item].[System_WorkItemType].[Task]})

In our modified template we have Defect work items, yours may be Bug work items.

Answer 4

Hi Tim,

Mine looks a little different but it does have all the work item types (this is out of the box):

SELECT
  CrossJoin(
   StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"),
   StrToSet(@StateParam),
   StrToSet(@AreaParam),
   StrToSet(@IterationParam),
   Except(
    Descendants(StrToSet(@WorkItemTypeParam)),
    [Work Item].[System_WorkItemType].[All] + StrToSet(@WorkItemsToExclude)
   )
  ) ON COLUMNS
 FROM [Team System]
)

Answer 5

It's a burndown report.

Answer 6

And it seems the relevant dataset is "dsWorkAssignment". That's where the name of the person shows up. And it's linked to an XML query which points to "Assigned To" field. I changed it to "Resolved By" but it doesn't show any names. We may have to change that xml query to show "Assigned To" for all WIs except Bug where it should show "Resolved By".

<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[System_AssignedTo].[System_AssignedTo]" />

I am not sure how to change this xml query with if then else kinda statements....????

Answer 7

Apologies. I was looking at the burndown chart not Burndown and BurnRate. I found the report in another collection and ran it against one of my projects. I don't have the same issue. My defect work items are also modified to include estimate, completed and remaining hours.

Make sure that the same criteria is set in the dsWorkAssignment. That is the query that pulls data for assigned to table. Can you post an image of the report with Tasks only then with tasks and defects?

Answer 8

Tim,

The hours are counted and reflected. The issue is with the name. Lets say a tester tyz submitted a bug and assigned it to developer abc. The developer works on it and puts in 10 hrs (completed) and resolves the bug. He then assigns it back to tester tyz to verify and close. Now in the burndown and burnrate chart it shows 10 hrs for that project but instead of showing abc (developer's name) it shows 10 hrs against tester's name who the bug is currently assigned to. That is the root of the problem.

The bug is resolved and assigned to tester for verification. The work was completed by developer but in reports it shows as if tester completed 10 hrs for the bug.

Answer 9

Wow, I am way off base with how I understood the problem. "But we are unable to show the hours in the Burndown and BurnRate charts. All the hours are showing against the "Assigned To" person." sounded like the hours weren't showing in the chart but were in the assigned to table.

I think you are saying the hours are reflected in both but you want the hours to reflect against the developer fixing the bug rather than whoever is currently assigned (tester). Is this correct?

Answer 10

That's correct. And now that I am reading all my previous posts I think I should have explained it a little better :-(

Answer 11

Much tougher problem. It leads to some additional questions: Do the testers add their hours to the defect as well? They do in my org. If so, that further complicates it because you would then need to attribute some hours to dev and some to test.

The problem you have is you need to access history to get the assigned to value when it was in a development state. I am afraid I am not proficient enough with MDX to code that. In Pseudo-TSQL it could be done using a subquery against the history view and would be something like:

Case When System_WorkItemType = 'Defect' Then (Select Top 1 AssignedTo from WorkItemHistoryView
Where System_Id = ParentQuery.System_Id and System_State = 'Development'
Order By System_Rev DESC) Else AssignedTo End Case AS Assigned_To

Don't know how to do that in MDX.

Answer 12

Well, I am not a DBA so both MDX and SQL are equally difficult for me. For but do we really need to go in the history? Can we get it from "Resolved By" field - esp. for Bugs? And use Assigned To for the rest of WITs.

Under work assignment it shows the correct hours, just the name is incorrect so I am trying to see if I can put some logic to display the Resolved By name for Bugs and Assigned to for the rest.

Vik

Answer 13

Yes, you may be able to use Resolved By. In the report modify the first CrossJoin statement as follows:

	CrossJoin(
		[Work Item].[System_State].[All].Children,
		[Work Item].[System_WorkItemType].[System_WorkItemType].AllMembers,
		TopCount(
			NonEmpty(
				IIf([Work Item].[System_WorkItemType].CurrentMember IS [Work Item].[System_WorkItemType].&[Defect],[Work Item].[Microsoft_VSTS_Common_ResolvedBy].[Microsoft_VSTS_Common_ResolvedBy],[Work Item].[System_AssignedTo].[System_AssignedTo]).AllMembers,
				[Measures].[Work Item Count]
			),
			@TopUserCount,
			IIF(@YAxis = "hours",
				[Measures].[Microsoft_VSTS_Scheduling_RemainingWork],
				[Measures].[Work Item Count]
			)
		)
	)

This modifies the MDX to use ResolvedBy for Person when the WorkItemType is "Defect". If you use Bug then alter appropriately.

Answer 14

Looking better than before but now it's showing the same name for all work assignments. Under the burndown and burnrate chart where you see "Assignment of Work (Hours)" it's showing the same name repeatedly with different hours. The issue might be with the field "Person" in dsWorkAssignment dataset.

***dsWorkAssignment--->Person (Field source)*********************
<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[System_AssignedTo].[System_AssignedTo]" />

****************

I changed it to this ---->

<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[Microsoft_VSTS_Common_ResolvedBy].[Microsoft_VSTS_Common_ResolvedBy]" />

***********************

But we need to have a combination of Assigned To and Resolved By based on the WIT.

Answer 15

Are you editing the report XML directly? I checked the fields tab for my modified report and it shows the below XML for the Person field:

<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[System_AssignedTo].[System_AssignedTo]" />

However the MDX has the code I posted above. Can you post your query MDX?

Answer 16

MDX query for what dataset? Each dataset has it's own query. The one I posted above is from Person field in the dsWorkAssignment dataset.

Answer 17

Here are both the queries from dsBurndown and dsWorkAssignment..

### dsBurnDown full query ###############
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],
  [Work Item].[System_State].[All].Children
 )
} ON ROWS
FROM
(
 SELECT
  CrossJoin(
   StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"),
   StrToSet(@StateParam),
   StrToSet(@AreaParam),
   StrToSet(@IterationParam),
   Except(
    Descendants(StrToSet(@WorkItemTypeParam)),
    [Work Item].[System_WorkItemType].[All] + StrToSet(@WorkItemsToExclude)
   )
  ) ON COLUMNS
 FROM [Team System]
)
### dsBurnDown full query ###############

### dsWorkAssignment full query ###############

// Returns the completed and remaining work for each person with a work item
// currently assigned to them.
SELECT
{
 [Measures].[Microsoft_VSTS_Scheduling_CompletedWork],
 [Measures].[Microsoft_VSTS_Scheduling_RemainingWork],
 [Measures].[Work Item Count]
} ON COLUMNS,
{
 // Filter out people who don't currently have any work items assigned to them.
 CrossJoin(
  [Work Item].[System_State].[All].Children,
  [Work Item].[System_WorkItemType].[System_WorkItemType].AllMembers,
  TopCount(
   
   NonEmpty(
    IIf([Work Item].[System_WorkItemType].CurrentMember IS [Work Item].[System_WorkItemType].&[Bug]
    ,[Work Item].[Microsoft_VSTS_Common_ResolvedBy].[Microsoft_VSTS_Common_ResolvedBy]
    ,[Work Item].[System_AssignedTo].[System_AssignedTo]).AllMembers,
    [Measures].[Work Item Count]
    
    //IIF([Work Item].[System_WorkItemType] = "Bug",
    //[Work Item].[Microsoft_VSTS_Common_ResolvedBy].[Microsoft_VSTS_Common_ResolvedBy].AllMembers,
    //     //[Work Item].[Microsoft_VSTS_Common_ResolvedBy].AllMembers,
    //[Work Item].[System_AssignedTo].[System_AssignedTo].AllMembers),
    //[Measures].[Work Item Count]

   ),

   @TopUserCount,
   IIF(@YAxis = "hours",
    [Measures].[Microsoft_VSTS_Scheduling_RemainingWork],
    [Measures].[Work Item Count]
   )
  )
 )
  
} ON ROWS
FROM
(
 // Apply filters on the other parameters to restrict the results
 SELECT
  CrossJoin(
   StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"),
   StrToSet(@StateParam),
   StrToSet(@AreaParam),
   StrToSet(@IterationParam),
   Except(
    Descendants(StrToSet(@WorkItemTypeParam)),
    [Work Item].[System_WorkItemType].[All] + StrToSet(@WorkItemsToExclude)
   )
  ) ON COLUMNS
 FROM [Team System]
)

### dsWorkAssignment full query ###############

Answer 18

I am talking about the Query for dsWorkAssignment where the MDX is:

	CrossJoin(
		[Work Item].[System_State].[All].Children,
		[Work Item].[System_WorkItemType].[System_WorkItemType].AllMembers,
		TopCount(
			NonEmpty(
				IIf([Work Item].[System_WorkItemType].CurrentMember IS [Work Item].[System_WorkItemType].&[Defect],[Work Item].[Microsoft_VSTS_Common_ResolvedBy].[Microsoft_VSTS_Common_ResolvedBy],[Work Item].[System_AssignedTo].[System_AssignedTo]).AllMembers,
				[Measures].[Work Item Count]
			),
			@TopUserCount,
			IIF(@YAxis = "hours",
				[Measures].[Microsoft_VSTS_Scheduling_RemainingWork],
				[Measures].[Work Item Count]
			)
		)
	)

Did you add the IIf statement as shown above? I let the fields XML take care of itself.

Answer 19

The dsWorkAssignment query is pasted above (my previous post).

Answer 20

Get rid of the line:   [Work Item].[System_WorkItemType].[System_WorkItemType].AllMembers, I added that to validate the data and in comparing my working report to yours, that and the commented lines are the only differences. That still does not explain the same name showing in the assigned to table. I am unable to reproduce that in my copy of the report.

Answer 21

To reproduce, please try this - Open a bug as a tester and then resolve it and add completed hours as a developer. Once resolved assign it back to tester. Then update the cube. The hours will show up against the tester instead of the developer who put them in.

Answer 22

Removed the line but still same name mutiple times with different hours. And I think it's coming from the xml in the Person field which is pointing to Assigned to...

***dsWorkAssignment--->Person (Field source)*********************
<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[System_AssignedTo].[System_AssignedTo]" />

Answer 23

Please send your report XML to me at tim underscore pacl at dell dot com. I will take a look at what may be causing the problem. My modified report appears to be working as expected. Defect hours show up as expected. I get top 10 "Persons" listed in the table, no duplicates.

Answer 24

Thanks, I got the report. You are getting the same name in all 10 rows of the assigned to because the expression in the name column is "=First(Fields!Person.Value, "dsWorkAssignment")".

Change it back to the out-of-box formula:

=IIF(Fields!Person.Value.ToString().Length > 33, First(Fields!Person.Value).ToString().Substring(0, Math.Min(30, Fields!Person.Value.ToString().Length)) + "...", Fields!Person.Value.ToString())

That will fix it.

Answer 25

Back to where we started - now it's showing different names but not the person who put in the hours but who the work item is currently assigned to.

Answer 26

Check the Resolved By value for those Bugs. The MDX is using that value for Person when work item type is Bug. If it is not the correct value then it is a process issue, not a report problem.

Answer 27

Yep, that's how we identified the issue. All these bugs were resolved by the developer and completed hours put it and then assigned to tester to validate and close. However, in the burndown and burnrate charts the hours are showing up against the tester.

Answer 28

The IIf statement you added to the MDX returns the ResolvedBy name instead of the AssignedTo name when the work item type is Defect. You may not be seeing what you expect if a defect was resolved by a user that is not in the top 10. The default setting of @TopUserCount is 10. You can increase that number to see if the user you are expecting shows up lower in the list.

Answer 29

You mean this IIF...?

=IIF(Fields!Person.Value.ToString().Length > 33, First(Fields!Person.Value).ToString().Substring(0, Math.Min(30, Fields!Person.Value.ToString().Length)) + "...", Fields!Person.Value.ToString())

Yes, that's the out-of-the-box and I put it back. But my thinking is that has more to do with the xml in the Person's field that poitns to "Assigned To" field. As it's showing the correct hours just the incorrect name.

***dsWorkAssignment--->Person (Field source)*********************
<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[System_AssignedTo].[System_AssignedTo]" />

There are only 5 users so not exceeding the 10 count...