Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
MDX query for Concatinating Date parts

Answered MDX query for Concatinating Date parts

  • Thursday, January 10, 2013 11:36 AM
     
     
    Sign In to Vote
    0

    HI All,

    I am runnning the following MDX query, but I am not getting the expected output.

    WITH  MEMBER [Measures].[Full Date] as 'NOW()'

    MEMBER [Measures].[What Day] as 'DAY([Full Date])'

    MEMBER [Measures].[What Month] as 'MONTH([Full Date])'

    Member [Measures].[What Year] as 'YEAR([Full Date])'

    MEMBER [Measures].[Test] as 
    [What Day] + "-"  + ([What Month])  + "-"  + ([What Year]) 

    SELECT
       {[Full Date],[What Day],[What Month],[What Year],[Test]} ON COLUMNS
    FROM [Adventure Works]

    For the Test measure I want the output as 10-1-2013, rest of the Measures giving the correct answer.

    I want to concatinate the rest measure into the Test measure and want that result.

    Please help me to accompolish this.

    Sudipta Ghosh Tata Consultancy Services

     

All Replies

  • Thursday, January 10, 2013 11:43 AM
     
      Has Code
    Sign In to Vote
    0

    This is what you want?

    WITH 
MEMBER [Measures].[ActualDate] as 'VBAMDX.Format(VBAMDX.Now(),"dd-MM-yyyy")'
SELECT
   {[ActualDate]} ON COLUMNS
FROM [Analysis Services Tutorial]

    Or this:

    WITH  MEMBER [Measures].[Full Date] as 'NOW()'
MEMBER [Measures].[What Day] as 'DAY([Full Date])'
MEMBER [Measures].[What Month] as 'MONTH([Full Date])'
Member [Measures].[What Year] as 'YEAR([Full Date])'
MEMBER [Measures].[Test] as 
CSTR([What Day]) + "-"  + CSTR([What Month])  + "-"  + CSTR([What Year]) 
SELECT
   {[Full Date],[What Day],[What Month],[What Year],[Test]} ON COLUMNS
FROM [Analysis Services Tutorial]
    Regards,Eshwar.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Eswararao C Thursday, January 10, 2013 11:46 AM
    •  
     
  • Thursday, January 10, 2013 12:00 PM
     
     
    Sign In to Vote
    0

    Hi Eshwar,

    Thanks so much for the quick reply.

    Actually I have a Last Modified Date dimension which have a  attribute called Actual Date,

    it contains date in the format like 1/10/2013 i.e. mm/dd/yyyy whereas I want the attribute value to be in the format of dd/mm/yyyy

    As i have to display the value of the attribute in a report, so now can we use the dimension attribute in the above formulae provided by you.

    Sudipta Ghosh Tata Consultancy Services

     
  • Thursday, January 10, 2013 12:15 PM
     
     
    Sign In to Vote
    0

    You can do the formatting in the report itself instead of doing it here.

    Format(Field!DateColumn.Value,”dd/MM/yyyy”)

    http://www.jackdonnell.com/?p=122

    http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/32b62d3c-9a32-41bd-b7ad-95c3b89a7668

    Regards,Eshwar.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Eswararao C Thursday, January 10, 2013 12:15 PM
    •  
     
  • Thursday, January 10, 2013 12:30 PM
     
     
    Sign In to Vote
    0

    HI Eshwar,

    I have tried to change the format of the attribute in SSAS also, but it seems that SSAS only allows the date as

    mm-dd-yyyy

    If also I changed the date format to dd-mm-yyyy in SSAS, the value still comes as 1/10/2013 instead of 10/01/2013

    I will be totally surprised if SSAS wont allow me to do this...:(

    Sudipta Ghosh Tata Consultancy Services

     
  • Friday, January 11, 2013 3:30 AM
     
     
    Sign In to Vote
    0

    I would suggest do it in ssrs report that would be easier instead of struggling here.

    Regards,Eshwar.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Monday, January 14, 2013 8:17 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Sudipta,

    I think that you can create a new named calculation for the Date table in the Data Source View. We can use the following expression to get the expected date format:
    Expression: Convert(varchar(10), FullDateAlternateKey,103)

    For more information about create named calculation and Convert, please see the following articles:
    Define Named Calculations in a Data Source View (Analysis Services): http://msdn.microsoft.com/en-us/library/ms174859.aspx
    CAST and CONVERT (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx

    Regards,

    Elvis Long
    TechNet Community Support