MDX Date Formatting

Question

Can any one please tell me how to format date in MDX queries? We dont use SSRS to generate report ,we have our own customised reporting tool built on SSAS.Date filter sends date in yyyy/mm/dd format . As of now we dont have a date dimension. My date member looks like:

[CNB_DimSampleInfo].[COAReleasedON].&[2013-01-02T03:20:00].

How can I format date in STRTOmemeber? I have tried doing this. My question is how will the value coming from user suit my member format as below. I know ssrs does it easily but we are not using SSRS. Below is my Code

my code

 SELECT 
  [Measures].[Result] ON COLUMNS
 ,NON EMPTY 
    {
        [CNB_DimProduct].[ProductUcode].[ProductUcode].ALLMEMBERS*
        [CNB_DimProduct].[ProductDesc].[ProductDesc].ALLMEMBERS*
        [CNB_DimTest].[TestUcode].[TestUcode].ALLMEMBERS*
        [CNB_DimTest].[TestName].[TestName].ALLMEMBERS*
        [CNB_DimSampleInfo].[LotNo].[LotNo].ALLMEMBERS*
        [CNB_DimSampleInfo].[BatchNo].[BatchNo].ALLMEMBERS*
        [CNB_DimSampleInfo].[COAReleasedBy].[COAReleasedBy].ALLMEMBERS*
        [CNB_DimSampleInfo].[COAReleasedON].[COAReleasedON].ALLMEMBERS*
        [CNB_DimSampleInfo].[SampleReferenceNo].[SampleReferenceNo].ALLMEMBERS*
        [CNB_DimSampleInfo].[AnalysedBy].[AnalysedBy].ALLMEMBERS*
        [CNB_DimSampleInfo].[AnalysedOn].[AnalysedOn].ALLMEMBERS
    } ON ROWS
FROM 
(
  SELECT 
    StrToMember
    (
            "[CNB_DimSampleInfo].[COAReleasedON].[" + Format("2013-01-02","yyyy MM")
          + "]:STRTOMember([CNB_DimSampleInfo].[COAReleasedON].["
        + 
          Format
          ("2013-01-02"
           ,"yyyy MM"
          )
      + "]"
    ) ON COLUMNS
  FROM Cube001
);

score 0 · Answer 1 · answered Apr 29 '16 at 09:50

There is also StrToSet which is better in your circumstance as you're using the : operator which returns a set:

...
...
FROM 
(
  SELECT 
    StrToSet
    (
            "[CNB_DimSampleInfo].[COAReleasedON].[" + Format("2013-01-02","yyyy MM")
          + "]:[CNB_DimSampleInfo].[COAReleasedON].["
        + 
          Format
          ("2013-01-02"
           ,"yyyy MM"
          )
      + "]"
     ,CONSTRAINED
    ) ON COLUMNS
  FROM Cube001
);

does the following definitely return the date formatted the same as your key values?

Format("2013-01-02","yyyy MM")

Do your key values for dates look like this?...

[CNB_DimSampleInfo].[COAReleasedON].[2013 01]

score 0 · Answer 2 · answered Apr 29 '16 at 11:23

Your date member

[CNB_DimSampleInfo].[COAReleasedON].&[2013-01-02T03:20:00]

looks like a bad candidate for a user date parameter, as it's precise down to the minute (perhaps the second). Unless the user exactly matches the time, they'll get nothing. But perhaps you're enforcing exact matches by using a LimitToList select list in the UI.

To get this member name, you can format the input string like this:

format([Your Input Parameter],'yyyy-MM-ddThh:mm:ss')

score 0 · Answer 3 · answered May 03 '16 at 04:43

I have tried out using filter as below

SELECT
[Measures].[Result] ON COLUMNS
,NON EMPTY
 {
  filter([CNB_DimSampleInfo].[COAReleasedON].members,instr([CNB_DimSampleInfo].[COAReleasedON].currentmember.member_caption,"2013-01-02")>0 or instr([CNB_DimSampleInfo].[COAReleasedON].currentmember.member_caption, "2013-04-01")>0)

   *[CNB_DimProduct].[ProductUcode].[ProductUcode].ALLMEMBERS*
    [CNB_DimProduct].[ProductDesc].[ProductDesc].ALLMEMBERS*
    [CNB_DimTest].[TestUcode].[TestUcode].ALLMEMBERS*
    [CNB_DimTest].[TestName].[TestName].ALLMEMBERS
} ON ROWS

FROM Cube002

MDX Date Formatting

3 Answers3