locked
AVG time in Reporting Services RRS feed

  • Question

  • Hello,

    I am new to SSRS and need to AVG a column that has only time in the following format (11:05:00). How would I go about doing this? I need to do this in SSRS and NOT T-SQL. I am new to expressions on SSRS, so as you can imagine this is frustrating.

    Thanks


    Dave SQL Developer


    • Edited by DaveDB Wednesday, October 16, 2013 2:41 PM
    Wednesday, October 16, 2013 2:41 PM

Answers

  • Hi Dave,

    Generally, if we want to average a column which has the (11:05:00) format, we can use the following steps to write the expression:

    1. Sum all the values to this time format (11:05:00).
    2. Convert the time format into Second format.
    3. Average the Second format.
    4. Convert the Second format into time format. 

    The following expression which I tested can achieve your requirement:

    =Fix(Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))/3600)&":"&
    
    Fix((Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))-
    Fix(Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))/3600)*3600)/60)&":"&
    
    Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))-
    Fix(Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))/3600)*3600-
    (Fix((Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))-
    Fix(Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))/3600)*3600)/60))*60

    If there are any misunderstanding, please elaborate the issue for further investigation.

    Thanks,
    Katherine Xiong


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Friday, October 18, 2013 6:33 AM

All replies

  • Folks,

    I have a hard deadline today. Can someone please help?

    Thanks


    Dave SQL Developer

    Wednesday, October 16, 2013 3:01 PM
  • Hi Dave,

    Generally, if we want to average a column which has the (11:05:00) format, we can use the following steps to write the expression:

    1. Sum all the values to this time format (11:05:00).
    2. Convert the time format into Second format.
    3. Average the Second format.
    4. Convert the Second format into time format. 

    The following expression which I tested can achieve your requirement:

    =Fix(Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))/3600)&":"&
    
    Fix((Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))-
    Fix(Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))/3600)*3600)/60)&":"&
    
    Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))-
    Fix(Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))/3600)*3600-
    (Fix((Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))-
    Fix(Cint(Cint((Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(0))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(1)))*60+Cint(split((SUM(Cint(Split(Fields!col1.Value,":").GetValue(0))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(1))).ToString + ":" + SUM(Cint(Split(Fields!col1.Value,":").GetValue(2))).ToString),":")(2)))/Count(Fields!col1.Value))/3600)*3600)/60))*60

    If there are any misunderstanding, please elaborate the issue for further investigation.

    Thanks,
    Katherine Xiong


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Friday, October 18, 2013 6:33 AM