SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


AVG time (hh:mm:ss) in SSRS


AVG time (hh:mm:ss) in SSRS

Author
Message
DaveDB
DaveDB
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 215
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26444 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88090 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4176 Visits: 7865
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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Jeff Moden
    Jeff Moden
    SSC Guru
    SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

    Group: General Forum Members
    Points: 88090 Visits: 41128
    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.
    If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    mister.magoo
    mister.magoo
    SSCarpal Tunnel
    SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

    Group: General Forum Members
    Points: 4176 Visits: 7865
    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 :-P

    MM


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




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • DaveDB
    DaveDB
    SSC-Enthusiastic
    SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

    Group: General Forum Members
    Points: 159 Visits: 215
    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
    DaveDB
    SSC-Enthusiastic
    SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

    Group: General Forum Members
    Points: 159 Visits: 215
    Thanks Jeff Smile
    Sean Lange
    Sean Lange
    One Orange Chip
    One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

    Group: General Forum Members
    Points: 26444 Visits: 17557
    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.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
    Understanding and Using APPLY (Part 1)
    Understanding and Using APPLY (Part 2)
    Jeff Moden
    Jeff Moden
    SSC Guru
    SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

    Group: General Forum Members
    Points: 88090 Visits: 41128
    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.
    If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search