cancel
Showing results for 
Search instead for 
Did you mean: 

Manual Running Total

Former Member
0 Kudos

Post Author: cjsmile2106

CA Forum: Formula

Hello everyone! I need some help and hope you guys can do it

I have the following formula:

@extra linked

if ({machine.type} = 20 and {production.status} = 11 and {employee.descr} = next({employee.descr})) then 1 else 0

That formula seems to be working fine for me. I also need to Sum that formula. I do not have the option to insert a Sum based off that formula so I created a manual running total to perform the Sum. However my manual running total is not living up to expectations. I'm hoping you guys can tell me what I'm doing wrong. Here's my running total:

@Initialize (placed in the header)

WhilePrintingRecords;NumberVar RunningTotal;RunningTotal := 0;

@Evaluate (placed in the details section)

WhilePrintingRecords;NumberVar RunningTotal;RunningTotal := RunningTotal + {@extra linked}

@display (placed in the group footer)

WhilePrintingRecords;NumberVar RunningTotal;RunningTotal;

My running total display is not summing. It only shows the value "1". I'm using crystal 8.5.

Thank you!!!

Accepted Solutions (0)

Answers (17)

Answers (17)

Former Member
0 Kudos

Post Author: V361

CA Forum: Formula

Could you post the complete working formula, just so we can file it away for future reference.

Former Member
0 Kudos

Post Author: synapsevampire

CA Forum: Formula

Yep, a known "feature" of repeat group header.

Note that if you place a details formula of:

if {table.field} = 1 then1else0

And place it in the details, you can use conventional summaries/aggregates (right click and select insert->summary) against it. tis requires a recent version of crystal, older versions may not allow it.

You need to allow for all rows though in the code, hence the ELSE 0.

Best coding practices suggest doing this anyway.

-k

Former Member
0 Kudos

Post Author: Jagan

CA Forum: Formula

If you find that you do want that option then you can use InRepeatedGroupHeader to check for it, e.g.WhilePrintingRecords;NumberVar MyRunningTotal;if not(inrepeatedgroupheader) then MyRunningTotal:=0; Why did it reset it on each page? Because you were running the group header formula on each page...

Former Member
0 Kudos

Post Author: cjsmile2106

CA Forum: Formula

I figured it out. In the group options I had "Repeat group head on each page" selected. When I de-select this it works perfectly. I'm not sure why that would have it reset on each page, but I can live without that option.

Thank you again everyone!!!

Former Member
0 Kudos

Post Author: yangster

CA Forum: Formula

I would suspect you have some sort of start new page after or new page before checked off somewhere on the reportthe running total should not reset after each page unless the next function you are trying to use is breaking on the end of each pagetry omitting the next part of your if statement to test if this is the case

Former Member
0 Kudos

Post Author: cjsmile2106

CA Forum: Formula

Ah the light bulb just turned on!!! I understand what you and synapsevampire are saying now. It it seems to be working (sort of). One problem I'm running into is that it seems to be reseting after each new page instead of each new group name. I've double checked I've that the @initial and the @display are in the group header and footer.

Former Member
0 Kudos

Post Author: yangster

CA Forum: Formula

WhilePrintingRecords;NumberVar MyRunningTotal;if ({machine.type} = 20 and {production.status} = 11 and {employee.descr} = next({employee.descr})) then MyRunningTotal:= myrunningtotal + 1 you need to increment the running totalyour original formula simply state that the variable will always be 1 which does nothingthe reason you can't create a sum on the above formula using the summation function is because you have the next function in your if statementeverything should work fine now as your display will show you the total summed value

Former Member
0 Kudos

Post Author: cjsmile2106

CA Forum: Formula

Sorry for the delay in reply, I've been out for a few days. Heres what I have:

@initial (groupheader)

WhilePrintingRecords;NumberVar MyRunningTotal:=0;

@evaluate(details)

WhilePrintingRecords;NumberVar MyRunningTotal;if ({machine.type} = 20 and {production.status} = 11 and {employee.descr} = next({employee.descr})) then MyRunningTotal:= 1

@display(groupfooter)

WhilePrintingRecords;NumberVar MyRunningTotal

Everything here seems to be working fine except the @display formula. The @evalutate formula is doing exactly what I need (it's only placing a 1 if it satisfies the if statement, everything else is a 0). The biggest problem is the @display formula. I can't get it to Sum the @evaluate formula. I always receive and error "The summary / running total field could not be created". I've tried multiple different way, always with the same result. I've also tried inserting just a normal if then formula as upbove in the details section and tried the insert Sum option, but it does not exist on this forumla. Thank you guys for your patience and expertise!

Former Member
0 Kudos

Post Author: pwilliamsbssp

CA Forum: Formula

Could I use the same method to concatenate a string of values to show in the group footer? And if so, how would I modify - I tried the formula with "&" but I just get blank values for all formulas. My background is in VB not Crystal so I don't completely understand the syntax.Values would be user initials in detail sections but I'd like to see a list of all user initials in the group footer (separated by a comma).P.Post note: I was able to get this to work with following - I had an error in my evaluate formula that prevented it before. Regardless, couldn't have done it without the forum posts. Thank you.@Initialize:whileprintingrecords;stringvar UserNameString:= ""@Evaluate: whileprintingrecords;stringvar UserNameString;if len(UserNameString) < 2 then UserNameString:= {@UserInitials}else UserNameString:= UserNameString & ", " & {@UserInitials} @Display:WhilePrintingRecords;stringvar UserNameString

Former Member
0 Kudos

Post Author: synapsevampire

CA Forum: Formula

Post your formulas.

As yangster mentioned, the code looks OK, so you need to post what YOU used.

-k

Former Member
0 Kudos

Post Author: yangster

CA Forum: Formula

I don't know what you were expecting but that's what a manual running total does. It increments by one for every time the value evaluates to true. Is this not what you are after??What do you mean by its not resulting after it finds a match?

Former Member
0 Kudos

Post Author: cjsmile2106

CA Forum: Formula

No that did not work either. I can see it counting in the details section but its counting incorrectly. Its not resetting after it finds a match. For example I want it to only pace a 1 if it satisifys the if then statement, however its doing a running count 1,2,3,4... Also it's not totaling in the group footer, its always zero. Thank you!

Former Member
0 Kudos

Post Author: synapsevampire

CA Forum: Formula

Try:

@Initialize (placed in the GROUP header)WhilePrintingRecords;NumberVar MyRunningTotal:=0;

@Evaluate (placed in the details section)WhilePrintingRecords;NumberVar MyRunningTotal;if ({machine.type} = 20 and {production.status} = 11 and {employee.descr} = next({employee.descr})) then MyRunningTotal:=MyRunningTotal+1 @display (placed in the group footer)WhilePrintingRecords;NumberVar MyRunningTotal

Former Member
0 Kudos

Post Author: cjsmile2106

CA Forum: Formula

I'm glad to hear I might be doing something right. I have placed the @extra link formula in the details section and it seems to be calculated correctly (I see it's putting the zero's and one's in the correct area. However when I place it in the details section and right click on it I DO NOT see an option to insert summary as I would normally. Nor is it in the insert menu at the top. I double check that the field is a number. Any other ideas? Thank you!

Former Member
0 Kudos

Post Author: synapsevampire

CA Forum: Formula

Place the @extra linked in the details and see how many you get per group.

Your logic seems fine, so I think you are misunderstanding the data.

btw, once you place the formula:

if ({machine.type} = 20 and {production.status} = 11 and {employee.descr} = next({employee.descr})) then 1 else 0

in the details, you can just right click it and select Insert->Summary->Sum and get the totals by any group or a grand total.

-k

Former Member
0 Kudos

Post Author: cjsmile2106

CA Forum: Formula

I'm grouping by {state.descr}. Also {employee.descr} is a field I'm using to assist in checking for extra links per employee. Each employee should have only one. I'm not sure what you mean by the two other things in the @evaluate formual.

Former Member
0 Kudos

Post Author: Jagan

CA Forum: Formula

What is the group you mention? {employee.descr} ?Two other things that may make no difference whatsoever, in the @Evaluate formula:WhilePrintingRecords;evaluateafter({@extra linked});NumberVar RunningTotal;RunningTotal := RunningTotal + {@extra linked}; //; added