Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Suppressed Fields and Averages Post Reply Post New Topic
Author Message
gstace
Newbie
Newbie


Joined: 20 May 2014
Online Status: Offline
Posts: 4
Quote gstace Replybullet Topic: Suppressed Fields and Averages
    Posted: 28 May 2014 at 12:36am
Hi,
I have seen a few post along a similar line to this but as yet haven't found oen to fix my problem.
 
I have a report which looks at the time difference between the time a call is received and the time an action is taken. Each call is allocated to a worker. We want to know the time difference between the call time a repsonse time, and then the average time each worker take to respond.
 
I have suprssed some records as there are duplicates for the job ID and the worker (this is correct we expect duplicates as multiple responses at later times/dates are logged to the same call number) and we are just interested in the first call to response times for this report. For info there are occasions when no response time is logged and these are supprssed too.
 
I have tried using a running total average for each workers ID but the calculation is incorrect, the formula I have used in the evalute section of running total is
 
NOT (isnull ({MB_MOBILISATIONS.MB_MOBILE})
or{MB_MOBILISATIONS.MB_IN_REF}=previous({MB_MOBILISATIONS.MB_IN_REF})and {MB_MOBILISATIONS.MB_CALL_SIGN}=previous({MB_MOBILISATIONS.MB_CALL_SIGN}))
 
Not sure where I am going wrong! any help gatefully received and any questions please let me know
 
Thanks
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 05 Jun 2014 at 4:56am
running totals and how to filter them correctly is DBlank's specialty...

using shared variables would be about the same, you just have to identify the duplicates and not add them into your totals/counts...it is the going to be the same the running total.

what i would try doing (because I am lazy and I think this is the easiest solution) would be to group the responses...so that the duplicates are included in a group, then I would just report on the group headers(which would be unique). I think that Running Totals can then be used to capture just 1 entry, instead of all the duplicates. I know that shared variables would be able to capture the values.

perhaps someone else knows another answer
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 05 Jun 2014 at 9:56am
are the average values garnered from one row of data or are you looking across rows to get 'first call to response time' as compared to the call time?
IP IP Logged
gstace
Newbie
Newbie


Joined: 20 May 2014
Online Status: Offline
Posts: 4
Quote gstace Replybullet Posted: 05 Jun 2014 at 10:00pm
Inc No Staff ID Call Time Response Time Time Taken HH:MM:SS TimeTaken Sec
42003644 70P1 03/04/2014  19:46:51 03/04/2014  19:50:42 00:03:51 231.00
42003744 70P1 06/04/2014  13:44:07 06/04/2014  13:48:28 00:04:21 261.00
42003802 70P1 07/04/2014  13:18:51 07/04/2014  13:25:20 00:06:29 389.00
42003835 70P1 08/04/2014  12:39:49 08/04/2014  12:46:10 00:06:21 381.00
42004236 70P1 17/04/2014  19:24:29 17/04/2014  19:29:52 00:05:23 323.00
42004239 70P1 17/04/2014  20:10:17 17/04/2014  20:14:42 00:04:25 265.00
42004565 70P1 26/04/2014  12:51:02 26/04/2014  12:56:14 00:05:12 312.00
42003644 70P4 03/04/2014  19:46:51 03/04/2014  19:53:38 00:06:47 407.00
42003802 70P4 07/04/2014  13:18:51 07/04/2014  13:27:00 00:08:09 489.00
42004132 70P4 15/04/2014  15:40:05 15/04/2014  15:40:56 00:00:51 51.00
42004236 70P4 17/04/2014  19:24:29 17/04/2014  19:30:52 00:06:23 383.00
42004565 70P4 26/04/2014  12:51:02 26/04/2014  12:59:08 00:08:06 486.00
70



306.62
IP IP Logged
gstace
Newbie
Newbie


Joined: 20 May 2014
Online Status: Offline
Posts: 4
Quote gstace Replybullet Posted: 05 Jun 2014 at 10:06pm
The above is an exttact for a couple of our staff ID's in a team, team 70 in this case

So suppression forthose calls that were not reponded to are suprssesd as are those where the same ID and Inc No are repeated as this represents a repeat call I am currently average the time taken in seconds and would convert it to HH:MM:SS using

WhilePrintingRecords;
NumberVar TotalSec :=  {@TimeTakenSec};

NumberVar Days    := Truncate  (TotalSec / 86400);
NumberVar Hours   := Truncate  (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate  (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;

Totext ( Hours ,   '00' ) +  ':' +
Totext ( Minutes , '00' ) +  ':' +
Totext ( Seconds , '00' )

So the average at the moment comes from the Time Taken Sec column where each value is calculating the difference between the call time and response time. It's still counting the surpressed fields when it calculates it's averages though

Hope that helps
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Jun 2014 at 3:50am
for the running total you will need to create a formula to get the time difference and leave it as numeric because you cannot average a text field which you are using to display.
For the running total you should be able to do this
 
Name=AverageAll (or whatever)
field to summarize =formula field for numeric value of time difference
type=average
evaluate=use a formula
--use your suppression formula but with NOT(suppression formula here) around it
reset= never (assuming you want it for al records and not a grouping)
place in detail or footer


Edited by DBlank - 06 Jun 2014 at 3:51am
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.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.