CODEWITHSUNDEEP
reporting-services
Lukas Vecerek
Lukas Vecerek

Reputation: 11

SSRS Data Type Conversion issue | Tough one

I have been working on a SSRS project and ran into several issues. I am trying to move averages and team averages on integers and then convert it to a string.

Please see the picture below. I am getting weird results. The issue is that when I go over 60 minutes, I do not get 01:01:00 but I get 61:00. I have tried public functions such as :

     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 

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

I am not getting the right conversion. I am trying to get the hh : mm : ss format.

Anybody knows how to modify the code to get the result I want?

enter image description here

Thanks a lot!

Lukas

Upvotes: 1

Views: 65

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

I think you don't need to use custom code for that, if your field containing minutes information is set to an aggregable data type you can calculate the average in SSRS and format it to hh:mm:ss as follows:

=FORMAT(
   DATEADD(DateInterval.Minute,AVG(Fields!Minutes.Value),Cdate("00:00:00")),
   "hh:mm:ss"
 )

Let me know if this helps.

Upvotes: 1

Related Questions