MDX Statement results differ in MDX Sample App and VS 2003

Discussion in 'microsoft.public.sqlserver.olap' started by Atrus2711, Feb 20, 2006.

  1. Atrus2711

    Atrus2711 Guest

    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
     
    Atrus2711, Feb 20, 2006
    #1
    1. Advertisements

  2. Atrus2711

    Deepak Puri Guest

    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
     
    Deepak Puri, Feb 21, 2006
    #2
    1. Advertisements

  3. Atrus2711

    Atrus2711 Guest

    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

     
    Atrus2711, Feb 21, 2006
    #3
  4. Atrus2711

    Deepak Puri Guest

    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
     
    Deepak Puri, Feb 21, 2006
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.