SSRS and SSAS Formatting Dates

Ok, add this one to the annoying bin. If you are writing SSRS (SQL Server Reporting Services) reports of a SSAS (SQL Server Analysis Services) cube, and there is a time dim, you need to format the date parameters in the SSRS report to match the SSAS date format, as a string.

I started out doing this a while ago, like so:

=”[Time].[Calendar Date].&[“& Year(DateAdd(“D”,-1,DateTime.Now())) &”-“& IIf(Len(Month(DateAdd(“D”,-1,DateTime.Now()))) < 2,Month(DateAdd(“D”,-1,DateTime.Now())),”0″&Month(DateAdd(“D”,-1,DateTime.Now()))) &”-“& IIF(Len(Day(DateAdd(“D”,-1,DateTime.Now())).ToString()) < 2,”0″ & Day(DateAdd(“D”,-1,DateTime.Now())).ToString(),Day(DateAdd(“D”,-1,DateTime.Now())).ToString()) &”T00:00:00]”

Wow.. Ugly, hard to read, hard to debug. Just ran into issues, etc.

I decided to finally just write a function to do the same thing and make sure it is correct once and for all..

Function GetSSASDate(ByVal DateToRun As DateTime) As String
    Dim result As String = “[Time].[Calendar Date].&[”

    result = result & Year(DateToRun).ToString() & “-”

    If Month(DateToRun).ToString().Length < 2 Then
        result = result & “0”
    End If

    result = result & Month(DateToRun).ToString() & “-”

    If Day(DateToRun).ToString().Length < 2 Then
        result = result & “0”
    End If

    result = result & Day(DateToRun).ToString() & “T00:00:00]”
    Return result
End Function

then, I go to my report properties, Custom Code and add that function, then in my parameter I call it like so:

=Code.GetSSASDate(DateAdd(DateInterval.Day,-1,DateTime.Now()))

Much cleaner, easier and just all around good. If I wanted to take this a step further, I would actually just make an assembly with some useful functions and deploy to my SSRS server, but I will save that for a later time.