Claus on Code

A data dudes random musings about code, bugs, products, life etc.

SSAS Tip -> Format seconds as HHHH:MM:SS

A common way of showing durations in SSAS, is to format them as HH:MM:SS. A common scenario is to store the durations in seconds. To display them in the HH:MM:SS format, first we have to convert the duration to hours, and then put on the format, like so


[Measures].[someMesureInSeconds]/86400,FORMAT_STRING = “HH:MM:SS”,…

However, this only works if the duration is less than 24 hours.

If it is more, then there is no out the box way to show it in the correct format. To get around that problem, you have to create a mearure as a string. First calc the hours, and then add the minutes and seconds formated as above, like so:

CSTR(INT([Measures].[LongDurationInSec]/(60*60))) + “:”
+ FORMAT(CDATE([Measures].[LongDurationInSec]/86400
– INT([Measures].[LongDurationInSec]/86400)), “mm:ss”)

2 responses to “SSAS Tip -> Format seconds as HHHH:MM:SS”

  1. Very useful tip. Thanks

  2. […] in my previous blogpost where I did the same in analysis services, this only works if the duration is below 24 hours, else […]

Leave a Reply

Your email address will not be published. Required fields are marked *