CODEWITHSUNDEEP
sql-serverreporting-services
Will F
Will F

Reputation: 437

Total of minutes in SSRS

I am creating a report in SSRS that shows the duration of phone calls.

In my T-SQL script I am using: 

CONVERT(VARCHAR,DATEADD(second,Call,0),108)AS[Call Duration]

which works nicely and shows the time as 00:03:20, for example.

However when I create a table in SSRS and try to sum all the different time values it just says #error in the report. I need the report to be able to add these time values up so I can give a total per switchboard operator. So for example if officer x took three calls and they all lasted 3 minutes then I'd need the total to say 00:09:00

Do you know of a way where I can display the total time spend rather than having to list each value separately? I can sum up the number of seconds for each call - so for example get a total of 540 seconds - but need to show this as hh:mm:ss

Thanks

Upvotes: 0

Views: 482

Answers (1)

iamdave
iamdave

Reputation: 12243

The report is throwing an error because you are trying to sum up a varchar value. Rather than trying to format your data in your SQL query, simply return the values in their raw form to your SSRS report and let your presentation layer format the data for you.

Rather than using a dateadd, it seems your call length is already held within your Call column? If that is the case, simply return that column to your report, either as detail rows to be summed if the detail is required elsewhere in your report, or pre-aggregated in your SQL as this will perform better.

You can then format your Call Duration as follows:

=format(today().AddSeconds(Fields!Call.Value),"HH:mm:ss")

If you aren't aggregating your call seconds in your SQL query, you will need to do this in your expression:

=format(today().AddSeconds(sum(Fields!Call.Value)),"HH:mm:ss")

Obviously this method assumes you won't have any calls longer than 24 hours. If that is a possibility, you will need to calculate the hours, minutes and seconds to be concatenated together.

Upvotes: 1

Related Questions