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:

enter image description here

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

I am guessing a bit: Maybe `CURRENTMEMBER.member_value` is already a string created when processing the dimension, and hence formatting does not change it at all. You could try to use `CurrentMember.Propertities('key0', TYPED)` and see if the formatting has an effect then. — FrankPl, Apr 03 '14 at 07:22
they are now back to the DateKey format - we us integers in our warehouse e.g. 20140403 — whytheq, Apr 03 '14 at 15:11

score 2 · Accepted Answer · answered Apr 03 '14 at 15:50

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".

spaghettifunk · Answer 2 · 2014-04-02T15:30:44.367

0

~~Try with one of this format:~~

 =Format(Fields!myDateTime.Value, "M/d/yy") ... 6/15/09 
 =Format(Fields!myDateTime.Value, "M/d/yyyy h:mmtt") ... 6/15/2009 2:45PM 
 =Format(Fields!myDateTime.Value, "MM/dd/yy HH:mm") ... 06/15/09 14:45 
 =Format(Fields!myDateTime.Value, "MMM d, yyyy") ... Jun 15, 2009 
 =Format(Fields!myDateTime.Value, "Short Date") ... 6/15/2009 
 =Format(Fields!myDateTime.Value, "Long Date") ... Monday, June 15, 2009 
 =Format(Fields!myDateTime.Value, "ddd dd/MM/yyyy") ... Mon 04/04/2011

~~Source Link. Hope it helps~~

EDIT I'm sorry! I didn't see the comment in the end. Anyway, I guess you've already seen this Link but you perhaps can try to use the format_string like so:

format_string = "dd/mmm/yyyy"

edited Apr 02 '14 at 15:30

answered Apr 02 '14 at 14:59

spaghettifunk

1,936
4
24
46

check out Bob's comment at the bottom of that source link - I agree this is an expression solution within `SSRS` not an `MDX` solution...or maybe you can test against your own environment and give full script answer? – whytheq Apr 02 '14 at 15:12
I'm sorry but I can't test because I don't have Analysis service anymore – spaghettifunk Apr 02 '14 at 15:32
I'll add another measure to OP – whytheq Apr 02 '14 at 15:45

Changing a date format to a shorter date

2 Answers2

Linked