How to display a time span of seconds in hh:mm:ss format in Reporting Services

In MS Reporting Services 2008, I have a field that is a duration stored as seconds. Is there a slick way to get i开发者_Python百科t into hh:mm:ss format in a group section of the report?

If you just want to display it, convert in an expression for the Value of the textbox:

=Format(DateAdd("s", Fields!MySecondsField.Value, "00:00:00"), "HH:mm:ss")

If you want to do calculations on it, convert the seconds to a DateTime in your dataset. Using SQL:

SELECT DATEADD(ss, MySecondsField, '1900-01-01') AS SecondsAsDateTime
FROM TimeTable

In Linq this would be something like:

var qry = from Q in t.TimeList
    select new
    {
        SecondsAsDateTime = DateTime.Today.AddSeconds(Q.MySecondsField) 
    };

Then you can just format it as a normal DateTime.

If you need to work with times longer than 24 hours (Chris Latta's solution will wraparound in these cases), then there are a couple of solutions.

Simple Formula

If you want to just use a formula on the field such the following from this thread, (which also links back to this question)!

=int(sum(Fields!Sec_Online.Value)/3600) & ":" & int((sum(Fields!Sec_Online.Value) Mod 3600)/60) & ":" & (sum(Fields!Sec_Online.Value) Mod 3600) Mod 60

If you need to pad your value to 2 characters you can wrap a RIGHT("0" & {X}, 2) around each sub-section, where {x} indicates one of the individual calculations in the above formula.

Code Behind

Another approach, also suggested in this thread, is to use TimeSpan.FromSeconds(doc), and there is an implementation of that on this blog, using custom code behind in the report.

I ended up using the custom code approach (as I had lots of fields sharing this), and combining it with something more like the first method as I didn't want days to start appearing I just wanted hours to count up bigger than 23.

I added some custom code to the report as follows which pads all values to at least 2 characters, and allows hours to hours count up > 23.

Public Function ConvertSecondsToHourMinSec(ByVal intTotalSeconds) As String
    Dim hours As String =INT(intTotalSeconds/3600)
    If Len(hours) < 2 Then
        hours = RIGHT(("0" & hours), 2)
    End If
    Dim mins As String = RIGHT("0" & INT((intTotalSeconds MOD 3600)/60), 2)
    Dim secs AS String = RIGHT("0" & ((intTotalSeconds MOD 3600) MOD 60), 2)

    ConvertSecondsToHourMinSec = hours & ":" & mins & ":" & secs

End Function

and then called this from each cell in questions as follows:

=code.ConvertSecondsToHourMinSec(Fields!MyField.Value)

I hope this helps someone else!

Use expression below, replace bold with your field containing the seconds variable.

=DateAdd(DateInterval.Second, Sum(Fields!totalDuration.Value), CDate("1900-01-01 00:00:00"))

I always apply formating in the textbox properties-

H"h "m"m "s"s" will show as "2h 16m 5s"

use a function like this:

Public Function ConvertTsToHMS(myValue As long) As String
    Dim ts as TimeSpan = TimeSpan.FromSeconds(myValue)

    return Int(ts.TotalHours).ToString("00") + ":" + Int(ts.Minutes).ToString("00") + ":" + ts.Seconds.ToString("00")

End Function

I've used the idea of Xan recently and due to the fact the numbers of seconds I have are quite large - I ran out of integer type limit. Therefore I designed a different approach - maybe someobody will find it useful :)

I needed to create calculated fields for Hours, Minutes and Seconds - used following formulas:

• XXXNumberOfHours = int(Fields!TimeInSec.Value/3600)
• XXXNumberOfMinutes = int((Fields!TimeInSec.Value Mod 3600)/60)
• XXXNumberOfSeconds = =((Fields!TimeInSec.Value Mod 3600) Mod 60)

Then I created an expression to display the seconds in HH:MM:SS (without days - did not needed that) format:

1. (Sum(Fields!LWTNumberOfHours.Value)+int((Sum(Fields!LWTNumberOfMinutes.Value) + int(Sum(Fields!LWTNumberOfSeconds.Value)/60))/60))
2. & ":" &
3. right("0" & (Sum(Fields!LWTNumberOfMinutes.Value) + int(Sum(Fields!LWTNumberOfSeconds.Value)/60)) Mod 60, 2)
4. & ":" &
5. right("0" & Sum(Fields!LWTNumberOfSeconds.Value) Mod 60, 2)

In the above lines you can see that line (1) calculates number of hours, line (3) calculates number of minutes and line (5) calculates number of seconds. Of course you can notice that additional calculations are made to get the number of full minutes out of XXNumberOfSeconds and same applies to Minutes/Hours. This could be also done in the calculated fields already (and maybe it would even be more right to do so :) ) - however I preferred to use the above approach.

This way I was able to still format very large numbers of seconds that exceeds integer typesize.