Tuesday, August 23, 2011

TFS Scrum Templates and Burndown Report and Weekends

This applies to Microsoft Visual Studio Scrum 1.0

You may notice that when you run the Burndown report with a scrum runs longer than one week your burn down will look like :
image

The horizontal line is when the sprint runs over the weekend. This means your ideal trend line is out.
This can be fixed by editing the report, select the following menu :
image
This will open the report in the editor.
The first thing we will do is remove weekend from the horizontal line. Select Category Group Properties for the Category Groups eg
image
Select the filter tab and enter the following :
Filter Expression
=Weekday(Fields!DateValue.Value,0)
Type Integer (to the right of the fx button)
Operator <
Value
6
It will look like :
image
If we run the report now you will see the weekend have gone but you will have a ‘kink’ in the ideal trend :
image
The report runs some code called Burndown, to fix this we need to alter the parameters we pass to Code.Burndown .
On the chart data select expression for Work_Item_Count eg
image
Change this to
=Code.Burndown(Sum(Fields!Remaining_Work.Value), IIF( DateDiff( DateInterval.Day,Parameters!StartDateParam.Value , Fields!DateValue.Value) > 6, DateAdd(DateInterval.Day,-2, Fields!DateValue.Value )  ,Fields!DateValue.Value)  ,  DateAdd(DateInterval.Day,-2, Parameters!EndDateParam.Value )  )
What we have done is subtracted 2 from the end date and we subtract 2 from the Date on this axis if its greater then 6.
I am sure you could just edit this function on the server but the above works.
You report will now look like :
image
Posted by at
Labels: ,

19 comments:

  1. AnonymousOctober 19, 2011 at 11:28 AM

    Nice post, one remark: this solution only works for a two-week sprint.

    ReplyDelete
  2. AnonymousOctober 19, 2011 at 11:42 AM

    Yep, I agree but you could change it to make it work for how many weeks your sprint runs.

    ReplyDelete
  3. Graeme FosterJanuary 9, 2012 at 6:08 AM

    Cheers Steve,

    FYI here's some code to make it work with arbitrary length sprints. I've just created a function which works out how many weekend days have passed and uses that to offset, instead of the hard-coded 2.

    Add this function to the Code for the report:

    ' Define other methods and classes here
    Function NumberOfDaysToAdjustForWeekends(ByVal valNow as Date, ByVal startOfSprint as Date) AS Int32

    Dim valCurrent As Date = startOfSprint
    Dim weekendDaysPassed As Int32 = 0

    Do While valCurrent < valNow

    valCurrent = valCurrent.AddDays(1)
    If (valCurrent.DayOfWeek = DayOfWeek.Saturday Or valCurrent.DayOfWeek = DayOfWeek.Sunday) Then
    weekendDaysPassed = weekendDaysPassed + 1
    End If

    Loop

    Return weekendDaysPassed

    End Function

    And change the Code.Burndown code to:
    =Code.Burndown
    (
    Sum(Fields!Remaining_Work.Value),
    Fields!DateValue.Value.AddDays(-Code.NumberOfDaysToAdjustForWeekends(Fields!DateValue.Value, Parameters!StartDateParam.Value)), Parameters!EndDateParam.Value.AddDays(-Code.NumberOfDaysToAdjustForWeekends(Parameters!EndDateParam.Value, Parameters!StartDateParam.Value)))

    ReplyDelete
  4. Jasmin SehicMarch 28, 2012 at 4:43 AM

    Thanks Steve and Graeme. I've made further adjustments to the Sprint Burndown report as using Weekday() function was sensitive to regional settings plus also the Today line was out by number of weekend days passed so I had to adjust the IntervalOffset by NumberOfDaysToAdjustForWeekends from start to Today().

    I've uploaded the complete and working sprint burndown without weekends report below.

    http://www.crocko.com/8C0F07E2AF114FA2B1CC41F75F236F83/Sprint_Burndown.rdl

    ReplyDelete
    Replies
    1. Jamie SnellSeptember 17, 2012 at 9:58 PM

      This comment has been removed by the author.

      Delete
    2. Jamie SnellSeptember 17, 2012 at 10:08 PM

      The file is missing, would you be able to upload the file again?

      Delete
    3. AnonymousSeptember 27, 2012 at 1:56 PM

      Would be nice if Jasmin could upload the file again, i don't understand where to adjust the IntervalOffset...?

      Delete
    4. MBNovember 12, 2013 at 7:10 AM

      could u please upload the .RDL file again?

      Delete
    5. MBNovember 12, 2013 at 7:53 AM

      send me the file to bondre.mayur@gmail.com if possible.

      Delete
    6. AnonymousNovember 12, 2013 at 5:57 PM

      Any chance you can email me the rdl to tchristensen (at) sanjel.com?
      Or anyone else for that matter :)

      Delete
  5. AnonymousJune 15, 2012 at 8:16 PM

    Just had to apply this same fix in TFS 11 Beta. Graeme's custom function worked beautifully.

    ReplyDelete
  6. AnonymousJune 18, 2012 at 8:10 AM

    Dear Greame, really nice addition, works like a charm.

    ReplyDelete
  7. UnknownSeptember 7, 2012 at 12:05 AM

    Hello,
    This is exactly what I need.
    And sorry if this is a newbie question but how do you access the screens you have listed here? I am new to working with TFS bu t would like to get this change in place.

    Thanks for any help you can give.

    ReplyDelete
  8. BavoNovember 29, 2012 at 8:39 AM

    After a bit of searching I found how to fix the Today stripline hinted at by Jasmin Sehic.

    Click on the Chart Date Axis and open the StripLines property collection. This is the Today line.
    Go to the IntervalOffset property and enter the following code:

    =CountDistinct(Fields!DateValue.Value, "dsBurndown") - DateDiff("d", Today(), Max(Fields!DateValue.Value, "dsBurndown")) - Code.NumberOfDaysToAdjustForWeekends(Today(), Parameters!StartDateParam.Value)

    Getting to the screens listed here is fairly simple:
    Go to the following url on your tfs server: http://ServerName/Reports/Pages/Folder.aspx
    There you can drill down the folder structure until you find the reports you want to edit.
    Open the dropdown of the report and choose "Edit in Report Builder". This will install SQL Server Report Builder using ClickOnce if it was not installed yet.

    To access the code behind of the report, right click outside the white canvas and choose "Report Properties". In the next screen you can see the code tabpage. Just add the NumberOfDaysToAdjustForWeekends method at the bottom.

    ReplyDelete
  9. AnonymousDecember 3, 2012 at 11:51 AM

    Bavo, I'm trying to follow your post with minimal BIDS experience... are you able to explain in a little more detail, how to get to the IntervalOffset property, please?

    Do I click on the X-Axis, and select "Horizontal Axis Properties", and then what?

    I appreciate any help you can give.

    Many thanks

    ReplyDelete
    Replies
    1. naniJuly 18, 2013 at 4:15 PM

      Simple search for IntervalOffset in viewcode

      Delete
  10. aleutianeagleMarch 19, 2013 at 4:50 PM

    excellent post, many thanks! i added some details about how to use the WeekDayName() instead of WeekDay() http://usingscrum.blogspot.com/

    ReplyDelete
  11. UnknownApril 15, 2013 at 3:12 PM

    Hello

    does any one fix the problem width the "Today line" in the Scrum 2.0 Template

    thank's a lot

    ReplyDelete
  12. UnknownAugust 5, 2013 at 11:02 AM

    I wanted to also not include the weekends in the burndown, however I still wanted to include them in the graph, the following solution will adjust the burndown line to a flat line during the weekends
    To use, just replace the Burndown Function with the following code.

    Function Burndown(ByVal val As Object, ByVal valDate As Date, ByVal endDate As Date)

    If valDate < firstDate Then
    firstValue = Double.NaN
    firstDate = DateTime.MinValue
    End If

    If Double.IsNaN(firstValue) Then
    If Not val Is Nothing And val <> 0 Then
    firstValue = val
    firstDate = valDate
    numDays = WorkDays(valDate, endDate)
    Return firstValue
    End If
    Else
    Return (WorkDays(valDate, endDate) / numDays) * firstValue
    End If
    Return Nothing
    End Function

    Function WorkDays(ByVal startDate As Date, ByVal stopDate As Date)
    Dim Current As Date = startDate
    WorkDays = 0
    While (Current < stopDate)
    Select Case Current.DayOfWeek
    Case DayOfWeek.Saturday
    Case DayOfWeek.Sunday
    Case Else
    WorkDays += 1
    End Select
    Current = Current.AddDays(1)
    End While
    End Function

    ReplyDelete

Subscribe to: Post Comments (Atom)