MDX Statement results differ in MDX Sample App and VS 2003

Hi,

I use the following MDX statement on a MSSQl 2k server to format a
smalldatetime column in my regional (german) date format, dd.mm.yyyy:

WITH MEMBER [Measures].[TimeMemberUniqueName] AS
'[MyDate].CurrentMember.UniqueName' MEMBER [Measures].[TimeDisplayName] AS
'[MyDate].CurrentMember.Name', FORMAT_STRING='dd.mm.yyyy' SELECT
{[Measures].[TimeMemberUniqueName], [Measures].[TimeDisplayName]} ON Columns,
{[MyDate].Members} ON Rows FROM [MyTable]

This works fine in MDX Sample app (result in dd.mm.yyyy). In VisualStudio
2003, the same statement returns the original smalldatetime value in format
yyyy-mm-dd hh:mm:ss. Obviously, the Format statement is ignored. :-(

How can I influence the date format in MDX? The Dimension is a regular (not
Time!) dimension, but I tested on a Time Dimension as well - same results...

Thanks
Martin

 

Hi Martin,

What happens when you use VBA!Format() function, like:
WITH MEMBER [Measures].[TimeMemberUniqueName] AS
'[MyDate].CurrentMember.UniqueName'
MEMBER [Measures].[TimeDisplayName] AS
'Format(CDate([MyDate].CurrentMember.Name),
"dd.mm.yyyy")'
SELECT
{[Measures].[TimeMemberUniqueName],
[Measures].[TimeDisplayName]} ON Columns,
{[MyDate].Members} ON Rows FROM [MyTable]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

 

Hi Deepak,

thanks for your answer.

It worked nearly perfect: your statement generated dates like 6012006 for
January 6, 2006. I changed the format a bit, eventually to "dd mm yyyy", and
now it evaluates to 06.01.2006, as desired. But how does a format with blanks
generate dots between day, month and year?

Just curious, but happy

Best wishes,
Martin

 

Martin,

Maybe this has to do with locale settings, but their subtleties escape
me. In the US locale, this works:
With Member [Measures].[FmtDate] as
'Format(CDate("2006-02-21"), "dd.mm.yyyy")'

select {[Measures].[FmtDate]} on 0
from Sales
The result is "02.21.2006"

With a format of "dd mm yyyy", result is: "02/21/2006"

In AS 2005, "MM" is needed for month, rather than "mm"


- Deepak

Deepak Puri
Microsoft MVP - SQL Server