none
Output of Attribute Calendar Date from Date Dimension needs to Format in mm/dd/yyyy

    Question

  • Hi All,

    I have a energy cube which has data of USA and Australia. We have excel sheet to access cube data for USA and another one for Australia.

    We want to format all the dates in mm/dd/yyyy in USA Excel sheet and dd/mm/yyyy in Australia Sheet.

    I tried formating Calendar date and can do it. It is not changing. Its current format is yyyy-mm-dd


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Friday, May 18, 2018 4:06 AM

All replies

  • Hi S Kumar Dubey,

    Thanks for your question.

    In this scenario, take USA key as an example,you might try to  format the Dimension Attribute field in your view first. Example:
    FORMAT(DateAlternateKey,'MM/dd/yyyy','en-US') AS DateUSAKey

    Then you should go to DateUSAKey attribute properties of your Date Dimension and set NameColumn=DateUSAKey. This attribute should be set to Type=Date.

    For Australia, you can try the same. For information about format function, please refer to below article.
    https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-2017


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Monday, May 21, 2018 11:50 AM
    Monday, May 21, 2018 1:19 AM
    Moderator
  • Thank you for response.

    I don't think It is allowed to change Datatype of NameColumn. It only allows WChar. I tried changing and was allowed but when I tried to save it I was getting below error message.

    Dimension [Date] cannot be saved because of the following errors:

    DimensionAttribute [Date].[Calendar Date] : The 'Integer' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.  DimensionAttribute [Date].[Calendar Date 1] : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.  DimensionAttribute [Date].[Calendar Date In US Format] : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.

    For your Reference: Below are the datatype and value of columns in Database.

    [Date].[Calendar Date] =20180131  (Data Type=Int in Database)

    [Date].[Calendar Date 1] = 2018-01-31  (Data Type=Date in Database)

    https://social.msdn.microsoft.com/Forums/en-US/999df234-981c-4965-b905-e786c4a73292/namecolum-data-type-wchar-only-ssas-2005?forum=sqlanalysisservices


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, May 23, 2018 12:11 AM
  • Hi S Kumar Dubey,

    Thanks for your response.

    >>>I don't think It is allowed to change Datatype of NameColumn. It only allows WChar. I tried changing and was allowed but when I tried to save it I was getting below error message.

    Please note that Format will return value as nvarchar or null data type, thus, you can use it as NameColumn.

    DECLARE @myDateTime DATETIME
    SET @myDateTime = '02-28-2013'
    
    SELECT convert(VARCHAR(10),@myDateTime, 101) DateUSAKey
    
    select FORMAT( @myDateTime,'MM/dd/yyyy','en-US')  as DateUSAName
    



    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 23, 2018 1:38 AM
    Moderator