Creating formula to count detail records 2

[jbhsuman]

Version CR9

I created a formula to count the number of records in the detail section based on the value of a running total field. The running total field is reset on change of group. The formula is inserted on the detail section of the report. As you run through the report the formula works fine until the last record on the report. If the last record meets the criteria, it adds it correctly in the details section. However, I want to display the count in the report footer and when I copy the formula to the report footer, it adds one more to the count. It looks like it adds one more because the running total field is not reset at the end of the report and therefore thinks it should count it again.

How can I either reset the running total value so it does not meet the formula’s criteria at the end of the report? Any suggestions?

Thanks

Joe

 

[FVTrainer]

What does your formula that is counting look like? Please post it.

 

[lbass]

If you are using a variable to do the count, as in:

whileprintingrecords;
numbervar count := count + 1;

Then you need to do a separate display formula for the report footer, since the counter will add one if you move it to a new section:

@display} for the report footer:
whileprintingrecords;
numbervar count;

If you use the running total editor instead, you can copy the running total from the detail section to the report footer without this problem.

-LB

 

[jbhsuman]

Thanks LB, I will try that and let you know the results.

 

[jbhsuman]

I must be doing something wrong. I tried your solution to create a new formula to display the total but it still added one more. I think it's because the original formula executes again.

I would like to use the running total editor but I could not figure out how to do a running total based on evaluating against a formula. When I go into the editor, I do not see my count formula listed as a report field. How do I make reference to the formula?

Any suggestions?

 

[lbass]

First, to use the manual running total display formula, you must remove the accumulation formula from the report footer, so that the display formula is the only reference to the variable in that section.

The running total editor is an alternative to the formula method. To use the running total editor to count, you would not refer to a count formula. You would select the field you want to count--pick an ID field that recurs with each record in your case. Then choose "count" or "distinctcount" (if you have duplicates), evaluate for every record, reset never.

Finally, if you ONLY want the count of detail records, select the ID field, right click->insert grand total->count or distinct count. This is the only method you need as long as you have no group selection or record suppression in the report.

If you want to show a running number with each record and you have no groups, you should go to the field editor->special fields->recordnumber and add this to your detail section.

-LB

 

[jbhsuman]

LB, thanks for you input. The reason I cannot use the running total editor is the details that I am basing my count logic does not have a specific field to identify which should be counted and which should not. Therefore I have several calculations I am working with to establish the criteria for the count. I use a running total field to sum the number of detail records. The detail records relate to the number of times an invoice was sent to a customer. This allows me to determine if there are more then one detail record and helps me to establish the criteria for my manual total calculations. Basically, the logic for the determining which manual calculation to perform is:

If detail records > 1 then this invoice has been sent previously.
If detail records = 1 then this is a new invoice.

The running total calculation.

Then I have two manual totals one counting the number new claims, the other resubmitted claims. I created formula’s to initialize the variable as global and placed the formulas in the report header. This should make the variables available throughout the report. (BTW I am using Basic Syntax)

global TotReSub as number
TotReSub = 0
Formula = TotReSub

global TotNew as Number 
TotNew = 0

Formula = TotNew[/code]

Then I created a manual count formula for each calculation dependant on if the count = 1 or is greater then 1. (remember count is a running total field).

//count the number of resubmissions

Global TotReSub as number;
if count >1 then TotReSub = TotReSub +1
Formula = TotReSub

//count the number of new claims

Global TotNew as number;
if count =1 then TotNew = TotNew +1
Formula = TotNew

The display formula as you had suggested I get a return value of zero.

When I creat a formula for display, it still increments the value by 1.

 

[jbhsuman]

FVTrainer. Thanks for responding. I posted my formula's in my last post to LB.

Perhaps you can shed some light on another question. When I try to us the formula with Crystal Syntax, I get a boolean return value even though I do not declair the formula or variable as a boolean. I am checking a condition in an if statement so I assume the boolean value is a result of the conditional statement but this does not happen with I use Basic syntax.

Example: where #count is a running total field

Global Numbervar Total; 

if #count > 1 then Total = Total + 1;

This formula returns a boolean value on the report rather than the numerical value of the variable total.

Also, if I declair a variable with a global scope in the report header, I am not able to refer to it without declairing it again. As in my above example, I can not refer to the numbervar total in another formula without the statement Numbervar Total in the formula. What am I doing wrong?

 

[FVTrainer]

In Crystal syntax, ":=", not "=" is the assignment operator (as in "x is set equal to the value y&quot. "=" is a comparison operator. So your formula,

if #count > 1 then Total = Total + 1;

translates to Crystal as "if #count > 1 then return the result of the boolean statement Total is the same as Total + 1.

Of course, this evaluation will always be false.

What you want to do is use the formula:

if #count > 1 then Total := Total + 1;

As for global variables, they always need to be declared in any and all formulas in which they are used. But the 2nd (or nth) declaration does not reset the value; it simply tells Crsytal you want to use that memory variable again. It's somewhat unique to crystal and is very unlike visual basic.

Are you still getting your counter incremented by one when you write a display formula for it?

 

[jbhsuman]

Fv, and LB, thank you both for your help and clarification. I was able to resolve my issues with your advice.

Joe