none
How to get the MDX query output in different format

    Question

  • Hi All,

    Whenever I am executing MDX query in Microsoft SQL Server Management Studio I am getting the ouput as follows:

    Name Value

    A      100

    B      200

    C      300

    But I want to get the output in following format in SSIS

    Name Value

    A      100.00

    B      200.00

    C      300.00

    1. Is it possible to achieve with some setting or MDX query modification?

    2. If Source is having data as 100.00 the MDX query output would always display as 100? or whatever values I am getting from MDX query output are exactly same with the cube data.

    Please guide me on this.

    Thanks for your help in advance.

    Regards,

    Vaibhav

    Wednesday, September 07, 2011 9:59 AM

Answers

  • 1. Is it possible to achieve with some setting or MDX query modification?

    Yes , you can use Format_String to format the values from the Measures as below

    with member measures.x
    as
    [Measures].[Internet Order Count] ,FORMAT_STRING = '#.00'
    
    SELECT {measures.x ,[Measures].[Internet Order Count]} ON 0 
      FROM [Adventure Works]
    

    2. If Source is having data as 100.00 the MDX query output would always display as 100? or whatever values I am getting from MDX query output are exactly same with the cube data.

    Not neccesarily , this should be exactly same as the source data. The format depends on the setting made while creating the measure.

    Also as you have mentioned it is needed in SSIS , you can use a Data Conversion transformation task to convert from one datatype to another.

     

    • Proposed as answer by Kieran Patrick Wood Wednesday, September 07, 2011 12:09 PM
    • Marked as answer by Abhiyanta Thursday, September 08, 2011 10:25 AM
    Wednesday, September 07, 2011 11:37 AM

All replies

  • Use FORMAT_STRING in your MDX:http://msdn.microsoft.com/en-us/library/ms146084.aspx

     

    Example from here: http://stackoverflow.com/questions/4998431/any-way-to-control-number-of-decimal-places-while-browsing-ssas-cube

     

    WITH MEMBER [Measures].[NewMeasure] AS '[Measures].[OldMeasure]', FORMAT_STRING='##0.00'



    Wednesday, September 07, 2011 11:32 AM
  • 1. Is it possible to achieve with some setting or MDX query modification?

    Yes , you can use Format_String to format the values from the Measures as below

    with member measures.x
    as
    [Measures].[Internet Order Count] ,FORMAT_STRING = '#.00'
    
    SELECT {measures.x ,[Measures].[Internet Order Count]} ON 0 
      FROM [Adventure Works]
    

    2. If Source is having data as 100.00 the MDX query output would always display as 100? or whatever values I am getting from MDX query output are exactly same with the cube data.

    Not neccesarily , this should be exactly same as the source data. The format depends on the setting made while creating the measure.

    Also as you have mentioned it is needed in SSIS , you can use a Data Conversion transformation task to convert from one datatype to another.

     

    • Proposed as answer by Kieran Patrick Wood Wednesday, September 07, 2011 12:09 PM
    • Marked as answer by Abhiyanta Thursday, September 08, 2011 10:25 AM
    Wednesday, September 07, 2011 11:37 AM