CODEWITHSUNDEEP
sqlstored-proceduresreporting-servicesformatting
mslhrt
mslhrt

Reputation: 207

SSRS Average Time in Minutes and then Format to HH MM

I've seen a lot of questions about average time in SSRS but I'm pretty new to this and none of these questions address my specific need so any help would be appreciated.

I have a duration field (Fields!Duration1.Value) which is pulling in a duration in minutes (example: 170). In my stored procedure I'm converting this to HH MM like so:

CONVERT(VARCHAR(5),DATEADD(minute, test.Duration,0) ,114) AS Duration1

This is working like a charm and I'm using it in my SSRS report. Unfortunately, I also have need of getting an average duration. So I threw in the original field test.Duration as an extra SELECT in my stored procedure. The report is set up as a matrix and so it is the "total" line where I want to have the average displayed.

So I'm pretty sure I need to first:

=Avg(Fields!test.Duration.Value)

Which works (but has a ridiculous decimal value), but how do I then Format that value back to HH:MM??? I'm sure I could do this in my stored procedure but I'm not positive on how to do it there either so basically: any and all help welcome!

Thanks

Upvotes: 1

Views: 1936

Answers (1)

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

You could add your own formatting function to the Report Code section:

Public Function MinsToHHMM (ByVal Minutes As Decimal)
  Dim HourString = Floor(Minutes/60).ToString() 
  Dim MinString = Floor(Minutes Mod 60).ToString()
  Return HourString.PadLeft(2, "0") & ":" & MinString.PadLeft(2, "0")
End Function

and then call it in the cell expression like this:

=Code.MinsToHHMM(Avg(Fields!test.Duration.Value))

Upvotes: 1

Related Questions