none
MDX Format String for file sizes (MB, GB, TB)

    Pregunta

  • I have a measure (let's say 'Disk Size') which is stored in MB. I would like to format the measure to display it in GB or TB when appropriate.

    E.g.

    Value = 150 -> Display = 150 MB

    Value = 2048 -> Display = 2 GB

    Value = 1048576 -> Display = 1 TB

    I would like to write something like:

    IIF([Measures].[Disk Size] < 1024, "0 MB", IIF([Measures].[Disk Size] < 1048576, "0, GB", "0,, TB"))

    The problem here is that I don't know how to recalculate the value (divisions by 1024 instead of 1000, represented by the comma in the format string).

    Anybody got any ideas?

    miércoles, 20 de junio de 2012 10:05

Respuestas

  • A post-calculation formatting of the result - is to be peformed on the reporting tool, ofcourse as a best practice, using expressions.

    Would you say that a format string (such as "0 %") for a measure is a bad practice?
    My answer would be yes. But again, I am not imposing my thoughts. When certain tasks can be achieved elegantly by a one line statement in SSRS, I would not risk my time finding a solution in a query language.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    viernes, 22 de junio de 2012 13:36

Todas las respuestas

  • With
     
    MEMBER  Test as CASE When [Measures].[Disk Size]=1024 then "0MB"

    WHEN [Measures].[Disk Size]=2048 then "2GB"

    ELSE

    "Your condition"

    END



    Hope this will help you !!!
    Sanjeewan

    miércoles, 20 de junio de 2012 10:18
  • Hmm, I might not have been clear enough. I am looking for a format string.

    The standard format strings support this scenario (meters and km):

    IIF([Measures].[Distance] > 1000, "0, km", "0 m")

    This will format 345 as "345 m" and 2354 as "2 km".

    I am now looking for something which manages to format the original value in MB as GB when appropriate. E.g.:

    23 -> 23.0 MB

    135 -> 135.0 MB

    1024 -> 1.0 GB

    1536 -> 1.5 GB

    There is only one tricky part to it (maybe impossible) and that is dividing by 1024 in the format string. I have no clue how to do that.

    IIF([Measures].[Disk Size] < 1024, "0.0 MB", "<divided by 1024.0> GB")

    miércoles, 20 de junio de 2012 12:00
  • Hi,

    this should work just fine:

    MEMBER [Measures].[DiskSizeV2] AS (
    IIf([Measures].[DiskSize] < 1024, 
    	[Measures].[DiskSize],
    	IIF([Measures].[DiskSize] < 1048576, 
    		[Measures].[DiskSize]/1024,
    		[Measures].[DiskSize]/1048576))
    ), FORMAT_STRING = "#,#0.0" + IIF([Measures].[DiskSize] < 1024, 
    				"\M\B", 
    				IIF([Measures].[DiskSize] < 1048576, 
    					"\G\B", 
    					"\T\B"))
    hth,
    gerhard

    - www.pmOne.com -

    miércoles, 20 de junio de 2012 12:44
  • Hi Gerhard!

    It will work, that's true. But you define a measure, not a format string.

    [Disk Size] will be a measure and will be used in other calculations. Your solution requires me to define duplicate measures for each measure. One for display and one for further calculations.

    I wanted to avoid that, but I fear we can't.

    miércoles, 20 de junio de 2012 13:57
  • i think that if you want a precise value (divide by 1024 instead of 1000 and so on) then you will have to create two measures

    though, you could still rename your current [Disk Size]-Measure to [Disk Size MB] and define a new [Disk Size]-calculated measure as i suggested

    then use [Disk Size MB] in all your previous calculations and hide it so the user only sees the will formated calculated measure whereas all dependent measure sitll use the orginal value

    is this an alternative for you?


    - www.pmOne.com -

    miércoles, 20 de junio de 2012 14:26
  • That is indeed what the only solution seems to be. A visible formatted measure and an invisible 'raw' measure. As written before, I wanted to avoid that.

    The whole thing becomes very bulky in terms of code. Everything will be duplicated and for each measure, the same conversion code with the divisions will have to be copied as well. Not very maintainable...

    miércoles, 20 de junio de 2012 14:31
  • how about using some kind of scale-dimension?

    with 3 members: "in MB", "in GB" and "in TB" - setting the membervalue accordingly to 1, 1024 and 1048576

    then use SCOPE assignments as follows:

    FORMAT_STRING([Scale].[Scale].&[in MB]) = <MB-FormatString>;
    FORMAT_STRING([Scale].[Scale].&[in GB]) = <GB-FormatString>;
    FORMAT_STRING([Scale].[Scale].&[in TB]) = <TB-FormatString>;

    SCOPE([Scale].[Scale].[Scale].members);
         this = [Measures].currentmember / [Scale].[Scale].currentmember.membervalue;
    END SCOPE;   

    just an idea


    - www.pmOne.com -

    jueves, 21 de junio de 2012 8:20
  • I am sure you will end up consuming this data returned from the MDX in Excel or SSRS or any other reporting platform. Why not handle this there itself and not strain the MDX?

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    jueves, 21 de junio de 2012 8:49
  • I am sure you will end up consuming this data returned from the MDX in Excel or SSRS or any other reporting platform. Why not handle this there itself and not strain the MDX?

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Because that's poorly maintainable and not reusable.
    viernes, 22 de junio de 2012 13:07
  • how about using some kind of scale-dimension?

    with 3 members: "in MB", "in GB" and "in TB" - setting the membervalue accordingly to 1, 1024 and 1048576

    then use SCOPE assignments as follows:

    FORMAT_STRING([Scale].[Scale].&[in MB]) = <MB-FormatString>;
    FORMAT_STRING([Scale].[Scale].&[in GB]) = <GB-FormatString>;
    FORMAT_STRING([Scale].[Scale].&[in TB]) = <TB-FormatString>;

    SCOPE([Scale].[Scale].[Scale].members);
         this = [Measures].currentmember / [Scale].[Scale].currentmember.membervalue;
    END SCOPE;   

    just an idea


    - www.pmOne.com -

    Interesting idea!
    viernes, 22 de junio de 2012 13:08
  • I am sure you will end up consuming this data returned from the MDX in Excel or SSRS or any other reporting platform. Why not handle this there itself and not strain the MDX?


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Because that's poorly maintainable and not reusable.
    MDX is not responsible for any text formatting. Any good implementation of BI will focus text formatting at reporting layer. Hope you got the idea.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    viernes, 22 de junio de 2012 13:12
  • MDX is not responsible for any text formatting. Any good implementation of BI will focus text formatting at reporting layer. Hope you got the idea
    I agree. That's exactly why I was looking for a format string in the first place. A post-calculation formatting of the result.
    viernes, 22 de junio de 2012 13:16
  • MDX is not responsible for any text formatting. Any good implementation of BI will focus text formatting at reporting layer. Hope you got the idea

    I agree. That's exactly why I was looking for a format string in the first place. A post-calculation formatting of the result.
    A post-calculation formatting of the result - is to be peformed on the reporting tool, ofcourse as a best practice, using expressions.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    viernes, 22 de junio de 2012 13:22
  • A post-calculation formatting of the result - is to be peformed on the reporting tool, ofcourse as a best practice, using expressions.

    Would you say that a format string (such as "0 %") for a measure is a bad practice?
    viernes, 22 de junio de 2012 13:27
  • A post-calculation formatting of the result - is to be peformed on the reporting tool, ofcourse as a best practice, using expressions.

    Would you say that a format string (such as "0 %") for a measure is a bad practice?
    My answer would be yes. But again, I am not imposing my thoughts. When certain tasks can be achieved elegantly by a one line statement in SSRS, I would not risk my time finding a solution in a query language.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    viernes, 22 de junio de 2012 13:36
  • My answer would be yes. But again, I am not imposing my thoughts. When certain tasks can be achieved elegantly by a one line statement in SSRS, I would not risk my time finding a solution in a query language.

    I'll keep that in mind.
    viernes, 22 de junio de 2012 13:46