SSS在HHMMSS中的总时间(SSRS Total Time in HHMMSS)

SSS在HHMMSS中的总时间(SSRS Total Time in HHMMSS)

从上图中，我总结了HHMMSS列是SSRS的，具有以下表达式：

=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)

如果列中没有空白但在没有值或空白时给出错误，则效果很好。 我该如何克服这个问题？ 该报告的查询是：

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

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

最满意答案

您正在获取#error因为您尝试使用整数添加varchars（空白/空值）。

而不是空白或NULL使HH:MM:SS列为0：0：0（或00:00:00）。 如果您不想在报告中显示，可以使用文本框中的隐藏表达式在报告级别隐藏它。 有了这个改变，SSRS就不需要将varchars与数字混合，你就不会得到#error。

处理此问题的其他方法是在SQL查询本身中处理它。

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 ....

分区列取决于您如何基于empid, jobid和其他字段对数据进行分区。 现在您有了以秒为单位的总时间，您可以在SSRS或查询中进行转换。

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.