Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-05
5
Medium Priority
?
1,799 Views
Last Modified: 2014-01-06
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
Comment
Question by:jaisy99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 2

Assisted Solution

by:burdit
burdit earned 2000 total points
ID: 39625617
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
 
LVL 1

Author Comment

by:jaisy99
ID: 39625721
I have limited access to the report builder 3.0 only.
0
 
LVL 1

Accepted Solution

by:
jaisy99 earned 0 total points
ID: 39625915
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

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
How to increase the row limit in Jasper Server.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question