How to display time in hours overshooting 24 hours in SSRS

Question

I have a report with a column displaying time cumulatively overshooting 24 hours. I'd like it to be displayed in hours. I've found a solution that when time exceeds 86400 seconds (number of seconds in a day), it'll display number of days and time but I want only time in hours to be displayed.

=IIF(Fields!TotalTime.Value < 86400, 
Format(DateAdd("s", Fields!TotalTime.Value, "00:00:00"), "HH:mm:ss"), 
Floor(Fields!TotalTime.Value / 86400) & " days, " & Format(DateAdd("s", Fields!TotalTime.Value, 
"00:00:00"), "HH:mm:ss")

Answer 1

You could do this with expressions only but this way is a little bit more reusable..

Add the following code to your Report's custom code (apologies to the original author who's blog I based this on years ago... I can't find your post)

Public Function SecondsAsHHMMSS(ByVal secs) As String
        Dim h As String =INT(secs/3600)
        Dim m as string
        Dim s as string
        If Len(h) <2 Then
                h = RIGHT(("0" & h), 2)
        End If
        m = RIGHT("0" & INT((secs MOD 3600)/60), 2)
        s = RIGHT("0" & ((secs MOD 3600) MOD 60), 2)

        SecondsAsHHMMSS= h & ":" & m & ":" & s

End Function

This function will take a number of seconds and convert to to HH:MM:SS format.

Now all we have to do is pass in the cumulative number of seconds for each row.

We can use System.TimeSpan for this.

This assumes the database field is a TIME datatype

=Code.SecondsAsHHMMSS(RunningValue(Fields!TimeInOffice.Value.TotalSeconds, SUM, Nothing))

Starting from the middle and working out....

1. We get the value of the TimeInOffice field and, as it's a Time datatype we can use the TotalSeconds property to get the number of seconds this time represents.
2. We get the running sum of these number of seconds ('Nothing' is the scope, if you want to limit the scope within a rowgroup etc, put the name of the rowgroup, in quotes, in place of the Nothing keyword)
3. New we have the number of seconds, we pass this to our custom function

The result looks like this.