John-S Pretorius
asked on
Sum of Durations in a Group
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).
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
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
ASKER
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",({ContractParkerMovem ents.Time} ),next({Co ntractPark erMovement s.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.
UsageDuration-MontUsers.rpt
(datediff ("n",({ContractParkerMovem
-(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.
UsageDuration-MontUsers.rpt
ASKER
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. MovementTy pe}) <> 4 then user_duration +
(datediff ("s",({ContractParkerMovem ents.Time} ),next({Co ntractPark erMovement s.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.
UsageDuration-MontUsers0411.rpt
This is what I added to the formula James supplied :
WhilePrintingRecords;
Global NumberVar user_duration;
user_duration := if ({ContractParkerMovements.
(datediff ("s",({ContractParkerMovem
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.
UsageDuration-MontUsers0411.rpt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
You're welcome. Glad I could help.
James
James
mlmcc