Date Parameter in ISO-format as column header

• sean redmond

  SSCertifiable

  Points: 5829

  More actions

  April 9, 2018 at 10:22 am

  #150662

  Hello to you all,

  I have a question that I am sure has been asked but I have not  yet found an answer to it.
  We have a website (not yet finished) with a date-parameter (amongst others).
  Is it possible to have the date-parameter in YYYY-MM-DD format as the column header, rather than the Month Year given by SSAS?
  If I create a measure, the measure name is used.
  One option is to replace the automatically generated date dimension with a custom made one and have this date shown.
  Instead, I'm hoping that the problem can be solved by means of MDX.

  The MDX-query runs something like this:
   -- contents of string are from the date dropdown. It gives year and month.
  with      set [DateParameter] as StrToMember( '[Date].[Calendar].[Month].&[2008]&[4]' )
  select    non empty { ( [DateParameter].item(0), [Measures].[Order Count] ) } on 0
            , non empty { ( [Product].[Product Categories].[Subcategory] ) } on 1
  from      [Adventure Works];

  The following is returned:
               April 2008
               Order Count
  Bike Racks            49
  Bike Stands           29
  Bottles and Cages    466
  and so on

  How can I display 'April 2008' as '2008-04-01'?
  Alternatively, if there is a property in the solution in SSDT that I can change, then that would be good too.

  Thank you very much for your time,
  Sean Redmond.

• Martin Schoombee

  SSCoach

  Points: 19134

  More actions

  April 9, 2018 at 10:39 am

  #1985989

  You can try the format() VBA function (https://docs.microsoft.com/en-us/sql/mdx/vba-functions-in-mdx-and-dax) or the FORMAT_STRING MDX function (https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/mdx/mdx-cell-properties-format-string-contents)