Ordinal Number in Reporting Services

Recently I had a client request for reporting services dates to use ordinal numbers, that being, “1st instead of 1, 2nd instead of 2″ etc. To my surprise, this isn’t a standard datetime format in reporting services!

So I set out to create my own ordinal function based on a C# version I found on the Internet.

To use the below function in your report;

1) Open up your report in Reporting Services
2) Make sure you’re in the Design section and click the “Report” menu option (up the top), then “Report Properties”
3) Select “Code”
4) Copy and Paste the function below into the “Custom Code” box.

Public Function FormatOrdinal(ByVal day As Integer) as String

    ' Starts a select case based on the odd/even of num
    Select Case (day Mod 100)
        ' If the nymber is 11,12 or 13 .. we want to add a "th" NOT a "st", "nd" or "rd"
        Case 11
        Case 12
        Case 13
            Return day.ToString() + "th"
    End Select

    ' Start a new select case for the rest of the numbers
    Select Case day Mod 10
        Case 1
            ' The number is either 1 or 21 .. add a "st"
            Return day.ToString() + "st"
        Case 2
            ' The number is either a 2 or 22 .. add a "nd"
            Return day.ToString() + "nd"
        Case 3
            ' The number is either a 3 or 33 .. add a "rd"
            Return day.ToString() + "rd"
        Case Else
             ' Otherwise for everything else add a "Th"
            Return day.ToString() + "th"
    End Select
End Function

I was then able to use the function in an expression.

I wanted to format “01/01/2010″ as “1st January 2010″. My expression was;

=Code.FormatOrdinal(Day(Fields!LETTERDATE.value)) & " " & MonthName(Month(Fields!LETTERDATE.value), False) & " " & Year(Fields!LETTERDATE.value)