This project is read-only.

Aggregation Calculation Error for Tasks that are moved between Team Sprint.


In my case,
  1. I have one Release with several Sprint underneath.
  2. Current Sprint contains 3 different Team Sprint underneath.
  3. All the Product Backlog Items (PBIs) are assigned to the sprint
  4. All the Sprint Backlog Tasks (SBTs) are assigned to different Team Sprints against current sprint.
In the current scenario, while planning for work distribution between different team, I had to move few SBTs from one team into another team. This causes issue with calculating Sprint Burndown Chart as now it include given SBT in both the Team Sprint instead of the one which is the current Team Sprint to which it’s being assigned. I think this is the cause of the issue that I’m experiencing with Sprint Burndown Chart.

See the attached document which explains the issue in greater details.

file attachments


AndreasEller wrote Feb 29, 2012 at 10:56 AM

I encountered the same problem and was a bit shocked that this issue is marked with "Impact Low" because it definitely makes the Sprint Burndown useless. So I decided to fix it myself.

The problem is the report's MDX query that doesn't take into account when a work item is moved out of the sprint. So here's my workaround to make the query work:
  1. adapt the WorkItemList so it doesn't limit the result on the selected iterations yet
    WITH WorkItemList(WorkitemSK, HistoryDate, System_ID ,System_Rev, System_State, IterationSK)
    SELECT WorkitemSK, CONVERT(date,dwi.Scrum_v3_HistoryDate,112) AS HistoryDate, dwi.System_Id, dwi.System_Rev, System_State, dwi.IterationSK
    FROM dbo.DimWorkItem dwi
    WHERE dwi.TeamProjectSK = @vTeamProjectID
    AND dwi.System_WorkItemType = 'Sprint Backlog Task'
  2. create a new query that does the iteration filtering AFTER the creation of "WorkItemLastDayChange"
    WorkItemLastDayChange_Filtered (HistoryDate, System_ID, System_Rev)
    SELECT d.HistoryDate
    , wildc.System_ID AS System_ID
    , wildc.System_Rev AS System_Rev
    FROM @vDates d
    LEFT OUTER JOIN WorkItemLastDayChange wildc
    ON d.HistoryDate = wildc.HistoryDate
    LEFT OUTER JOIN dbo.DimWorkItem dwi
    ON wildc.System_ID = dwi.System_ID
    AND wildc.System_Rev = dwi.System_Rev
    INNER JOIN @vIterationIDs i
    ON dwi.IterationSK = i.IterationIDs
    GROUP BY d.HistoryDate, wildc.System_ID, wildc.System_Rev
  3. use the filtered result for the final burndown data calculation
    INSERT INTO @vBurnDownData (HistoryDate, WorkRemaing)
    SELECT d.HistoryDate
    , SUM(ISNULL(fwih.Scrum_v3_WorkRemaining,0)) AS WorkRemaining
    FROM @vDates d
    LEFT OUTER JOIN WorkItemLastDayChange_Filtered wildc
    ON d.HistoryDate = wildc.HistoryDate
That's it. Now the burndown should look as expected.

Attached you find the complete query for easy copy/paste into your installed report.
  • Andreas

mlapointe wrote Mar 19, 2012 at 8:00 PM

Thanks for your fix. It saved me a lot of work fixing up the report query.
I agree with your statement about this bug not having "Low" impact. After putting the fix in and re- running the burndown charts they look dramatically different now that the real values are showing on the chart.

You wouldn't happen to have the fixed up query for the Sprint Burn Down Day View would you? The query in that report suffers from the same bug. Clicking on a data point launches that report showing the items for that day.
  • Mike

AndreasEller wrote Mar 21, 2012 at 4:01 PM

I'm glad my fix had some use to you. Unfortunately, we don't use the Sprint Burn Down Day View, so I have no fix at hand at the moment.
  • Andreas