Skip to Content
avatar image
Former Member

How to find the SUM of TIME Fields

Hello Experts,

#########################

1. Introduction:

I am trying to make Crystal Reports add several HH:MM:SS TIME fields together so that I can get the SUM.

The name of the table is ChargesTable and the column is TIMESPENT.

So that's: ChargesTable.TIMESPENT.

The database saves time in milliseconds, so I have used a Formula called "Time Spent"...

Time({ChargesTable.TIMESPENT}/86400000)

...to convert it into a HH:MM:SS format i.e. 01:00:00

If I don't use the "Time Spent" formula, the times are displayed as number i.e 3600000

#########################

2. Previous Attempts:

I have tried to Insert > Summary > Choose field to summarize...

But! The TimeSpent formula does not provide a SUM option.

Image 1:

#########################

3. Formulas:

So, I created two Formulas in an attempt to summarize the fields in another way.

Formula 1:

TimeVar t:= Time({ChargesTable.TIMESPENT});

(Hour(t) * 3600) + (Minute(t) * 60) + Second(t);

Formula 2:

// '@Formula 1' is the formula created in the 1st formula. 
// {AaaUser.FIRST_NAME} is the field used to Group.

NumberVar fin_t   := Sum({@Formula 1}, ({AaaUser.FIRST_NAME})); 

NumberVar Hours   := Truncate (fin_t / 3600);

NumberVar Minutes := Truncate (Remainder (fin_t, 3600) / 60);

NumberVar Seconds := Remainder (fin_t, 60) ;

Time(Hours, Minutes, Seconds)

Then I place the "Formula 2" field into the report.

And...it does nothing?

Image 2: (The 'Formula 2' is bold and green) -

What is going wrong?

Apologies for any obvious mistakes; I am fairly new to Crystal.

If you need more pictures or information, do not hesitate to ask :)

Thanks.

Gareth.


image-1.png (27.0 kB)
image-2.png (7.4 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Feb 09, 2017 at 09:52 AM

    Hi Gareth,

    What happens if you just have Formula3 do:

    Sum ({ChargesTable.TIMESPENT}, {AaaUser.FIRST_NAME});

    I would like to see what comes up. If it's still 0 then I suspect we're running into NULLs. If this is the case, go to File | Report I Options and put a check in Convert NULL to default values. Then see if Formual3 returns a number. If it does then try converting it to a time.

    If you get a number then we'll have to find out why we can't convert the number into a time.

    Brian Dong

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 07, 2017 at 09:22 PM

    Hi Gareth,

    I believe you have the right logic but just need to tune it a bit. Instead of converting the field to a time first, sum the TIMESPENT field then convert the summed amount to a time.

    So leave your Formula 1 as is so we can see the detail time. Your second formula would be something like:

    NumberVar TotTime; 
    
    TotTime := Sum ({ChargesTable.TIMESPENT}, {ChargesTable.WORKORDERID}); 
    (Hour(TotTime)*3600)+(Minute(TotTime)*60)+ Second(TotTime);

    This assumes you have a group on WORKORDERID. So the TIMESPENT is summed for each WORKORDERID and converted to a time afterwards.

    I hope this helps,
    Brian

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Brian,

      The report Groups on Technician Names; as the point of the report is to see how many hours of work each technician has done.

      Image 1:

      However, if I change the 'Group' to {AaaUser.FIRST_NAME}...

      NumberVar TotTime; 
      
      TotTime := Sum ({ChargesTable.TIMESPENT}, {AaaUser.FIRST_NAME}); //Group on the name of the technician 
      (Hour(TotTime) * 3600) + (Minute(TotTime) * 60) + Second(TotTime);
      

      I get an error:

      Image 2:

      If I change the code to:

      NumberVar TotTime; 
      
      TotTime := Sum ({ChargesTable.TIMESPENT}, {AaaUser.FIRST_NAME}); //Group on the name of the technician 
      Time(TotTime);

      The total time is still 00:00:00?

      Regards,

      Gareth

      image-1.png (21.9 kB)
      image-2-error.png (14.6 kB)
  • Feb 08, 2017 at 03:48 PM

    Hi Gareth,

    Create a new formula with this code:

    Sum({@Formula 1},{AaaUser.FIRST_NAME})

    Place this on the Group Footer - does it display anything at all?

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Brian Dong

      Hi Brian,

      The above formula worked!

      (I then converted the NUMBER value into a TIME datatype as recommended.)

      Image 5:

      Formula 3 - Code:

      NumberVar s; 
      
      s := Sum ({ChargesTable.TIMESPENT}, {AaaUser.FIRST_NAME});
      
      // The database saves time values in milliseconds, so the data needs to be divided by '86400000' first. 
      
      Time(s / 86400000); 
      

      Thanks for your help - much appreciated :D

      Regards,

      Gareth

      image-5.png (5.1 kB)
  • Feb 09, 2017 at 08:16 AM

    I also suspect it's NULLS.

    As Brian said, select 'Default Values for NULLS' and see if it helps.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded