SSRS: Summing TimeSpan values in a report

Question

I have a report and a datasource where one of the columns are of type TimeSpan. The TimeSpan value appears to display correctly in the report when I use Fields!TheTime.Value, no problem there.

• 07:02:00
• 05:41:00

But I would like to do a Sum on those values to get the total time of a group. In C# and such I can of course do a TimeSpan + another TimeSpan, so I know they can be added. I tried

=Sum(Fields!TheTime.Value)

But it ends up printing out as a long number of some sort. For example for the outputted times above, I would get 457800000000 as the sum. And what is that even supposed to be?

Anyways, how can I sum timespan values in a report? For the above timespans I would like to end up with 12:43:00 as the sum. Unless my head failed me at math once again... but you get the idea :p

Answer 1

What is probably happening is that when you display Fields!TheTime.Value, SSRS is smart enough to know to display that as a DateTime type field

when you add the sum in there it thinks it is a numeric type field and displays it as such (ie, it is summing the number of "ticks" in each timespan field)

try specifically formatting the summed value as a datetime in the field properties and it will probably show correctly

Answer 2

@Svish - I deleted my previous post because I had a fit uncertainty about my answer but I concur with @pfunk.

I finally got SSRS back up and had a play around and it certainly looks like your big number is the number of ticks so it looks like a bit of formatting of the result will work for you.

Interestingly enough my previous convoluted answer was a workaround for summing DateTime values (using SQL Server DATETIME datatype in my query) which you cannot do in SSRS (and SQL) because you cant sum a DATETIME. I'll include it here again for future reference but I think was on a bit of a tangent earlier :)

The below code converts a DateTime field into a double, sums the result and then converts it back to DateTime and formats it for hh:mm:ss

=Date.FromOADate(Sum(Fields!TheTime.Value.ToOADate())).ToString("hh:mm:ss")

Answer 3

sigh The solution annoyingly simple... Why couldn't I just have tried that in the first place? Oh well... maybe because I didn't realise I had access to TimeSpan class... maybe because I had thought myself blind... But anyways, here it is:

=TimeSpan.FromTicks(Sum(Fields!TheTime.Value))

D'oh!