Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

AVG time (hh:mm:ss) in SSRS Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 2:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, 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.

Post #1505916
Posted Thursday, October 17, 2013 3:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 12,928, Visits: 12,346
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 Moden's 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)
Post #1505928
Posted Thursday, October 17, 2013 5:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1505951
Posted Thursday, October 17, 2013 6:26 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 1,778, Visits: 5,730
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1505954
    Posted Thursday, October 17, 2013 9:38 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 12:42 PM
    Points: 35,216, Visits: 31,672
    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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1505966
    Posted Friday, October 18, 2013 1:06 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 3:47 PM
    Points: 1,778, Visits: 5,730
    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

    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1505998
    Posted Friday, October 18, 2013 6:39 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Monday, February 24, 2014 11:06 AM
    Points: 109, 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?
    Post #1506123
    Posted Friday, October 18, 2013 7:05 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Monday, February 24, 2014 11:06 AM
    Points: 109, Visits: 215
    Thanks Jeff :)
    Post #1506141
    Posted Friday, October 18, 2013 7:45 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 1:52 PM
    Points: 12,928, Visits: 12,346
    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 Moden's 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)
    Post #1506167
    Posted Saturday, October 19, 2013 5:12 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 12:42 PM
    Points: 35,216, Visits: 31,672
    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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1506422
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse