Search code examples
sqlssasmdx

How to format value as float with 2 deciaml only in mdx?

Here is my value :

VALUE -0,00398911382022891

and member which count this value

         MEMBER [Date].[Percentage] AS 
              ([Date].[AverageSaleNumberThisYear] - [Date].[AverageSaleNumberPreviouseYear] )/ [Date].[AverageSaleNumberThisYear].

If I use FORMAT_STRING = "Percent" I am getting this value : -0,40%, but i want exactly the same but without the % sign.

Solution

  • You can use custom format strings in MDX. It sounds like you want

    FORMAT_STRING = "0.00"

    or

    FORMAT_STRING = "Fixed"

    I'm assuming that you didn't really want to multiple the value by 100, which is what "Percent" does)

    If you do want it multiplied by 100, then do that in the expression:

         MEMBER [Date].[Percentage] AS 
          100 * ([Date].[AverageSaleNumberThisYear] - [Date].[AverageSaleNumberPreviouseYear] ) / [Date].[AverageSaleNumberThisYear].