Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: [RESOLVED] Convert Elapsed Time Field and Average Post Reply Post New Topic
Author Message
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Topic: [RESOLVED] Convert Elapsed Time Field and Average
    Posted: 10 Aug 2016 at 6:02am
I hope one day to be a contributor to this amazing site, but for now I'm the needy little kid that keeps showing up at the door.

I am working on a report for my department where I am trying to count the number of computer generated incidents and how long it took to get those runs assigned.

I have tried everything I can find on the net but I have not found the correct solution or I'm simply too ignorant to make them work.

The data field {Response_Master_Incident.Elapsed_InQueue_2_FirstAssign} is a string (varchar(10), null). The format is hh:mm:ss but is not an integer field.

I am hoping there is a way I can convert the field into an integer and then add average summaries to all group sections.

Any help would be greatly appreciated.

Thank you,
Kyle

Edited by KCowden - 22 Aug 2016 at 3:00am
There is no situation so terrible that you can't make it worse.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 10 Aug 2016 at 8:17am
I would think converting the string to a number with a formula (i.e. val(left( {Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},2))*3600 +val(mid({Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},3,2))*60 +val(right({Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},2))  )at least it should be a number (in seconds).
IP IP Logged
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Posted: 12 Aug 2016 at 9:29am
Thank you but I'm missing something.

It displays the trailing seconds only. I.E. Original Value is "00:01:13" the displayed value in the formula field "13".

Is there maybe a way to convert the value to integers either when the data is pulled in or after writing to the report?
There is no situation so terrible that you can't make it worse.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 12 Aug 2016 at 1:01pm
I did not test the code.  I miss counted for the mid.
val(left( {Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},2))*3600 +val(mid({Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},4,2))*60 +val(right({Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},2)) 
IP IP Logged
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Posted: 16 Aug 2016 at 2:05am
Okay! Now we're simmering. Thank you very much, it works, gives me seconds and I can apply average summaries.

Now if I can beg one more favor. Is there a way I can get it to display as hh:mm:ss? My first thought is to repeat your formula using the "/" operator vs. the "*" (multiplication) operator. But I am so beyond ignorant on how CR thinks.

Help?..Please?

God Bless,
Kyle
There is no situation so terrible that you can't make it worse.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 16 Aug 2016 at 4:47am
But I thought the original value was in hh:mm:ss?!?  Thus you should be able to just drop that field in where you need it.
IP IP Logged
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Posted: 19 Aug 2016 at 1:19am
The original field was displayed as hh:mm:ss but it was a string (varchar) not integers.

Your solution does convert the field to integers as seconds and allows me to average by seconds but I was hoping to get the integer field (seconds) to display as hh:mm:ss or the average summary to display as hh:mm:ss.

With the first, I could replace the elapsed time field and average off of that. With the second I could average off of the formula field without including that field in the actual report.

You've gotten me a lot closer to a solution but my ignorance has outpaced the answers.

Added in Edit: A thought occurred to me. In the data set I have available two date/time fields. One is when the run enters the queue and one when the first unit is assigned. That time difference equals the Elapsed_InQueue_2_FirstAssign value.

I tried to use a DateDiff function but I cannot get the value to display as hh:mm:ss. What I get is a single integer 0-5 based on how many minutes are between the two times.

If I get to that point I can run the average summary from that value.

Edited by KCowden - 19 Aug 2016 at 2:04am
There is no situation so terrible that you can't make it worse.
IP IP Logged
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Posted: 22 Aug 2016 at 3:13am
Thank you @kevlray for your help! With what you gave me and some other things I've read that were tangentially related, it sorted itself out in a dream (I know sad, right?).

The first thing I did was use a built in TimeDate function to calculate the elapsed time instead of pulling the text string of the elapsed time by using the Time() function in my formula.

I used the DateTime that the first unit was assigned and subtracted the DateTime that the call entered the queue which gave me the total seconds.

Time({Response_Master_Incident.Time_First_Unit_Assigned}) - Time({Response_Master_Incident.Time_CallEnteredQueue})


Then I used a string conversion formula as suggested by @DBlank in another thread to format it to display correctly (Display String in Format Field Dialog):

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(currentfieldvalue / 3600);
// Subtract the hours portion to get RemainingSeconds
RemainingSeconds := currentfieldvalue - (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 := currentfieldvalue - (Hours * 3600) -
(Minutes * 60);
// Format the hours, minutes, and seconds to hh:mm:ss
totext(Hours,"00") + ":" + totext(Minutes,"00") +
":" + totext(Seconds,"00")


And now I can average, total, determine max and min values. Thank you so much for your patience and thank you for making this forum what it is.

Edited by KCowden - 22 Aug 2016 at 3:15am
There is no situation so terrible that you can't make it worse.
IP IP Logged
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.031 seconds.