group sum for seconds

[tezzyr]

I have a formula which returns a number eg(26,2345.00)
) and displays this value for each record in a group (grpname)

I'm trying to add all of these values and display this sum in the group footer.
I've created an initialize, evaluate and display formula eg @init
whileprintingrecords;
numbervar runningtotal;
runningtotal:=0;
and an evaluate function eg
@eval
WhilePrintingRecords;
NumberVar RunningTotal;
RunningTotal := RunningTotal {@totalsecondsformula);
runningtotal;

this seems to work for the first record that is displayed in the group but does not add up the second record to the first record correctly.
at the moment it shows:-

runningtotal=0
record1 = 12,461 runningtotal=12,461
record2 = 23,734 runningtotal=705,568

can you help??
thanks

 

[synapsevampire]

This formula couldn't work:

@eval
WhilePrintingRecords;
NumberVar RunningTotal;
RunningTotal := RunningTotal {@totalsecondsformula);
runningtotal;

The post doesn't make sense in intent nor does the code supplied.

Try posting example data and expected output.

Why would you show the same value for every record? "I have a formula which returns a number eg(26,2345.00)
) and displays this value for each record in a group (grpname)"

Are you trying to simply sum seconds as the title suggests?

Why not just use:

sum({table.seconds},{table.group})

-k

 

[tezzyr]

sorry, should have been clearer.
basically, Ive got a number datatype which is returned from a formula that calculates number of seconds in a time period.
i have grouping set up as follows
group1 header
firstname
group 2 header
secondname
group 3 header
department
group 4 header runningtotal initialised to=0.0



record 1 38,365.00 runningtotal =38,365.00
record 2 124,755.00 runningtotal =379,243.00
record 3 20,034.00 runningtotal =788,180.00



group4 footer.
runningtotal final value

group3footer
group2footer
group1footer
as you can see, the first running total is correct, but instead of adding 38,365 to 124,755 for the second runningtotal value, it outputs an incorrect value.
what I'm trying to do is to sum up all of these values and display the sum in the group footer.
I've tried simply right clicking on the record1 value field and inserting a summary that displays in the group footer but the values still do not add up.

 

[synapsevampire]

Sounds liek you're using suppression or a group selection formula so you have other rows in there.

You still didn't post the formula you're using, so I'll just supply theory for you.

Whatever criteria you are using for the record display should be used in the aggregate required. Crystal deosn't decide to inflate values in totals, something on your design is causing this, so you need to think about what you did and the effect of it.

So whatever is in the suppression or group formula, create a formula as in:

if {table.,field} = <value> then
{table.secs}
else
0

Now place this formula in the details and you cna create sums off of it.

It's difficult to help unless you post what you've done.

But Im fairly confident that once you realize that there are other rows involved you will be able to use conditional
aggregates.

btw, you cna also use a conventional Running Total and place the criteria in the Evaluate->Use a formula.

-k

 

[synapsevampire]

Sounds like you're using suppression or a group selection formula so you have other rows in there.

You still didn't post the formula you're using, so I'll just supply theory for you.

Whatever criteria you are using for the record display should be used in the aggregate required. Crystal deosn't decide to inflate values in totals, something on your design is causing this, so you need to think about what you did and the effect of it.

So whatever is in the suppression or group formula, create a formula as in:

if {table.,field} = <value> then
{table.secs}
else
0

Now place this formula in the details and you cna create sums off of it.

It's difficult to help unless you post what you've done.

But Im fairly confident that once you realize that there are other rows involved you will be able to use conditional
aggregates.

btw, you cna also use a conventional Running Total and place the criteria in the Evaluate->Use a formula.

-k

 

[tezzyr]

thanks so much for your help so far, it really is appreciated.
i created a formula called elapsedtime to convert datediff values into text so that it shows in hh:mm:ss format on the report and this returns the correct values.
elapsedtime reads as follows
cstr( Truncate( (DateDiff ("h", {EVENTS.EVENTIME}, {EVENTS_OUT.EVENTIME}) )),"00") + ":"
+ cstr( Remainder (DateDiff ("n", {EVENTS.EVENTIME}, {EVENTS_OUT.EVENTIME}), 60), "00" )
+ ":"+ cstr(Remainder (DateDiff ("s", {EVENTS.EVENTIME}, {EVENTS_OUT.EVENTIME}), 60), "00")

this value is then converted into a number in a formula called totalseconds, which outputs a number datatype which is the total number of seconds between two dates.


@totalseconds is the name of the formula that returns a number value for number of seconds between two dates

totalseconds formula reads as follows: -

local numbervar hours;
local numbervar minutes;
local numbervar seconds;
global numbervar totalsecs;
local stringvar hoursplit;
local stringvar minsplit;
local stringvar secssplit;

hoursplit:=split ({@ElapsedTimehrs},":" )[1];
minsplit:=split ({@ElapsedTimehrs},":" )[2];
secssplit:=split ({@ElapsedTimehrs},":" )[3];

hours := tonumber(hoursplit);
minutes := tonumber(minsplit);
seconds := tonumber(secssplit);

hours := (hours * 3600);
minutes := (minutes * 60);

totalsecs := (hours + minutes + seconds);

as you see, this returns a number value (which will be later used to convert back into an hh:mm:ss string for display on the report)

formula for initializing runningtotal (@init) is placed in groupheader and is as follows:
WhilePrintingRecords;
NumberVar RunningTotal;
RunningTotal := 0

formula for evaluating totals is placed in details section along with the values it is supposed to add up and reads as follows (@eval):
WhilePrintingRecords;
NumberVar RunningTotal;
RunningTotal := RunningTotal +ToNumber({@totalseconds});

and its this value (runningtotal) which isnt adding up correctly.

Ive tried simply incrementing runningtotal by 1 each time it is displayed (not using any data from the record), runningtotal should show 1 for record 1, 2 for record 2 etc in my test but it seems to be incrementing by strange amounts (1 then 4 then 7 then 20!)
im not sure how to find out if the runningtotal is using values it shouldnt as ive not had much experience with cr im afraid.
no groups are hidden or suppressed on the report but runningtotal value still doesnt add up, also tried right clicking the @totalseconds field on the report and inserting a summary using the standard wizard for the group but the values still dont add up.

thanks again for your help

 

[synapsevampire]

Yeah, i knew that you wetren't using suppression in the groups, what about the details?

And what's is in the Report->selection formula->Group?

So this "Ive tried simply incrementing runningtotal by 1 each time it is displayed (not using any data from the record), runningtotal should show 1 for record 1, 2 for record 2 etc in my test but it seems to be incrementing by strange amounts (1 then 4 then 7 then 20!)"

indicates what I'm trying to get across to you, you have OTHER rows in the detaiuls section. you need to discover how YOU eitehr suppressed them or used a Group Record Selection on them.

Crystal isn't making up numbers when it's displaying the incrementing, you are either suppressing at the detail level, or you are filtering in the record selection group level, or something akin to that.

I don't know how to explain it any better, you ignored:

"Sounds like you're using suppression or a group selection formula so you have other rows in there."

and started chatting about group suppression, I hadn't suggested that you were using group suppression, and since you're summarizing at the detail level, it wouldn't make much sense to look in at the group level, right?

Please, take the time to explore what I've suggested.

-k

 

[tezzyr]

please bear in mind that ive not had ANY experience with cr synapsevampire and I apologise for anything that comes across as though i'm ignoring your advice, I'm trying not too but i'm finding it hard to understand some of what you are suggesting.

I was handed a report and told to amend it so that it showed a total number of hh:mm:ss in the footer of each group. there is no database field in the database that displays with each record, just a formula which takes the datediff between two dates and outputs it as a string hh:mm:ss. as i couldnt simply create a running total or summary from this (as it is a string) I converted the values to numbers, and sucessfully been able to calculate the amount of seconds between two dates. this is outputted in the formula mentioned earlier.

I have no clue how to see if i have anything suppressed in the details section, i looked at section expert and suppress was unticked, only thing ticked is free form placement.

looking in record selection formula i see
{READER.TIMEATT} = 1 and {READER_OUT.TIMEATT} = 2 and
{EVENTS.EVENTIME} < {EVENTS_OUT.EVENTIME} and

(({EVENTS.EVENTTYPE} = 0) or
// Duress
({EVENTS.EVENTTYPE} = 2 and ({EVENTS.EVENTID} = 0 or
{EVENTS.EVENTID} = 2)) or
// APB
({EVENTS.EVENTTYPE} = 3 and ({EVENTS.EVENTID} = 4 or
{EVENTS.EVENTID} = 5)))
and

(({EVENTS_OUT.EVENTTYPE} = 0) or
// Duress
({EVENTS_OUT.EVENTTYPE} = 2 and ({EVENTS_OUT.EVENTID} = 0 or
{EVENTS_OUT.EVENTID} = 2)) or
// APB
({EVENTS_OUT.EVENTTYPE} = 3 and ({EVENTS_OUT.EVENTID} = 4 or
{EVENTS_OUT.EVENTID} = 5)))

these relate to other fields placed in the details section.

looking in group selection formula, it is blank.

I thank you for your help and apologise if i'm annoying you in any way with my ignorance, this is all new to me.