Suppose you have a calculation (or even a real measure) which returns elapsed time or duration. We want to present it to the user as such, i.e. break into hours, minutes and seconds. This sounds easy enough, we would just use appropriate FORMAT_STRING. If we read documentation, we quickly find that by using ‘hh’ for hours, ‘mm’ for minutes and ‘ss’ for seconds, we can have ‘hh:mm:ss’ formatting, i.e.

with member measures.delta as 0.14159
 ,format_string = 'hh:mm:ss'
select {measures.delta} on 0
from [Adventure Works]

will produce ‘03:23:53’ result. This works great, but if duration is greater than one day, we need to include number of days elapsed as well. So it may look like the format string ‘d “days” hh:mm:ss’ would work, but there are few problems with using ‘d’ inside format string. It is supposed to give the day of the month, therefore it can not work for more than 31 days, and it has other undesired effects on durations – like returning 31 for values between 0 and 1, and 30 for values between 1 and 2. So we need better solution. And the solution is to use real MDX expression to build FORMAT_STRING calculation property.

I discovered that even some of the advanced MDX users were surprised that MDX expressions can be used with FORMAT_STRING. Certainly, everybody knows that it is possible to use MDX expressions for other calculations properties, like BACK_COLOR – there is whole chapter dedicated to it in my “Fast Track to MDX” book, or even more exotic ones, like LANGUAGE – see this blog for example. But there are no examples of using MDX expressions in the most popular and widely used calculation property – FORMAT_STRING. (Perhaps the reason for that in AS2000 it was not possible, but it certainly is possible according to OLEDB for OLAP spec, and both AS2005 and AS2008 fully support it).

So in our case one way to write such expression would be

with member measures.delta as 3.14159
 ,format_string 
   = 
    IIF(
      measures.delta < 1
     ,'d "days" hh:mm:ss'
     ,'"' + cstr(int(measures.delta)) + ' days" hh:mm:ss')
select {measures.delta} on 0
from [Adventure Works]