Changing a date format to a shorter date

Question

We've got the following script:

WITH 
SET [Last56Days] as
 TAIL ( [Date].[Date - Calendar Month].[Calendar Day].members, 56 )
MEMBER [Measures].[DateValue] as
 [Date].[Date - Calendar Month].CURRENTMEMBER.member_value, format_string = "short date"
MEMBER [Measures].[DateValue2] as
 [Date].[Date - Calendar Month].CURRENTMEMBER.member_value, format_string = "dd/mm/yyyy"
SELECT
    { [Measures].[DateValue], [Measures].[DateValue2]} ON COLUMNS,
    Hierarchize ({ [Last56Days] } ) ON ROWS
FROM [Our Cube]

It returns this:

Can I change the date format somehow so that the dates are like this "09 Feb 2014" i.e. shorter ?

Answer 1

From the comments I suppose that the issue is that you have integer date keys and a name column which is always of type string in Analysis Services. These will not work with date formats which need dates (or doubles containing the days since January, 1, 1900, and as fractions the time of day, i. e. 8:30 am would be 8.5/24).

Thus, you could use

MEMBER [Measures].[Date as int] as
       [Date].[Date - Calendar Month].CURRENTMEMBER.Properties('Key0', Typed)
MEMBER [Measures].[Date Year] as
       Fix([Measures].[Date as int] / 10000)
MEMBER [Measures].[Date Month] as
       Fix(([Measures].[Date as int] - [Measures].[Date Year] * 10000) / 100)
MEMBER [Measures].[Date Day] as
       [Measures].[Date as int] - [Measures].[Date Year] * 10000 - [Measures].[Date Month] * 100
MEMBER [Measures].[DateValue] as
       // convert it to Date data type and use a format string on that:
       DateSerial([Measures].[Date Year], [Measures].[Date Month], [Measures].[Date Day]),
       format_string = 'dd/mm/yyyy'

See the documentation of Properties and the list of VBA functions like Fix and DateSerial.

Looking at the complexity of my above code, it may be better to use string logic instead of integer logic, i. e. omitting the typed argument to Properties and then calculating year, month, and day using Left, Mid, and Right and converting that to integers. But "the details of that are left as an exercise to the reader".