Search code examples
sql-serverreporting-servicesformattingssrs-2008

Display Sum of time in HH:MM format in SSRS 2008

I have a table with last column with total_service_time in HH:MM format getting correctly from my SQL Query.

I am getting #Error while adding expression =Sum(Fields!total_service_time.Value) in last row to get the sum of total_service_time in HH:MM format.

Is there any way to get the Sum of time in HH:MM format in last row

My query for total_service_time value:

(SELECT    CAST(total / 60 AS varchar(8)) + ':' + CAST(total % 60 AS varchar(2)) AS Expr1
 FROM (SELECT CAST(SUM(Action.[Travel Time] + Action.[Total Productive Time]) AS int) AS total) AS T) AS total_service_time

Screenshot Thanks, Salman

Solution

  • Your are trying to sum up hours and minutes whose format is not compatible to with SUM so to add hours and minutes you need to use specific DATETIME functions. Try out the below expression - 

    =Right("0" & Sum(CInt(Left(Fields!total_service_time.Value,2)), "<DataSetName>")
    + Floor(Sum(CInt(Right(Fields!total_service_time.Value,2)), "<DataSetName>") / 60),2)
  & ":" & Sum(CInt(Right(Fields!total_service_time.Value,2)), "<DataSetName>") Mod 60

    What I am trying is taking the total from the hours, adding the overflow from the total minutes, then concatenating the minutes. References - Sample DataSet Output

    Edit: New expression as per the requirement - 

    =SUM(Hour(Fields!total_service_time.Value)) + FLOOR(SUM(MINUTE(Fields!total_service_time.Value))/60) & ":" & SUM(MINUTE(Fields!total_service_time.Value)) Mod 60

    Screenshots based on edited query - Dataset Replicated Result - as Output

    Edit: Try this expression - 

    =Sum(CInt(MID(Fields!total_service_time.Value,1,INSTR(Fields!total_service_time.Value,":")-1)), "<DataSetName>")
    + Floor(Sum(CInt(Right(Fields!total_service_time.Value,2)), "<DataSetName>") / 60)
    & ":" & Sum(CInt(Right(Fields!total_service_time.Value,2)), "<DataSetName>") Mod 60