Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Seconds to Hour formula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Topic: Seconds to Hour formula
    Posted: 29 Oct 2012 at 3:20am
Hi guys,
Having some issues displaying my seconds to hour formula correctly.  When I goto the cross tab my hourly formula can only be displayed as a count which I already have a count formula.  When I add this formula into a row or column it shows every second for the customer.  It looks something like this.
 
Phyllis       00:00:00       645
                                      0.00
 
                 00:00:01        25
                                       25.00
                   
                 00:00:02        30
                                       60.00
 
However, when I take the ConvertToHours formula out of the column it displays like this.
 
Phyllis                              1,268 (Count of the report)
                                        6,790 (Total Seconds)
 
This should be correct I just need to seconds converted to hours.
 
Here is my formula:

 local numbervar RemainingSeconds;
local numbervar Hours ;
local numbervar Minutes;
local numbervar Seconds;
 
//divide the @TotalSeconds by 3600 to calculate
// hours. Use truncate to remove the decimal portion.
Hours := truncate({@TotalSeconds} / 3600);

// Subtract the hours portion to get RemainingSeconds
RemainingSeconds := {@TotalSeconds} - (Hours *
3600);
 
// Divide RemainingSeconds by 60 to get minutes.
// Use truncate to remove the decimal portion.
Minutes := truncate(RemainingSeconds/60);
 
// Subtract the Hours and Minutes and what is left
// over is seconds.
Seconds := {@TotalSeconds} - (Hours * 3600) -
(Minutes * 60);
 
// Format the hours, minutes, and seconds to hh:mm:ss
totext(Hours,"00") + ":" + totext(Minutes,"00") +
":" + totext(Seconds,"00")
 
Thanks for all the help in advance!
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Oct 2012 at 3:55am
once the seconds are converted to a string they can no longer be summed.
instead leave them as seconds (numeric) so they can be summed. Use a display string formula on the summarized fields (even in the crosstab) to change the display (but not the actual value)
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 29 Oct 2012 at 4:37am
Thanks for the reply.  I am still not sure how to do a display string formula to calculate this.  When I right click and format fields I only have a number criteria to change and not able to change it to a hh::mm:ss format
 
 
 
Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Oct 2012 at 4:40am
right click on the field
select format field
select comman tab
mid way there is a "Display string" option
click on the formula button
add your display formula here


Edited by DBlank - 29 Oct 2012 at 4:41am
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 29 Oct 2012 at 5:20am

select format field

select common tab
mid way there is a "Display string" option
click on the formula button
add your display formula here

After I did this I added the formula {@ConvertToHours} for the display string formula.  But, the data shows up as 00:00:00 and it is not converting the seconds.  What am I doing wrong?

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Oct 2012 at 5:27am
sorry,
you have to alter the display formula to use
currentfieldvalue
in place of {@totalseconds}
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 29 Oct 2012 at 5:37am
So in my ConvertToHours formula I need to change @TotalSeconds to
currentfieldvalue.
 
It should be:
Hours:=truncate(currentfieldvalue/3600);
RemainingSeconds:= currentfieldvalue - (Hours*3600);
Minutes:=truncate(RemaininSeconds/60);
Seconds:=currentfieldvalue - (Hours *3600)-(Minutes*60);
 
Totext (Hours, "00") + ":" + totext(Minutes, "00") + ":" + totext(Seconds, "00")
 
When I add this in the replace of @TotalSeconds in the formula I get an error saying "This function can only be used in field formatting formulas."
 
Any ideas?
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 29 Oct 2012 at 5:43am
Nevermind this worked out perfectly.  Thank you so much for the help.  I was changing the old ConvertToSeconds formula to change the currentfieldvalue from TotalSeconds when I just need to create the display string formula and copy and paste my old formula in there and update with the new field currentfieldvalue.
 
Thanks again!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Oct 2012 at 5:47am
glad you got it.
the currentfieldvalue in a display string can save you a ton of work converting things back and forth between summarys to end user display needs.
You can also use the Format Painter to copy the display formula from field to field. Since it uses currentfieldvalue you usually don't have to worry about altering it.
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 29 Oct 2012 at 6:09am
Good Tip.. I think ive noticed something wrong with my Total Seconds formula and I want to run it passed an expert if I could.  Its part of a multiple formula report so I think I am not calculating it right.
 
//Starttime Formula
DateTimeVar StartTime;
If {Reports_Events.Type} = 'start' then StartTime := {Reports_Events.Timestamp};
StartTime
 
 
//Duration Formula
If {Reports_Events.Type} = 'Stop' Then DateDiff('s',{Reports_Events.Timestamp}, {@startTime})
 
//Convert to positive.  For some reason some of my numbers were negative when the duration all should be positive.
if {@duration} < 0 then ({@duration} *-1);
 
//TotalSeconds formula
local NumberVar TotalSecs;
TotalSecs:= {@ConvertToPositive}
 
My cross tab is pulling the sum of TotalSeconds.  I think that it could only be pulling the negative numbers converted to a positive.  Is there a formula I can use to Add the negative numbers after they have been converted and the duration that are already positive?
 
Thanks!
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.030 seconds.