Removing weekends from the TFS Sprint Burndown report

This blog describes how to remove weekends from the Sprint Burndown report in TFS.

Essentially:

Open the report in SQL Server 2012 Report Builder. Save As a new report filename in order to keep the original intact!

Right click outside of the white report canvas and select Report Properties.
Select the Code tab item.

Add the following function:

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

Click on the chart, Select Work_Item_Count from the Values fact table and edit the Expression.
Change it 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))
)

Now the today line is out as it is not taking into account the weekends.
Turn on the Properties Tool Window if it is not already.
Select View menu tab and tick the Properties checkbox.
Click on the date axis.
In the Properties tool window find the Striplines property.
Edit the collection and the IntervalOffset property.
Enter the following value:

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

Save the report back to the server.