Solved

Sum of Durations in a Group

Posted on 2012-04-10
7
371 Views
Last Modified: 2012-08-14
I have Crystal report which is grouped per user, I then make use of Datediff to get the duration of stay between entrance and exit. If a User has multiple entrances and exits (Multiple durations) I want to add them but Crystal tells me that the field cannot be summarized, If have been able to use count and variable to 'cheat' this in the past but know need the sum of all 'durations' per Group (User).

D
0
Comment
Question by:John-S Pretorius
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 37830935
How are you doing the calculation?

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 37831358
It looks like you've got separate Entry and Exit records, and I'm guessing that you're using something like DateDiff ("m", {date field}, Next ({date field})) to get the Duration.  You wouldn't be able to do a summary on that, since it uses Next.  If I'm right, you'll probably need to use a variable to get the total.  The details may vary depending on your report, but here are the basics.

 Create a formula like the following (call it whatever you like) and put it in the report header _and_ the User group header:

WhilePrintingRecords;
Global NumberVar user_duration;
user_duration := 0;
""

 The "" at the end is simply so that it doesn't produce any visible output on the report.  You could also suppress the formula, or the section that it's in.

 Putting that in the report header declares the variable for the report, and putting it in the group header resets the variable for each new group (user).

 Change the formula where you calculate the Duration.  Add these lines to the beginning:

WhilePrintingRecords;
Global NumberVar user_duration;


 Change the end of that formula (where you calculate the Duration) to something like this:

user_duration := user_duration + <your Duration calculation>;

<your Duration calculation>


 Do the calculation once to add the result to the variable, then do it again to produce the Duration on the report.


 Then create another formula to output the total in the group (User) footer:

WhilePrintingRecords;
Global NumberVar user_duration


 Like I said, the details may vary, depending on exactly how you're calculating the Duration, etc., but that's the general idea.

 James
0
 

Author Comment

by:John-S Pretorius
ID: 37832290
Your approach is what I have used in the past to get a count together, and it is close but as you mentioned causes issues due to the fact how I'm calculating Duration :

(datediff ("n",({ContractParkerMovements.Time}),next({ContractParkerMovements.Time})));
-(Note - I conditionally suppressed the Exit value as I don't care for how long the user has left the building)

The answer in the footer is not calculating the sum, I found that there is actually copied values below some of th e original entrie values and then at the footer I always get the last value of the group.

I'm guessing the result is due to the 'next' statement I'm using - how would one use 'if onlastrecord' in a group?

Please have a look at the image and sample report.

1UsageDuration-MontUsers.rpt
0
Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

 

Author Comment

by:John-S Pretorius
ID: 37832768
I hate cheating/(working around) to get results and hope someone can guide me how to calculate the same results correctly:

This is what I added to the formula James supplied :
WhilePrintingRecords;
Global NumberVar user_duration;
user_duration :=  if ({ContractParkerMovements.MovementType}) <> 4 then user_duration +
(datediff ("s",({ContractParkerMovements.Time}),next({ContractParkerMovements.Time})))
else user_duration;

The result is a total sum of the durations, Following the instructions and refering to the previous image insert you will see that the 'Entry' duration totals was actually just growing/(adding the difference to the previous duration) - so I created a second dutation formula which shows me the distinct duration for each entry, and then I changed the font color to white of the original duration/(acumulating duration)

I would like to be able to do datetime difference per Group correctly (Using Next? and maybe 'onlastrecord' ?) - I'm still very new to Crystal and don't want to adapt the wrong habbits....Please assist.



See Image and Report example.

1UsageDuration-MontUsers0411.rpt
0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 37833673
As far as the "growing" figure goes, that's why I said to include the duration calculation twice.  Forgetting the test on MovementType for the moment, @Duration would look like this:

WhilePrintingRecords;
Global NumberVar user_duration;

user_duration := user_duration +
 DateDiff ("s", {ContractParkerMovements.Time}, Next ({ContractParkerMovements.Time}));

DateDiff ("s", {ContractParkerMovements.Time}, Next ({ContractParkerMovements.Time}))


 It calculates the difference once and adds that to the variable, and then calculates it again and uses that as the output for the formula, so you see the current figure, instead of the total.

 If you only want to add the difference to the total when MovementType <> 4, I would use this:

WhilePrintingRecords;
Global NumberVar user_duration;

if ({ContractParkerMovements.MovementType}) <> 4 then
  user_duration := user_duration +
   DateDiff ("s", {ContractParkerMovements.Time}, Next ({ContractParkerMovements.Time}));

DateDiff ("s", {ContractParkerMovements.Time}, Next ({ContractParkerMovements.Time}))


 That only adds to user_variable when MovementType <> 4.  It still outputs the difference (the last DateDiff), but you won't see it when MovementType = 4, because of your suppression on that field.


 I'm not sure what you were saying about Next and OnLastRecord, but if you're concerned about using Next and getting the value from the first record from the next group (user), you can check Next ({ContractParkerMovements.UserNo}).  Something like this maybe:

WhilePrintingRecords;
Global NumberVar user_duration;

if not OnLastRecord and
 {ContractParkerMovements.UserNo} = Next ({ContractParkerMovements.UserNo}) and
 {ContractParkerMovements.MovementType} <> 4 then
  user_duration := user_duration +
   DateDiff ("s", {ContractParkerMovements.Time}, Next ({ContractParkerMovements.Time}));

if not OnLastRecord and
 {ContractParkerMovements.UserNo} = Next ({ContractParkerMovements.UserNo}) then
  DateDiff ("s", {ContractParkerMovements.Time}, Next ({ContractParkerMovements.Time}))



 James
0
 

Author Closing Comment

by:John-S Pretorius
ID: 37833722
Thank you.
0
 
LVL 35

Expert Comment

by:James0628
ID: 37836059
You're welcome.  Glad I could help.

 James
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question