SQL Server Reporting Services Format Hours as Hours:Minutes
- by Frank Schmitt
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:
Display a time interval (in minutes or hours) in HH:MM format while having it calculated in decimal form?
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.
