This is a simple MDX snippet, but it comes up often enough that I think it’s worth a blog entry. A frequent question that comes up is how to format dates for display in MDX, especially in the parameter boxes on Reporting Services reports. The best practice is to let whatever front end you are working with do the formatting, but sometimes that isn’t always possible or feasible.
Here’s an example that uses 3 of the most common approaches. This example runs against Adventure Works.
WITH
MEMBER [Measures].[X] AS VBA!Format([Date].[Calendar].CurrentMember.Name, "medium date")
MEMBER [Measures].[Y] AS CStr(Year([Date].[Calendar].CurrentMember.Name)) + " " + CStr(Month([Date].[Calendar].CurrentMember.Name))
MEMBER [Measures].[Z] AS CDate([Date].[Calendar].CurrentMember.Name), FORMAT_STRING = "mmmm yyyy"
SELECT {[Measures].[X], [Measures].[Y], [Measures].[Z] } ON 0,
[Date].[Calendar].[Date].Members ON 1
FROM [Adventure Works]
[Measures].[X] – Uses the VBA format function
[Measures].[Y] – Uses native functions to get at the numeric date components
[Measures].[Z] – Uses the FORMAT_STRING function to apply a mask to the incoming date value.
Given a choice, I would use the method employed by [Measures].[Z]
Thank you! VBA!Format was failing me when it came to AM/PM. The Format String method worked.