MDX Format String for file sizes (MB, GB, TB)
-
miércoles, 20 de junio de 2012 10:05
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?
Todas las respuestas
-
miércoles, 20 de junio de 2012 10:18
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 12:00
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:44Usuario que responde
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 13:57
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 14:26Usuario que responde
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:31
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...
-
jueves, 21 de junio de 2012 8:20Usuario que responde
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:49I 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 -
viernes, 22 de junio de 2012 13:07
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?
Because that's poorly maintainable and not reusable.
Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog -
viernes, 22 de junio de 2012 13:08
Interesting idea!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 -
-
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 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
Because that's poorly maintainable and not reusable.
Cheers, Raunak | t: @raunakjhawar | My Blog
Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog -
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.
-
viernes, 22 de junio de 2012 13:22
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
A post-calculation formatting of the result - is to be peformed on the reporting tool, ofcourse as a best practice, using expressions.
I agree. That's exactly why I was looking for a format string in the first place. A post-calculation formatting of the result.
Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog -
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?
-
viernes, 22 de junio de 2012 13:36
A post-calculation formatting of the result - is to be peformed on the reporting tool, ofcourse as a best practice, using expressions.
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.
Would you say that a format string (such as "0 %") for a measure is a bad practice?
Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog- Marcado como respuesta Elvis LongMicrosoft Contingent Staff, Moderator martes, 26 de junio de 2012 9:29
-
viernes, 22 de junio de 2012 13:46
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.
.png)
