CODEWITHSUNDEEP
timeformatssrs-2008
Frank Schmitt
Frank Schmitt

Reputation: 25775

SQL Server Reporting Services Format Hours as Hours:Minutes

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.

Upvotes: 2

Views: 11440

Answers (3)

Aadspro Sandr
Aadspro Sandr

Reputation: 11

So in your case its minutes so you need to convert it to seconds , you can try using below code:

=Format(DateAdd("s",(Parameters!ReportParameter1.Value * 60), "00:00:00"),"HH:mm:ss")

Hope this helps

Upvotes: 0

KennyNeal
KennyNeal

Reputation: 46

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.

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 25775

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)

Upvotes: 4

Related Questions