Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: duplicate / overlapping records Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Mark O
Newbie
Newbie


Joined: 29 Mar 2007
Location: United States
Online Status: Offline
Posts: 5
Quote Mark O Replybullet Topic: duplicate / overlapping records
    Posted: 29 Mar 2007 at 2:40pm
Hi
 
I am working on a report which has fields tracking date and time of services performed.  Occasionally I will run into duplicate entries or worse entries where the time period overlaps a time period from another entry.  An example is pasted below.  My problem is I would like for each of these enties to appear on the report but I can not double count the time spent on each service.  The desired result for this example is 30 minutes total.  Please help!  I will give up my firstborn to anyone with a solution Smile
 
Date Start Time End Time Total Min
20070201 3:00 PM 3:15 PM 15
20070201 3:00 PM 3:15 PM 15
20070201 3:00 PM 3:30 PM 30
      60
IP IP Logged
dhakshu
Newbie
Newbie
Avatar

Joined: 01 Mar 2007
Location: Singapore
Online Status: Offline
Posts: 21
Quote dhakshu Replybullet Posted: 30 Mar 2007 at 12:19am
Hi!
 
How do you find the total?
 
Do you use running total?
 
Try using a formula in the summary field
---------------------------
if currentvalue(start time,end time) <>  lastvalue(starttime,endtime)
 
ie.,
In the Report Header
Declare
Global Numbervar total;
total:=0;
 
WhilePrintingRecords;
Global Numbervar total;
if not onfirstrecord then
if command.start_time <> previous(command.start_time) and
    command.end_time<>previous(command.end_time) then
(
total:=total+command.min;
);
 
Hope this works!
Regards,
Dhakshesh


Edited by dhakshu - 30 Mar 2007 at 12:28am
Dhakshu
IP IP Logged
Mark O
Newbie
Newbie


Joined: 29 Mar 2007
Location: United States
Online Status: Offline
Posts: 5
Quote Mark O Replybullet Posted: 30 Mar 2007 at 11:15am
Thanks Dhakshesh.  I wasn't using a running total but I am now.  I should have pointed out in my OP that I am a novice.  What is the summary field?  I did attempt to enter your formula in the Evaluate section of the Create Running Total Field but I do not think it goes there.  My running total field went blank when I applied the formula there.  Also went blank when I tried the formula in the Total Time field I created to sum the detail lines.  Thanks again!
IP IP Logged
dhakshu
Newbie
Newbie
Avatar

Joined: 01 Mar 2007
Location: Singapore
Online Status: Offline
Posts: 21
Quote dhakshu Replybullet Posted: 31 Mar 2007 at 7:35am
Hi Mark!
    
  Hope this helps!
  Use three formulas.
  @total1
-------------
Global numbervar total;
total:=0;
@total2
--------
Global Numbervar total;
total:=0;
 
WhilePrintingRecords;
Global Numbervar total;
if not onfirstrecord then
if command.start_time <> previous(command.start_time) and
    command.end_time<>previous(command.end_time) then
(
total:=total+command.min;
);
 
@total3
------------
Global Numbervar total;
 
Place @total1 in report header
Place @total2 in details section
Place @total2 in the report footer where you want to print the total min
 
I have used similar formula in my reports and has worked properly.
 
Regards,
Dhakshesh
      
   
Dhakshu
IP IP Logged
Mark O
Newbie
Newbie


Joined: 29 Mar 2007
Location: United States
Online Status: Offline
Posts: 5
Quote Mark O Replybullet Posted: 02 Apr 2007 at 2:02pm
Thank you Dhakshesh!  I am getting very close.  One minor detail left is that the first record in the group is not counted in my total even though it should be.  I only used the @total2 formula you gave me because #1 & #3 gives me values of 0.  I do not understand their purpose.
 
ClapClapClapClapClapClapClap


Edited by Mark O - 02 Apr 2007 at 2:06pm
IP IP Logged
dhakshu
Newbie
Newbie
Avatar

Joined: 01 Mar 2007
Location: Singapore
Online Status: Offline
Posts: 21
Quote dhakshu Replybullet Posted: 02 Apr 2007 at 6:21pm
Hi Mark!
 
Please note the change in the formula @total2
 
@total2
--------
WhilePrintingRecords;
Global Numbervar total;
// this will resolve the first record issue
if onfirstrecord then
total:=total+command.min;
 
if not onfirstrecord then
if command.start_time <> previous(command.start_time) and
    command.end_time<>previous(command.end_time) then
(
total:=total+command.min;
);
 
@total1 is used to set the total variable to 0.
 
@total3 is used to print the total value at the end of the report.
 
Regards,
Dhakshesh
 
 


Edited by dhakshu - 02 Apr 2007 at 6:23pm
Dhakshu
IP IP Logged
Mark O
Newbie
Newbie


Joined: 29 Mar 2007
Location: United States
Online Status: Offline
Posts: 5
Quote Mark O Replybullet Posted: 04 Apr 2007 at 12:49pm

Dhakshu

You have helped me greatly.  I have 2 other questions realted to this report.  Is there a way to reset my running total for each new group within the report?  Also, i ran into an occurence with a record which has a missing end time.  The running total correctly does not add time for this record.  The problem is the next record is not counted in the running total even though it should be.  In fact the running total field is blank for the next record instead of displaying 0.00.

Thank you !!!
 
Mark
IP IP Logged
dhakshu
Newbie
Newbie
Avatar

Joined: 01 Mar 2007
Location: Singapore
Online Status: Offline
Posts: 21
Quote dhakshu Replybullet Posted: 04 Apr 2007 at 7:39pm
Hi Mark
 
 
   1) In Running Total Wizard
           Evaluate-->  formula
           Reset  --> On change of group <your group name>
      
   2) In the formula add another condition
        if command.min <> 0 or  not Isnull(command.min)
 
Regards,
Dhakshesh


Edited by dhakshu - 04 Apr 2007 at 8:29pm
Dhakshu
IP IP Logged
Mark O
Newbie
Newbie


Joined: 29 Mar 2007
Location: United States
Online Status: Offline
Posts: 5
Quote Mark O Replybullet Posted: 17 Apr 2007 at 7:42am
Thanks for all your help Dhakshesh.  You should charge for your services Smile
 
Mark
IP IP Logged
dhakshu
Newbie
Newbie
Avatar

Joined: 01 Mar 2007
Location: Singapore
Online Status: Offline
Posts: 21
Quote dhakshu Replybullet Posted: 17 Apr 2007 at 6:38pm
Good to know that it has helped you.Smile
We must all be grateful to Brian for such a useful forum and his excellent book on crystal reports.
 
 
Regards,
Dhakshesh
Dhakshu
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.029 seconds.