| Author |
Message |
Mark O
Newbie
Joined: 29 Mar 2007
Location: United States
Online Status: Offline
Posts: 5
|
   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 
| 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 Logged |
|
|
dhakshu
Newbie
Joined: 01 Mar 2007
Location: Singapore
Online Status: Offline
Posts: 21
|
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 Logged |
|
Mark O
Newbie
Joined: 29 Mar 2007
Location: United States
Online Status: Offline
Posts: 5
|
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 Logged |
|
dhakshu
Newbie
Joined: 01 Mar 2007
Location: Singapore
Online Status: Offline
Posts: 21
|
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 Logged |
|
Mark O
Newbie
Joined: 29 Mar 2007
Location: United States
Online Status: Offline
Posts: 5
|
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.
Edited by Mark O - 02 Apr 2007 at 2:06pm
|
|
IP Logged |
|
dhakshu
Newbie
Joined: 01 Mar 2007
Location: Singapore
Online Status: Offline
Posts: 21
|
Posted: 02 Apr 2007 at 6:21pm |
Hi Mark!
@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 Logged |
|
Mark O
Newbie
Joined: 29 Mar 2007
Location: United States
Online Status: Offline
Posts: 5
|
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 Logged |
|
dhakshu
Newbie
Joined: 01 Mar 2007
Location: Singapore
Online Status: Offline
Posts: 21
|
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 Logged |
|
Mark O
Newbie
Joined: 29 Mar 2007
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 17 Apr 2007 at 7:42am |
Thanks for all your help Dhakshesh. You should charge for your services
Mark
|
|
IP Logged |
|
dhakshu
Newbie
Joined: 01 Mar 2007
Location: Singapore
Online Status: Offline
Posts: 21
|
Posted: 17 Apr 2007 at 6:38pm |
Good to know that it has helped you.
We must all be grateful to Brian for such a useful forum and his excellent book on crystal reports.
Regards,
Dhakshesh
|
|
Dhakshu
|
|
IP Logged |
|
|
|
