How to use the average function on HH:MM:SS fields in Report Builder 3.0

I have a field in HH:MM:SS format and I would like to add an average at the bottom of my table report.  I receive an error when attempting to use AVG in a calculated field.  If I try to use the matrix report option, I loose my HH:MM:SS formatting and basic format options won't return it.  Any suggestions are greatly appreciated.

Thank you,
LVL 1
jaisy99Asked:
Who is Participating?
 
jaisy99Author Commented:
Thank you for the suggestion.  Unfortunately, I don't have access to the SQL side of things, so I couldn't use it exactly, but here is a similar solution that worked in case others find it helpful.

First converted the initial field to seconds by adding a calculated field.
=DateDiff(DateInterval.Second,CDate(Fields!MyfieldName1.Value),CDate(Fields!MyfieldName2.Value))

Second, averaged and reformatted it with the following expression
=Format(DateAdd("s",Avg(Fields!MyFieldName3.Value), "00:00:00"), "HH:mm:ss")
0
 
burditCommented:
Two parts:
First on your sql side convert datetime into seconds
select datepart(hh,'00:48:38')*3600 + datepart(mi,'00:48:38') * 60 + datepart(ss,'00:48:38')

then in your report convert it back into datetime
CONVERT(datetime, DATEADD(ms, dateTimeInSeconds, 0))

this way you can average out the seconds and then convert it back into a date time
CONVERT(datetime, DATEADD(ms, AVG(dateTimeInSeconds), 0))
0
 
jaisy99Author Commented:
I have limited access to the report builder 3.0 only.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.