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

CREATE MEMBER CURRENTCUBE.[MEASURES].[someMeasure] AS

[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:

CREATE MEMBER CURRENTCUBE.[Measures].[MesureX]
AS
CSTR(INT([Measures].[LongDurationInSec]/(60*60))) + “:”
+ FORMAT(CDATE([Measures].[LongDurationInSec]/86400
- INT([Measures].[LongDurationInSec]/86400)), “mm:ss”)
FORMAT_STRING = “[HH]:MM:SS”, …

2 thoughts on “SSAS Tip -> Format seconds as HHHH:MM:SS

  1. Pingback: SQL TIP-> Display seconds as HH:MM:SS | Claus on Code

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>