Running Total of Specific Group

[mgkSHA]

Hello:

I am sure this question has been asked before, but I am new to Crystal Syntax and need some help.

I have a report which lists number of phone numbers called and the duration of each call from a list of extensions. I have created a report with an extension parameter, and the user selects the extension they want and they get all the phone numbers and the duration of each call. I have created two runningtotal fields, one for the number of times each phone number is called, and one to add up the total duration for each phone number.

My first runningtotal field works (#RTotal0), it populates a text box with the phone number count. I can't get the formula right for adding up the duration. As in, if a phone extension has four entries, I need (#RTotal1)to add up each duration and create a "Grandtotal" in the textbox, I have tried numerous count, sum, add, functions and I just can't get it right. Can someone tell me the appropriate formula within a runningtotal field to add up all entries within a group and provide a grand total? Thanks

 

[mgkSHA]

Ok, the more I think about it, do I want a running total for this? Basically, I have a report, and it is broken into groups - - by phone number. I want, on the same line as the Running total of the phone number, a field that adds up the length of all the calls. As in, if the group contains 7 instances of 999-999-9999, I want to add up the 7 call lengths and put them into a field.

Again, I don't know how to do this and I am on a tight schedule..please help if you can. Basically, I have to find a way to add up the "call length" fields within a group and put that information into a textbox, running total field, parameter, anything. What formula can I use for this? Thanks

 

[herbsza]

How are the call lenghts displayed ?

Example "01:00:10" or are they minutes "45" or seconds

 

[mgkSHA]

The current format is HH:MM:SS, and I want within the group, the fields to add up in the running total field, right now I can get a count of fields, a max, min, average, etc., but I want a sum of fields, just add one to two, two to three, etc. Any advice, how do you add fields together

(note, even though it is HH:MM:SS format, this is a text field...I just want to add the numbers together..if I could translate into time as well, great, but I have a utility in VB which does this on the actual VB grids I have)

 

[herbsza]

I have been struggling with a similar problem .
For your problem i think you would need to try something like this.

test := "01:20:34";
test2 := tonumber(ExtractString ((test),"",":&quot); //hours
test50 := tonumber(mid(test,4,2));// minutes
test51 := tonumber(mid(test,7,2));// seconds

placed in separate formulas on the details section.
Then summarize each formula.
If your minutes and seconds exceed 60 then you will need
divide by 60 and adjust your hours and minutes accordingly.
Then totext each variable and concatenate
totext(hours)+":"+totext(minutes)+":"totext(seconds)

you should then get the folowing results

User1 "03:09:15"
User2 "120:45:00"
and so on.

I hope this of some help.

 

[mgkSHA]

Herbsza:

I am new to Crystal Reports and I am not sure how to use your advice. Can you advise some more. As in, the field I am trying to review is called ado.CorrectedDuration. How does this play into these formulas:

test := "01:20:34";
test2 := tonumber(ExtractString ((test),"",":&quot); //hours
test50 := tonumber(mid(test,4,2));// minutes
test51 := tonumber(mid(test,7,2));// seconds

so these go into the details section of the report? If so, how does the actual field, ado.CorrectedDuration get effected?

This line here, does it go in the group footer:

totext(hours)+":"+totext(minutes)+":"totext(seconds)

I have a running total field called RTotal1, does this code go into this field? If not, again, how is it effected. Please help if you can.

Martin

 

[herbsza]

example

user1 "01:00:09"
user1 "01:00:20"
user2 "10:56:34"
user2 "20:55:10"
user3 "00:14:00"

formula gethrs

Global stringVar test := {ado.CorrectedDuration};
Global NumberVar test1 := 0;

test1 := tonumber(ExtractString ((test),"",":&quot); //hours

formula getmin

Global stringVar test := {ado.CorrectedDuration};
Global NumberVar test2 := 0;

test2 := tonumber(mid(test,4,2)); // minutes

formula getsec

Global stringVar test := {ado.CorrectedDuration};
Global StringVar test3 := 0;

test3 := tonumber(mid(test,7,2)); // seconds

place the formulas in the detail section

{ado.CorrectedDuration} gethrs getmin getsec
detail section user1 "01:00:09" 01 00 09
detail section user1 "01:00:20" 01 00 20
detail section user2 "10:56:34" 10 56 34
detail section user2 "20:55:10" 20 55 10
detail section user3 "00:14:00" 00 14 00

then create a group on user and sum the three different formulas
the summarys can be placed either in the group header or footer.

group1 header user1 02 00 29
detail section user1 "01:00:09" 01 00 09
user1 "01:00:20" 01 00 20
group footer
group2 header user2 30 111 44
detail section user2 "10:56:34" 10 56 34
user2 "20:55:10" 20 55 10
group footer
group3 header user3 00 14 44
detail section user3 "00:14:00" 00 14 00
group footer

now create a formula to be placed in the group.

formula actualtime

whileprintingrecords;
Global NumberVar test4 := //place the hours summary in this varialble;
Global NumberVar test5 := //place the min summary in this variable;
Global NumberVar test6 := //place the sec summary in this variable;
global numbervar test7 := 0;
global numbervar test8 := 0;
global numbervar min := 0;
global numbervar hrs := 0;
global stringvar finaltime := "";

if test6 > 60 then
(
min := truncate(test6 / 60);
test7 := min * 60;
test6 := test6 - test7;
);
if test5 > 60 then
(
hrs := truncate(test5 / 60);
test8 := hrs * 60;
test5 := test5 - test8;
);

finaltime := totext(test4 + hrs)+":"+totext(test5 + min)+":"+totext(test6);
finaltime
group1 header user1 02 00 29 02:00:29
detail section user1 "01:00:09" 01 00 09
user1 "01:00:20" 01 00 20
group footer
group2 header user2 30 111 44 31:51:44
detail section user2 "10:56:34" 10 56 34
user2 "20:55:10" 20 55 10
group footer
group3 header user3 00 14 44 00:14:44
detail section user3 "00:14:00" 00 14 00
group footer
I hope this is a better explanation.

 

[mgkSHA]

Herbza:

This is my problem: I can not group this by each user. I have a report which is grouped by Phone number. So, currently on this report, using the code provided, I have something like this: (within each phone number group)

1-301-4545 field field field DURATION
" " 0:02:34
"" 0:34:07
"" 0:21:05
"" 0:17:12
Running Total number of times called: 5
Running Sum of the duration: 00:17:12 (wrong)

-your example converts the string to an integer and then back to a time again...my problem is, I am getting each field value, but I still can't get THE RUNNING SUM. I need each value I am parsing and re-parsing to be added together as a sum in the runningtotal field. Unfortuntely, I can't use the user grouping you provided. How can I, based on this format, get these totals TO SUM UP. Thank you so much.

 

[herbsza]

Forget about the running total for duration, it will not work.
You must create your own formula's.
In my example just replace user with the phone number.
The formula actualtime will give you the duration of the calls in the variiable finaltime.
Remember to place this formula in the group header or footer
and not the detail section. Also important to note that
you must use the whileprintingrecord statement as the
calculations are done after the summaries have been calculated by C.R.
group header 1-301-4545
detail section 1-301-4545 0:02:34
"" 0:34:07
"" 0:21:05
"" 0:17:12
group footer Running Total number of times called: 5
summary of (formula gethrs) = 0
summary of (formula getmin) = 114
summary of (formula getsec) = 58
(formula actual time) = 01:14:58 (duration)

I hope this helps you a bit more.

 

[herbsza]

I made a small mistake when typing the previous message.
summary of (formula getmin) = 114 should read
summary of (formula getmin) = 74

 

[mgkSHA]

Herbsza: I am missing something here, again, my formula is providing the value for the last entry, not the running sum. I have attached exactly what I have right now:

Step One:

Three formulas all within the details section:

One:

Global stringVar test:={ado.CorrectedDuration};
Global NumberVar test1:=0;

test1:=tonumber(ExtractString((test),"",":&quot); //used for hours

Two:

Global stringVar test:={ado.CorrectedDuration};
Global NumberVar test2:=0;

test2:=tonumber(mid(test,4,2)); //get minutes

three:

Global stringVar test:={ado.CorrectedDuration};
Global NumberVar test3:=0;

test3:=tonumber(mid(test,7,2)); //get seconds

In the PhoneNumber GROUP HEADER - - I have the following formula:

whileprintingrecords;
Global NumberVar test4 :={@gethrs};
Global NumberVar test5 := {@getmins};
Global NumberVar test6 := {@getsecs};
global numbervar test7 := 0;
global numbervar test8 := 0;
global numbervar min := 0;
global numbervar hrs := 0;
global stringvar finaltime := "";

if test6 > 60 then
(
min := truncate(test6 / 60);
test7 := min * 60;
test6 := test6 - test7;
);
if test5 > 60 then
(
hrs := truncate(test5 / 60);
test8 := hrs * 60;
test5 := test5 - test8;
);

finaltime := totext(test4 + hrs)+":"+totext(test5 + min)+":"+totext(test6);
finaltime;

This is all the coding I have: My results

Phone# Field1 Field2 Field3 DURATION
1-202-999-9999 00:05:09
" " 00:02:23
" " 00:12:04
" " 00:10:04
Total Number of calls:4
formula @actualtime results: 0.00:5.00:9.00 (not correct )

Where did I go wrong, here, I am not getting the results of all durations, only the first one.

 

[mgkSHA]

Herbsza:

I think the culprit here are the three gethrs, getmin, getsecs formula. Because I am only getting the hour, min, and secs for the first entry..hence the actualtime formula would then also provide only that one entry.

The three formulas seem to only go through the first record of the group, not the entire recordset( if phone number group 1-800-999-9999 has four records, the formulas produce results for the first record only).

I am looking at the code, and I'm not sure how to fix that . Again, any help is appreciated. You have been very helpful and I hope to get this solved.

Martin

 

[mgkSHA]

All right, I found my problem:

You say numerous times to summarize my formulas, I have not done that. How do you summarize a formula? If I right click my mouse in the group footer I can choose the summary option which applies to field records (ado.fieldname) and count, max, min, mode, etc. How do I make a summary of my formula fields. I am totally lost and ready to quit.

 

[herbsza]

In the detail section go to the three formaulas.
left click on a formula.
The summary icon should become avaliable and then choose the sum.
Do this for all three formulas gethrs, getmin, getsecs.
If the summary icon does not get highlighted or does
not provide the sum you will need to create another formula
which adds the values of each of the three formulas together.
To get rid of the decimal's in the variable finaltime
(0.00:5.00:9.00) do the following
finaltime := totext((test4 + hrs),"00&quot+
":"+totext((test5 + min),"00&quot)+":"+totext(test6,"00"

You don't need to give up now. you are not very far from getting this report to work.

 

[mgkSHA]

Herbsza:

I am using Crystal 8.5 within VB...again, for whatever reason, I can not do a summary on these formulas. I right click and I get the dropdown menu of actions, nothing about the formula. If I right click the formula to highlight it, and go to the "Sigma" symbol (the greek E) to insert a summary I get a list of choices Max, Mix, mode, Nth, no sum, and I can only do these functions for fields on my report (ado.fieldname), not the formula selected. This is really, really frusterating me..thank you for your help and patience.

So, I have to create another formula to add up the formula. How should I write this formula? Also, does it go in the details section or group header/footer. Will the actualtime formula call to this formula. Thanks for your help, I HOPE this is my last step..you have been more than helpful.

Martin

 

[herbsza]

try the following

formula setvariable

global numbervar hours := 0;
global numbervar minutes := 0;
global numbervar seconds := 0;

place this new formula in the group header.

One:

Global stringVar test:={ado.CorrectedDuration};
Global NumberVar test1:=0;
global numbervar hours; //this is new

test1 := tonumber(ExtractString((test),"",":&quot);
hours := hours + test1; //this is new

Two:

Global stringVar test:={ado.CorrectedDuration};
Global NumberVar test2:=0;]
global numbervar minutes; // this is new

test2:=tonumber(mid(test,4,2));
minutes := minutes + test2; //this is new

three:

Global stringVar test:={ado.CorrectedDuration};
Global NumberVar test3:=0;
global numbervar seconds;// this is new

test3:=tonumber(mid(test,7,2)); //get seconds
seconds := seconds + test3; //this is new

formula actualtime can only be placed in the group footer now.
whileprintingrecords;
Global NumberVar hours; //this is new
global numbervar minutes; //this is new
global numbervar seconds; //this is new
Global NumberVar test5 := 0;
Global NumberVar test6 := 0;
global numbervar test7 := 0;
global numbervar test8 := 0;
global numbervar min := 0;
global numbervar hrs := 0;
global stringvar finaltime := "";

if test6 > 60 then
(
min := truncate(seconds / 60); //this has changed
test7 := min * 60;
test6 := seconds - test7; //this has changed
);
if test5 > 60 then
(
hrs := truncate(minutes / 60); //this has changed
test8 := hrs * 60;
test5 := minutes - test8; //this has changed
);

finaltime := totext(hours + hrs,"00&quot+":"+totext(test5 + min,"00&quot+":"+totext(test6,"00&quot); //this has changed
finaltime;

 

[mgkSHA]

Herbsza:

I really appreciate your continued help. Here is the newest problem . I put the exact formulas in where you specified, one: in the header, the next three: in details, the final actualtime formula in the footer. Here are the results.

PhoneNumber Field Field DURATION
1-900-999-9999 00:05:09
gethrs-0.00
getmins-62.00
getsecs-1,850.00

Total Number of Calls: 1
TOTAL DURATION: 18:00:00? (should be 00:05:09)

PhoneNumber Field Field DURATION
1-345-000-0000 00:02:43
gethrs-0.00
getmins-191.00
getsecs-3,957.00

" " 00:03:44
gethrs-0.00
getmins-194.00
getsecs-40001.00

Total Number of Calls: 2
TOTAL DURATION: 18:00:00 (the same duration all the way down)


Yeah, I am getting 18:00 for each group and it seems the hrs, mins, and secs is off too. Any ideas..I don't want you to get as frusterated as I am . I am just cursed with this report, I have NEVER had such a programming nightmare in my life.

 

[mgkSHA]

Herbsza:

I took some time away from this (if I didn't I was going to punch my monitor ) and I have some other VB/Java apps I am working on. I still can't figure out for the life of me why my formula in the footer always says 18:00:00, your code looks good, AHHH!

I had another programmer suggest another possible method, but his method involved still using the running total field and it wound up not working. He gave up and I don't blame him . I really appreciate your help, do you have any ideas to fix this current bundle of problems . Let me know, again, I am still a Crystal novice and I can't think of any way to get this current code to work .

Martin

 

[herbsza]

The problem is that we are not setting the three formula's back to zero after each group is worked out.
That is why the group footer time shows 18:00:00 because
it is adding all the records together.
Because of the whileprintingrecords statement the formula actualtime is performed right at the end by which time the
three formaula's gethrs, getmins, getsecs contain all the records summed up.
In the formal actualtime

//rest of formula stays the same
finaltime := totext(hours + hrs,"00&quot+":"+totext(test5 + min,"00&quot+":"+totext(test6,"00&quot); // stays the same

//add the following

global numbervar hours := 0;
global numbervar minutes := 0;
global numbervar seconds := 0;

finaltime; // stays the same
try the report now.

 

[mgkSHA]

Herbsza:

I attached the code as it now reads in the actualtime formula:

The details section reads as it should, each individual record is broken down accordingly, and in the group header the formula returns 0:00 because we wanted the initial values of our variables to be zero.

Now, instead of 18:00:00 for group totals, I am getting 0:00 for all the group totals . Any ideas?

whileprintingrecords;
Global NumberVar hours;
global numbervar minutes;
global numbervar seconds;
Global NumberVar test5 := 0;
Global NumberVar test6 := 0;
global numbervar test7 := 0;
global numbervar test8 := 0;
global numbervar min := 0;
global numbervar hrs := 0;
global stringvar finaltime := "";

if test6 > 60 then
(
min := truncate(seconds / 60);
test7 := min * 60;
test6 := seconds - test7;
);
if test5 > 60 then
(
hrs := truncate(minutes / 60);
test8 := hrs * 60;
test5 := minutes - test8;
);

finaltime:= (totext(hours + hrs,"00&quot+":"+totext(test5 + min,"00&quot+":"+totext(test6,"00&quot);

global numbervar hours := 0;
global numbervar minutes := 0;
global numbervar seconds := 0;

finaltime;