将构建器表达式报告给SUM HH：MM：SS

I am very new to SQL Server and SSRS and trying to create a report using a Matrix table within report builder.

我是SQL Server和SSRS的新手,并尝试使用报表生成器中的Matrix表创建报表。

I want to sum the HH:MM:SS spent per job, per engineer, per day e.g. job 1 is 00:30:00, job 2 is 01:00:00 and job 3 is 02:45:00 so the total would be 04:15:00.

我想总结HH:MM:每个工作每个工作每天花费的SS,例如:工作1是00:30:00,工作2是01:00:00,工作3是02:45:00所以总计是04:15:00。

I can use the query to produce the data and pull it together in an excel pivot table but not in a matrix table.

我可以使用查询生成数据并将其一起放在excel数据透视表中,但不能放在矩阵表中。

Thanks - Andy

谢谢 - 安迪

1 个解决方案

#1

I just created a new report and put a single textbox in it.

我刚刚创建了一个新报告,并在其中放入了一个文本框。

In the expression for the value of the textbox, I hard-coded this version of your expression:

在文本框值的表达式中,我对您的表达式的这个版本进行了硬编码:

=int(99999/3600) & ":" & int((99999 Mod 3600)/60) & ":" & (99999 Mod 3600) Mod 60

And when I preview the report, I see:

当我预览报告时,我看到:

27:46:39

So I know this works. The format of my textbox is blank.

所以我知道这很有效。我的文本框格式为空白。

If you are stuck with SQL that returns a time value in minutes then you can still use this as an expression:

如果你坚持使用几分钟返回时间值的SQL,那么你仍然可以将它用作表达式:

=int(sum(Fields!SMV_Time.Value * 60)/3600) & ":"
 & int((sum(Fields!SMV_Time.Value * 60) Mod 3600)/60) & ":"
 & (sum(Fields!SMV_Time.Value * 60) Mod 3600) Mod 60

Again, leave the format of the textbox blank, and you should get the desired results.

再次,将文本框的格式留空,您应该得到所需的结果。

#1