Skip to Content
0

How to find the SUM of TIME Fields

Feb 07, 2017 at 05:43 PM

56

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Gareth Davies Feb 09, 2017 at 09:52 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Brian Dong Feb 07, 2017 at 09:22 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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)
0
Abhilash Kumar
Feb 08, 2017 at 03:48 PM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Abhilash,

Here are the results of placing your new formula ('Formula 3') on the report:

Image 3:

Image 4:

Regards,

Gareth

image-3.png (7.9 kB)
image-4.png (18.4 kB)
0

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

1

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)
0
Abhilash Kumar
Feb 09, 2017 at 08:16 AM
0

I also suspect it's NULLS.

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

-Abhilash

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Abhilash,

The formula works now.

Thanks for all your help :)

Regards,

Gareth

0