SQL Server Reporting Services Format Hours as Hours:Minutes

Question

I'm writing reports on SQL Server Reporting Server that have a number of hours grouped by, say, user, and a total calculated based on the sum of the values.

Currently my query runs a stored proc that returns the hours as in HH:MM format, rather than decimal hours, as our users find that more intuitive. The problem occurs when I try and add up the column using an SSRS expression, because the SUM function isn't smart enough to handle adding up times in this format.

Is there any way to:

1. Display a time interval (in minutes or hours) in HH:MM format while having it calculated in decimal form?
2. Or split up and calculate the total of the HH:MM text values to arrive at a total as an expression?

I'd like to avoid having to write/run a second query just to get the total.

Answer 1

Answering my own #1:

1. Make your query return a number of minutes (denoted by TimeSpent below).
2. Set the format of your textbox to "General" if it isn't already
3. Use the following expression as the value: =FLOOR(Fields!TimeSpent.Value / 60) & ":" & RIGHT("0" & (Fields!TimeSpent.Value MOD 60), 2)
4. For the sum textbox, use the following expression: =FLOOR(Sum(Fields!TimeSpent.Value) / 60) & ":" & RIGHT("0" & (Sum(Fields!TimeSpent.Value) MOD 60), 2)

Answer 2

You may also try putting this function in your report's custom code.

Function secondsToString(seconds As int64) As String
Dim myTS As New TimeSpan(seconds * 10000000)
Return String.Format("{0:00}:{1:00}:{2:00}",myTS.Hours, myTS.Minutes, myTS.Seconds)
End Function

My SPs generally return time in seconds and this keeps me from having to think too hard if I have to return HH:MM:SS in more than one place. Plus, you can do all of your aggregations normally and wrap them in this to get a pretty format.

I can't take credit for this as I know I stumbled across it on the web some time ago, but I can't recall where.