Q: I have measure in my cube that shows duration on some event in seconds. How can I convert numeric result to string in the format hh:mm:ss?

Q: I have measure in my cube that shows duration on some event in seconds. How can I convert numeric result to string in the format hh:mm:ss?

For example, when my measure value is 635, I would like to see 10:35 (that is 10min and 35 second) as this would be much easier to understand. How can I do this with MDX?

A: Lets say we have a measure [Duration In Sec] that contains duration of some events. We can create new calculated measure [Duration String] that will take value of measure [Duration in Sec] and will translate that value to the string format hh:mm:ss. Here is how we will calculate values for hours, minutes and seconds

- seconds: we will divide [Duration in Sec] by 60 and will use a reminder. MDX does not have reminder (mod or %) function, but we can use function int(<decimal>) to get integer part of any decimal number. So reminder calculation would be: x - int(x/60)*60. As we would like to have leading zero for single digit durations, we will adjust formula: right('0' + cstr(x - int(x/60)*60), 2). Here x = [Duration in Sec]

- minutes: we will divide [Duration in Sec] by 60*60 and will use reminder as a resulting number of minutes. Formula for that would be: Int(x - Int(x/60/60)*60*60) / 60)). If value of [Duration in Sec] is less than 10min, then we will not need leading zeros to represent minutes. If value  of [Duration in Sec] is more than 10min, then we will need leading zeros. So formula for minutes would be:

IIF (x < 60, '',
     IIF (x < 600, CStr(Int( (x - Int(x/60/60)*60*60) / 60))
        , RIGHT('0' + CStr(Int((x - Int(x/60/60)*60*60) /60)) , 2)
     )
    )

- hours: we will divide [Duration in Sec] by 3600 (60*60) and will use whole integer part. We will not show hours part of the string if duration is less than 1 hour.

 So our final MDX formula would be:

CREATE MEMBER CURRENTCUBE.Measures.[Duration String] AS 
 IIF([Measures].[Duration In Sec] = NULL, NULL,
    -- Hours
    IIF ([Measures].[Duration In Sec] < 60*60, '',
        CStr(Int([Measures].[Duration In Sec]/60/60)) + ':'
    )
    -- Minutes
    +
    IIF ([Measures].[Duration In Sec] < 60, '',
     IIF ([Measures].[Duration In Sec] < 600,
        CStr(Int(
            ([Measures].[Duration In Sec] - Int([Measures].[Duration In Sec]/60/60)*60*60)
            /60))
        ,
        RIGHT('0' + CStr(Int(
            ([Measures].[Duration In Sec] - Int([Measures].[Duration In Sec]/60/60)*60*60)
            /60))
        , 2)
     )
    )
    -- Seconds
    + ':'
    + RIGHT('0' + CStr([Measures].[Duration In Sec] - (Int([Measures].[Duration In Sec]/60) * 60)), 2)
 )
;

Here are samples of results:

Note: It would be also very easy to adjust this formula to disply results like 1h 17min 27sec.