Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Duration Formula By ID Post Reply Post New Topic
Author Message
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Topic: Duration Formula By ID
     Posted: 25 Feb 2013 at 8:38am
Hi Guys,
I am stumped on a duration report not sure why was wondering if I could get some help?
 
Here are my Formulas:
\\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',{@STartTime} ,{Reports_Events.Timestamp} );
 
I have tried to tie these 2 Start and Stop time together using the Report_Events.Key.  The key is ran for every event and it is the same for the start and the stop time for each event.
 
Sample
Name                Room        TimeStamp                     Type        Duration
PT Bleeding       5020       2/19/2013 7:11:01 AM     Start         0
PT Bleeding       501MS    2/19/2013 1:42:16 PM     Start          0
 
PT Bleeding        5020       2/19/2013 7:14:39 AM    Stop        -23,257.00
PT Bleeding         501MS    2/19/2013 1:43:54 PM    Stop          98.00
 
The duration is in seconds and for some reason the -23,000 seconds is coming from the other start time and not the right one.  Can anyone help?
 
Thanks,
Nick 
 
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 28 Feb 2013 at 9:48am
Is this thing on? :)
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 01 Mar 2013 at 12:32pm
I am not sure the way that you have got the formulas that you would ever get a correct result.  I think a better way would be to use a shared variable for the first formula then use that value in the second formula. 

I.E.

\\StartTime Formula
Shared DateTimeVar StartTime;
IF   {Reports_Events.Type}='Start'
 Then StartTime:={Reports_Events.Timestamp};

\\Duration Formula
Shared DateTimeVar StartTime;
IF {Reports_Events.Type}='Stop'
Then DateDiff ('s',STartTime ,{Reports_Events.Timestamp} );
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 04 Mar 2013 at 8:02am
When I changed these variables to Shared I still get the same durations.  Some durations are correct but others it gives me negative numbers.  It seems to pull one start time and use this one as the default timestamp then take the rest of the stop timestamps minus this start timestamp that would explain my wrong numbers.
 
I was wanting to say if the stop timestamp has the same key as the start timestamp then calculate the duration.
 
Thanks,
Nick
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 04 Mar 2013 at 8:42am
I am not sure how to approach this.  Somehow the key(s) and times would have to be stored and then calculations done after all the records have been read.

Maybe using a shared array (never tried this before).
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 04 Mar 2013 at 10:00am
Kevlray the keys are assigned to the event so when there is a start timestamp it generates a key.  Then when it stops it uses that same key.  Then the next event that starts it generates another key then stops with that same key.  There has to be some way to tie these together.  I tried if then statements but cannot get it to work.
 
Can you get me started in creating a shared Array?  Not sure of where to begin with this.
 
Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Mar 2013 at 10:10am
if you can change your sort and or grouping to make the start and stop rows for the same 'key' then you could just use a previous() or Next() function to get the value.
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 06 Mar 2013 at 7:02am
Grouping worked Nicely DBlank it is calculating the durations nicely now.  I am using a cross tab now to evaluate the data.  I am not familiar with the previous() or Next() function.  Can you let me know why i might need these?
 
Thank you for your help!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2013 at 7:12am
You don't have ot use them since you got it working.
As an explanation, Previous or Next allows you compare a field on one row to a field on the next or previous row.
so if group on key you would always have 2 rows per group.
if you sorted on Type ascending the Start would always be the first row and the Stop would always be the second row.
knowing this you could apply a logic to using either next or previous, however this result would not be available for use in a crosstab.


Edited by DBlank - 06 Mar 2013 at 8:05am
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 07 Mar 2013 at 10:55am
Ok makes sense..  Thank you for your help again!
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.