SSRS Total Time in HHMMSS

1
votes

enter image description here

From the image above, I am totalling the column HHMMSS is SSRS with the below expression:

    =CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(0)))
     +sum(CInt(split(Fields!HHMMSS.Value,":")(1)))\60)
       &":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(1)))
      mod 60+sum(CInt(split(Fields!HHMMSS.Value,":")(2)))\60) 
      &":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(2))) mod 60)

It works well if there is no blank in the column but gives an error when there is no value or a blank. How do I overcome this? The query for the report is:

    DECLARE @Table TABLE 
    (
          RowID       INT IDENTITY(1,1) PRIMARY KEY
 , EmpID       INT NOT NULL
 , StartTime   DATETIME NOT NULL DEFAULT('1900/00/00')
 , FinishTime  DATETIME NOT NULL DEFAULT('1900/00/00')
 , JobID       INT NULL
 , IdleTime    INT 
    )

    INSERT INTO @Table(EmpID,StartTime,FinishTime,JobID,IdleTime)
    VALUES (100,'2011-09-08 09:00:03.410','2011-09-08 09:55:18.153',12,1)
         , (100,'2011-09-08 11:55:03.810','2011-09-08 12:30:18.153',12,-1)
         , (101,'2012-10-17 09:19:52.637','2012-10-17 17:19:52.637',15,1)
     , (101,'2012-10-17 12:30:52.637','2012-10-17 13:25:52.637',15,-1)
     , (103,'2012-10-17 09:00:03.410','2012-10-17 16:19:52.637',20,1)
     , (103,'2012-10-17 13:00:03.410','2012-10-17 13:49:52.637',20,-1)
     , (104,'2012-10-17 09:00:03.410','2012-10-17 15:19:52.637',54,1)
     , (104,'2012-10-17 11:00:03.410','2012-10-17 13:19:52.637',54,-1)
     , (105,'2012-10-16 09:00:03.410','2012-10-17 18:19:52.637',56,1)
     , (105,'2012-10-17 13:00:03.410','2012-10-17 14:19:52.637',56,-1)
     , (106,'2012-10-10 09:00:03.310','2012-10-17 15:19:52.637',10,1)
     , (106,'2012-10-17 10:00:03.410','2012-10-17 10:34:52.637',10,-1)
     , (106,'2012-10-17 13:00:03.410','2012-10-17 13:35:52.637',10,-1)
     , (107,'2012-10-17 09:00:03.410','2012-10-17 15:19:52.637',17,1)
     , (108,'2012-10-17 09:00:03.410','2012-10-17 15:19:52.637',19,1)
     , (109,'2012-10-17 09:00:03.410','2012-10-17 18:19:52.637',11,1)
     , (109,'2012-10-17 10:00:03.410','2012-10-17 10:19:52.637',11,-1)
     , (109,'2012-10-17 12:00:03.410','2012-10-17 12:20:52.637',11,-1)
     , (109,'2012-10-18 14:00:03.410','2012-10-18 14:20:08.677',11,-1)
     , (110,'2012-10-17 09:00:03.410','2012-10-17 15:19:52.637',20,1)
         , (101,'2012-10-18 09:19:52.637','2012-10-18 17:19:52.637',15,1)
     , (101,'2012-10-18 12:30:52.637','2012-10-18 13:25:52.637',15,-1)
     , (103,'2012-10-18 09:00:03.410','2012-10-18 16:19:52.637',20,1)
     , (103,'2012-10-18 13:00:03.410','2012-10-18 13:49:52.637',20,-1)
     , (104,'2012-10-18 09:00:03.410','2012-10-18 15:19:52.637',54,1)
     , (104,'2012-10-18 11:00:03.410','2012-10-18 13:19:52.637',54,-1)
     , (105,'2012-10-18 09:00:03.410','2012-10-18 18:19:52.637',56,1)
     , (105,'2012-10-18 13:00:03.410','2012-10-18 14:19:52.637',56,-1)
     , (106,'2012-10-18 09:00:03.310','2012-10-18 15:19:52.637',100,1)
     , (106,'2012-10-18 10:00:03.410','2012-10-18 10:34:52.637',100,-1)
     , (106,'2012-10-18 13:00:03.410','2012-10-18 13:35:52.637',10,-1)
     , (107,'2012-10-18 09:00:03.410','2012-10-18 15:19:52.637',17,1)
     , (108,'2012-10-18 09:00:03.410','2012-10-18 15:19:52.637',19,1)
     , (109,'2012-10-18 09:00:03.410','2012-10-18 18:19:52.637',133,1)
     , (109,'2012-10-18 10:00:03.410','2012-10-18 10:19:52.637',133,-1)
     , (109,'2012-10-18 12:00:03.410','2012-10-18 12:20:52.637',133,-1)
     , (109,'2012-10-18 14:00:03.410','2012-10-18 14:20:08.677',133,-1)
     , (110,'2012-10-18 09:00:03.410','2012-10-18 15:19:52.637',31,1)

     SELECT EmpID
           , CONVERT(VARCHAR(10), StartTime, 103) AS [Date] 
       , CONVERT(VARCHAR(5), StartTime, 108) AS [Time]
       , CONVERT(VARCHAR(10), FinishTime, 103) AS Date_Off 
       , CONVERT(VARCHAR(5), FinishTime, 108) AS Time_Off
       , CASE WHEN IdleTime = -1 THEN '' 
     ELSE CONVERT(VARCHAR(10)
           , DATEADD(SECOND, DATEDIFF(SECOND,StartTime,FinishTime),0), 108)END AS HHMMSS
      , CASE WHEN IdleTime = -1 THEN CONVERT(VARCHAR(10), DATEADD(SECOND, DATEDIFF(SECOND,StartTime,FinishTime),0), 108)
    ELSE '' END AS TrainingTime
    FROM @Table
2
reporting-servicesssrs-2008ssrs-2008-r2reportingservices-2005ssrs-2012
What version of SQL Server you are using 2005/2008/R2/2012? – Anup Agrawal
What version of reporting services you are using? – Anup Agrawal

2 Answers

1
votes

You are getting #error because you are trying to add varchars (blank/nulls) with integers.

Instead of blank or NULL make that HH:MM:SS column as 0:0:0 (or 00:00:00). If you don't want to show in the report you can hide it at the report level using the hide expression in textbox. With this change SSRS will not need to mix varchars with numbers and you won't get #error.

Other way to handle this is handling it in SQL query itself.

SELECT EmpID, [Date], [Time],
CASE WHEN ColumnA = 'Training' THEN '00:00:00' ELSE CONVERT(VARCHAR(10), DATEADD(SECOND, DATEDIFF(SECOND,StartTime,FinishTime),0), 108) END AS HHMMSS
,
SUM(IIF (ColumnA='Training',0,DATEDIFF(SECOND, StartTime,FinishTime))) OVER  (PARTITION BY EmpID, [Date]) AS TotalTime
,CONVERT(VARCHAR(10),
DATEADD(SECOND,
    SUM(IIF (ColumnA='Training',0,DATEDIFF(SECOND, StartTime,FinishTime))) OVER  (PARTITION BY EmpID, [Date])
, 108) AS TotalTimeHHMMSS
    FROM ....
    WHERE ....

Partition Columns depend on how you are partitioning the data based on empid, jobid and the other fields. Now you have the total time in Seconds you can convert it either in SSRS or query.

1
votes

Try to add IIF statement in following:

=iif(IsNothing(Fields!HHMMSS.Value) OR Fields!HHMMSS.Value = "", "",
 CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(0)))
 +sum(CInt(split(Fields!HHMMSS.Value,":")(1)))\60)
   &":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(1)))
  mod 60+sum(CInt(split(Fields!HHMMSS.Value,":")(2)))\60) 
  &":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(2))) mod 60)
)

UPDATE

=iif(Len(Fields!HHMMSS.Value) <= 0, "",
 CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(0)))
 +sum(CInt(split(Fields!HHMMSS.Value,":")(1)))\60)
   &":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(1)))
  mod 60+sum(CInt(split(Fields!HHMMSS.Value,":")(2)))\60) 
  &":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(2))) mod 60)
)

UPDATE 2

=iif(Len(Fields!HHMMSS.Value) <= 0, "",
 CStr(sum(CInt(split(iif(Len(Fields!HHMMSS.Value) <= 0, "", Fields!HHMMSS.Value),":")(0)))
 +sum(CInt(split(iif(Len(Fields!HHMMSS.Value) <= 0, "", Fields!HHMMSS.Value),":")(1)))\60)
   &":"& CStr(sum(CInt(split(iif(Len(Fields!HHMMSS.Value) <= 0, "", Fields!HHMMSS.Value),":")(1)))
  mod 60+sum(CInt(split(iif(Len(Fields!HHMMSS.Value) <= 0, "", Fields!HHMMSS.Value),":")(2)))\60) 
  &":"& CStr(sum(CInt(split(iif(Len(Fields!HHMMSS.Value) <= 0, "", Fields!HHMMSS.Value),":")(2))) mod 60)
)