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.
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.
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
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
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
Hi Abhilash,
Here are the results of placing your new formula ('Formula 3') on the report:
Image 3:
Image 4:
Regards,
Gareth
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
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
I also suspect it's NULLS.
As Brian said, select 'Default Values for NULLS' and see if it helps.
-Abhilash
Hi Abhilash,
The formula works now.
Thanks for all your help :)
Regards,
Gareth