Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1882
  • Last Modified:

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,
0
jaisy99
Asked:
jaisy99
  • 2
2 Solutions
 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now