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

Arthromere

As you currectly inferred below, the summed value is the "tick" value. You could also see this by calculating the number of seconds in the times above: 07:02:00 = 25320s and 05:41:00 = 20460s. 25320 + 20460 = 45780. – Regina

Answer 1

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!

Arthromere

I needed to prefix with 'System' - eg. =System.TimeSpan.FromTicks(Sum(Fields!Hours.Value)) – Jarman

@David: Then you probably didn't have using System; in your code file – Arthromere

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

Pashm

Where do I find those Date.FromOADate and ToOADate() functions? The code doesn't work. Makes a red underline in visual studio, although I can compile. But when I run it, I just get #Error in those fields. – Arthromere

The FromOADate and ToOADate are methods of the DateTime class not the TimeSpan class and I reckon yours was TimeSpan. Good news on your solution - don't you just love it when it's a simple answer :) – Pashm

Answer 3

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

Dedication