2 Replies Latest reply on Nov 4, 2013 5:59 AM by Israr Khan

    SSAS olap cube time duration measure display in Days:HH:MM:SS format

    Israr Khan

      Hi,

       

      I have a measure in MS SSAS cube that calculates the time duration of a case call. The format string of that measures is

      D: HH:MM:SS.

      i.e. if duration exceeds more than 24 hours, e.g. if the case duration is 49 hours, then cube will display  "01 01:00:00" .

       

      Tableau is not showing the time duration in this format. If I use format as D:HH:MM:SS it converts  the duration 00:12:10:03 into 30:12:10:03.

       

      Is there a way that we can show the time duration measure for MS SSAS cube in DD:HH:MM:SS format.

       

      thanks

        • 1. Re: SSAS olap cube time duration measure display in Days:HH:MM:SS format
          . Indumon

          Hi Israr,

           

          If your measure 'duration' is by hour then you can achieve the required format using a simple calculation.

           

           

          Str(int([duration]/24))+ ':' +
          (IIF(len(Str((int([Duration]%24))))<2,'0'+Str(int([duration]%24)),
          Str(int([duration]%24))))+':00:00'
          

           

          screen.JPG.jpg

          Please let us know if it is not working.

          • 2. Re: SSAS olap cube time duration measure display in Days:HH:MM:SS format
            Israr Khan

            Thanks for the answer. I tried your solution to create the calculated measure in SSAS cube.

            Using that calculated measure in Tableau , it is still showing #Error for that column.

            After investigating in detail it seems like tableau doesn't like the string ":" in the calculated measure.

            if I remove that string from measure then calculation work but then display is like a number.

            now

            CREATE MEMBER CURRENTCUBE.[Measures].[SessionDuration]

            AS

            right('00'+Str(int([Measures].[Sess:TotalSec]/86400)) ,2)

            +':'

            +right('0'+Str(int(([Measures].[Sess:TotalSec]-(int([Measures].[Sess:TotalSec]/86400)*86400))/3600)),2)

            +':'

            +right('0'+Str(int(([Measures].[Sess:TotalSec]-(int([Measures].[Sess:TotalSec]/3600)*3600))/60)),2)

            +':' 

            +right('0'+Str(int(([Measures].[Sess:TotalSec]-(int([Measures].[Sess:TotalSec]/60)*60)))),2)

            ,

            NON_EMPTY_BEHAVIOR = { [Session Count] },

            VISIBLE = 1 ,  DISPLAY_FOLDER = 'Session' ,  ASSOCIATED_MEASURE_GROUP = 'Session'  ;   

             

            the only way to display it as a sting by creating calculated member in tableau and data type is string.

            but with this approach it cannot be used as measure.

             

            I want to use this measure to display bar graph showing these values on axes.