Posted by: Karlo Bartels | 2008/10/31

Week numbers

Displaying correct week numbers in a table header

So today I had to develop a report which features aggregated data for a specific period in time. The underlying stored procedure returns data starting at the current week up to twelve weeks after that. I created a table in my report and defined the first column header expression as follows:

="Week " & ReportItems!txtCurrentWeek.Value

The second through the twelfth column header got this expression:

="Week " & Mid(ReportItems!txtWeekHeader[n-1].Value, 6) + 1

Works like a charm… as long as the data doesn’t span multiple years. Which, in my case, it did… What I wanted is this (note that the year 2008 has 52 weeks, whereas 2009 has 53):

What I wanted (click for a larger image)

Fortunately, reports allow you to add some ‘code-behind’, so you can add your own algorithms. I added two functions: one to determine the amount of weeks in a specific year (CountWeeksInYear) and another that returns the proper week number for my table header (GetNextWeekNumber):

Public Shared Function CountWeeksInYear(ByVal Year As Integer) As Integer
   Dim
dutchCI As New System.Globalization.CultureInfo("nl-NL")
  
Dim lastDay As New DateTime(Year, 12, 31, dutchCI.Calendar)
  
Dim week As Integer = dutchCI.Calendar.GetWeekOfYear(lastDay, System.Globalization.CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday)
  
If ((week = 53) AndAlso (((lastDay.DayOfWeek = DayOfWeek.Monday) OrElse (lastDay.DayOfWeek = DayOfWeek.Tuesday)) OrElse (lastDay.DayOfWeek = DayOfWeek.Wednesday))) Then
      Return
52
  
End If
   Return
week
End
Function
Public Shared Function GetNextWeekNumber(ByVal YearOfFirstWeek As Integer, ByVal PreviousWeek As Integer) As Integer
   Dim
weekNumber As Integer = (PreviousWeek + 1)
  
If (weekNumber > 52) Then
      Dim
yearsInWeek As Integer = CountWeeksInYear(YearOfFirstWeek)
     
If ((yearsInWeek = 53) AndAlso (weekNumber = yearsInWeek)) Then
         Return
53
     
End If
     
weekNumber = (weekNumber - yearsInWeek)
  
End If
   Return
weekNumber
End

The next step was to change the second through the twelfth column header expressions into this:

="Week " & Code.GetNextWeekNumber(ReportItems!txtYear.Value, Mid(ReportItems!txtWeekPlus[n-1].Value, 6))

The MID function removes the “Week ” part from the header textbox value. The [n-1] part means that it references the textbox of the previous week.

Download the sample RDL file to see how it works!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: