CODEWITHSUNDEEP
reporting-servicestime
Rich Travaglini
Rich Travaglini

Reputation: 1

SSRS convert decimal seconds to hh:mm:ss

I am trying to do an average call time calcualtion in SSRS at different group levels. I'm using the formula below to calculate averages for IBTOTALTALK, IBTOTALHOLD, IBTOTALWRAP. 

=Iif(IsNothing(SUM(Fields!CALLS_HANDLED.Value))="True",0,
 Iif(SUM(Fields!CALLS_HANDLED.Value)=0,0,
 Format(DateAdd("s",Sum(Fields!IBTOTALTALK.Value) / Sum(Fields!CALLS_HANDLED.Value), "00:00:00"), "HH:mm:ss")))

I then calculate an overall Call Handle TIme using this formula: 

=Iif(IsNothing(SUM(Fields!CALLS_HANDLED.Value))="True",0,
 Iif(SUM(Fields!CALLS_HANDLED.Value)=0,0,
 Format(DateAdd("s",(Sum(Fields!IBTOTALTALK.Value)+Sum(Fields!IBTOTALHOLD.Value)+Sum(Fields!IBTOTALWRAP.Value))
 / Sum(Fields!CALLS_HANDLED.Value), "00:00:00"), "HH:mm:ss")))

When I export the results to Excel, the Call Handle Time value is correct, but if you try to add up the three averages in Excel, it is off by 2-3 seconds, and the client wants it to be exact. It appears that the values are exported as or converted to integer in Excel.

CORRECT VALUES 

AVG TALK               AVG HOLD            AVG WRAP            AVG CHT
 416.706522      36.489130   43.032609   496.228261 
 0:06:57               0:00:36            0:00:43              0:08:16

WHAT IS DISPLAYED 

AVG TALK               AVG HOLD            AVG WRAP            SUM TOTAL
  416              36              43              495 
  0:06:56              0:00:36             0:00:43             0:08:15

Is there any way to export a more precise time value so that they add up to the correct result in Excel? I've tried a number of different time calculation formulas in SSRS and the one above delivers the most consistent result without producing an #ERROR message under certain conditions. Any help would be appreciated.

Upvotes: 0

Views: 2226

Answers (1)

Tak
Tak

Reputation: 1562

The DateAdd function does not round for you and it looks like you want to round up from milliseconds to seconds. Use the round function ..

format(dateadd("s", Round(Fields!IBTOTALTALK.Value / Fields!CALLS_HANDLED.Value, 0, system.MidpointRounding.ToEven) , "00:00:00"), "HH:mm:ss")

Upvotes: 1

Related Questions