AVG time (hh:mm:ss) in SSRS

Question

AVG time (hh:mm:ss) in SSRS

DaveDB

SSCrazy

Points: 2173
More actions
October 17, 2013 at 2:54 pm

#278981

Hello all,
I have to say that once again Microsoft has fallen short with their attempt at creating a reporting tool. I have spend the past 2 days trying to figure out how to AVG time in my report. A simple column that is formatted HH:MM:SS that just need to be AVG. This is ridiculous! It turns out that SSRS is not capable of doing this! I can write a .NET script to accomplish this (I think), but I am not a web developer. No one on the forums seems to either know how to do this OR has a workaround for it. All I am trying to do is AVG time, which should be basic functionality in any report. Does ANYONE have a workaround for this? Can someone point me in the right direction? I have been with my company for 2 months and need to impress, but once again Microsoft's shortcomings make everything difficult. But they are making money so that is Microsoft's bottom line, not creating a quality product. Damn cash cow!
Please help and pardon my rant.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

DaveDB (10/17/2013)
Hello all,
I have to say that once again Microsoft has fallen short with their attempt at creating a reporting tool. I have spend the past 2 days trying to figure out how to AVG time in my report. A simple column that is formatted HH:MM:SS that just need to be AVG. This is ridiculous! It turns out that SSRS is not capable of doing this! I can write a .NET script to accomplish this (I think), but I am not a web developer. No one on the forums seems to either know how to do this OR has a workaround for it. All I am trying to do is AVG time, which should be basic functionality in any report. Does ANYONE have a workaround for this? Can someone point me in the right direction? I have been with my company for 2 months and need to impress, but once again Microsoft's shortcomings make everything difficult. But they are making money so that is Microsoft's bottom line, not creating a quality product. Damn cash cow!
Please help and pardon my rant.

I understand the frustration but consider what you are asking. You have a time column. How do you average that? Remember that a time datatype is a point in time, not a sum of hours, minutes and seconds. Remember what an average is. It is the sum of all values divided by the number of values. How do you do that with a time of day?

1pm + 3:10pm = ???

Now if you said you wanted the average time I assume that you would want something like 2:05 from the above?

You can achieve this with some date math but you will have to do this in sql, not in SSRS.

See if something like this might help.

create table #Something

(

SomeValue time

)

insert #Something

select '01:00:00.000' union all

select '03:10:00.000'

select dateadd(ms, avg(datediff(ms, 0, SomeValue)), 0)

from #Something

drop table #Something

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

DaveDB (10/17/2013)
Hello all,
I have to say that once again Microsoft has fallen short with their attempt at creating a reporting tool. I have spend the past 2 days trying to figure out how to AVG time in my report. A simple column that is formatted HH:MM:SS that just need to be AVG. This is ridiculous! It turns out that SSRS is not capable of doing this! I can write a .NET script to accomplish this (I think), but I am not a web developer. No one on the forums seems to either know how to do this OR has a workaround for it. All I am trying to do is AVG time, which should be basic functionality in any report. Does ANYONE have a workaround for this? Can someone point me in the right direction? I have been with my company for 2 months and need to impress, but once again Microsoft's shortcomings make everything difficult. But they are making money so that is Microsoft's bottom line, not creating a quality product. Damn cash cow!
Please help and pardon my rant.

Convert the time to float, average it, then convert back to time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

DaveDB (10/17/2013)
Hello all,
I have to say that once again Microsoft has fallen short with their attempt at creating a reporting tool. I have spend the past 2 days trying to figure out how to AVG time in my report. A simple column that is formatted HH:MM:SS that just need to be AVG. This is ridiculous! It turns out that SSRS is not capable of doing this! I can write a .NET script to accomplish this (I think), but I am not a web developer. No one on the forums seems to either know how to do this OR has a workaround for it. All I am trying to do is AVG time, which should be basic functionality in any report. Does ANYONE have a workaround for this? Can someone point me in the right direction? I have been with my company for 2 months and need to impress, but once again Microsoft's shortcomings make everything difficult. But they are making money so that is Microsoft's bottom line, not creating a quality product. Damn cash cow!
Please help and pardon my rant.

Dave, you can do it, but maybe in your frustration you are not asking your question as clearly as you could.

As Sean pointed out, Time Of Day is not something you can average, so perhaps you are talking about a duration column, which has been formatted to look like a time?

What data type is the column that holds this value? Is the data representing a duration?

Perhaps as Jeff suggests, you just want to take the underlying, unformatted values and convert them into seconds (perhaps) and take an average of that value, then format the result to look like a time?

SSRS can be quirky, but I have yet to find a report that cannot be built...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

mister.magoo (10/17/2013)
...Time Of Day is not something you can average...

Gosh... I have to disagree with that statement that you and Sean made. What if the task is to find the average time of day when someone gets to work, for example?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

Jeff Moden (10/17/2013)
mister.magoo (10/17/2013)
...Time Of Day is not something you can average...
Gosh... I have to disagree with that statement that you and Sean made. What if the task is to find the average time of day when someone gets to work, for example?

...Ok, you got me there 🙂 What I should have said is "Time of Day is not something you should average...Power To The People :hehe:

I guess I have to agree that you could want to average Time of Day in that way, but I would strongly argue that the duration of the variance from what is considered to be "normal" in such cases would be a more useful measure than just a time.

None of this sidebar, however, changes my (and your) main argument, which is that this is definitely possible, so hopefully DaveDB will come back feeling refreshed and we can help him to sort this problem and look good in the new job 😛

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

DaveDB SSCrazy Points: 2173 More actions · Answer 6

Thank you all very much for your responses and encouragement. I know how to do this in SQL, I was just hoping that SSRS has a faster and easier way perhaps through the reporting code. Is that Excel code? VBA?

DaveDB SSCrazy Points: 2173 More actions · Answer 7

DaveDB

SSCrazy

Points: 2173

October 18, 2013 at 7:05 am

#1659695

Thanks Jeff 🙂

Sean Lange SSC Guru Points: 286573 More actions · Answer 8

Jeff Moden (10/17/2013)
mister.magoo (10/17/2013)
...Time Of Day is not something you can average...
Gosh... I have to disagree with that statement that you and Sean made. What if the task is to find the average time of day when someone gets to work, for example?

Perhaps I was not clear enough in my statement. You can't calculate the average time of day like you would other values. I do believe that the calculation I posted does in fact calculate exactly what you are saying here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Sean Lange (10/18/2013)
Jeff Moden (10/17/2013)
mister.magoo (10/17/2013)
...Time Of Day is not something you can average...
Gosh... I have to disagree with that statement that you and Sean made. What if the task is to find the average time of day when someone gets to work, for example?
Perhaps I was not clear enough in my statement. You can't calculate the average time of day like you would other values. I do believe that the calculation I posted does in fact calculate exactly what you are saying here.

Understood. That just makes it sound to the casual reader like it can't be done and wanted to make sure that people understood that it can be done but not directly. Your good code does, in fact, calculate the average using that very method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 10

DaveDB (10/18/2013)
Thank you all very much for your responses and encouragement. I know how to do this in SQL, I was just hoping that SSRS has a faster and easier way perhaps through the reporting code. Is that Excel code? VBA?

mister.magoo (10/17/2013)
What data type is the column that holds this value?

Without an answer to this question, it is impossible to provide a specific answer, but in SSRS, you could do this if the column is a "Date" type:

Add an expression to your "Average Time" :

=DateAdd(DateInterval.Second,AVG(

Hour(Fields!MyDate.Value)*3600

+

Minute(Fields!MyDate.Value)*60

+

Second(Fields!MyDate.Value)

),Today)

If the column is actually a string that has been formatted in the data source like HH:MM:SS then you really should take that formatting out of the data source to avoid exactly this sort of problem...however, if that is not an option - you can just convert it back to a date using the TimeValue function, then perform the above calculation in an expression.

There is no need for any "report code" or special handling, just get the data type right and work with it...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);