Insert a Running total or a summary on a formula field that 2

[sfunk]

Hello,

I am having trouble Inserting a Running total or a summary on a field that should be a numerical value.

When I right click on the formula field the "Insert Summary" is not enabled. I have verified that the data in the formula is numerical, I have also used ToNumber() on the value. What am I doing wrong? The formula field looks like:
{@JTT_Summary_Print} / Count({CR_JOB_TENANT.ESTIMATEDTIME}) Sincerely,
Steve Funk

 

[emcevoy]

Hi

Unfortunately, Due to the processing time of the formula you are using, you can not create a summary field base on that formula. What you will need to do is use running totals. Here is documentation on using manual running totals.

MANUAL RUNNING TOTAL USING FORMULAS

There are three formulas you are to create. One is to reset the variable you are going to create to contain the total of your running total. One is to do the actual calculation and the last one is to display the running total.

The Reset Formula
The following formula is to be place in your group header so it will reset the variable on the change of each new group. X is the name of the variable
Whileprintingrecords;
Numbervar X := 0

The Calculation Formula
Place this formula in the detail section.
Whileprintingrecords;
Numbervar X:=X +{fieldnametoaddup}

***For duplicate records the formula would look like this***
Whileprintingrecords;
If onfirstrecord or previous({fieldname})<>{fieldname} then
Numbervar X:= X + 1

***For Conditionally Suppressed Records***
You will have to include the suppression condition in the formula for example:
Lets say you have record #51 suppressed then the formula should look like this:
If {fieldname} <> 51 then numbervar X:=X+1
This means the running total will only add one for every record as long as it is not 51.

The Display Formula
Place this formula in the group footer to display the result
Whileprintingrecords;
Numbervar X;

Please let me know if this helps. Eileen McEvoy
Crystal Reports Consultant and Trainer

http://www.pacificridge.ca

emcevoy@pacificridge.ca

 

[dgillz]

This has nothing to do with the processing time, it is because you cannot summarize a summary function such as Count().

Use running totals or the 3 formula approach with variables as suggested above.

See faq767-2300 for examples of other formulas that cannot be summarized. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[emcevoy]

Hi

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples).

Cyrstal Reports uses a two-pass reporting process (this is the processing that I was referring to) to provide greater reporting capabilities. It is also largely responsble for determining what is and is not possible to do in Crystal Reports.

Understanding this two-pass process will explain many common questions, such as:

1. Why does a formula produce the error?

&quot;The function cannot be used because it must be evaluated later&quot;?

2. Why am I able to insert summaries on some formulas, but not on others?

3. Why are my grand total and/or summaries incorrect after performing a group selection or Top N/Bottom N sort?

Answers to these questions are:

1. Why does a formula produce the error?

&quot;The function cannot be used because it must be evaluated later&quot;?

Answer: This error usually happens when trying to insert a second pass function into a record selection. Since Crystal Reports evaluates record selection formulas during the first pass of the data, they cannot contain second pass functions.

In general, second pass functions are functions that require data from more than one record. Examples of second pass functions (also known as WhilePrintingRecords or print-time functions) are Next, Previous, TotalPageCount, NextIsNull, PreviousIsNull.

2. Why am I able to insert summaries on some formulas, but not on others?

Answer: Crystal usually evaluates formulas during the first pass of the data. These formulas are known as recurring formulas. However, if the formula contains a second-pass function (these functions are explained in the answer for (1), above), this forces Crystal Reports to evaluate the formula during the second pass instead.

Crystal Reports creates summaries shortly after evaluating any recurring formulas during the first pass. Since SCR has yet to evaluate the second pass formulas, it cannot create a summary for them.

To summarize second-pass formulas, create running totals using variables and the WhilePrintingRecords function.

3. Why are my grand total and/or summaries incorrect after performing a group selection or Top N/Bottom N sort?

Answer: While record selection is evaluated during the first pass, group selection is evaluated in the second pass. Because Crystal Reports creates summaries in the first pass (therefore prior to evaluating group selection), the summaries don't reflect the groups that have been suppressed from the report.

To correctly summarize and/or grand total a report that has a group selection formula, creating running totals, either using variables and the WhilePrintingRecords function, or with the Running Total Expert (available only with version 7 and 8).

Crystal Decisions has an excellent document that describes this process in detail and will give more information on why if you have a count() or Sum() function in your formula you would not be able to summarize on it.

You can find this document at

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr7_evaluationtime.pdf

Hope this gives some detailed information for you when creating your reports. Eileen McEvoy
Crystal Reports Consultant and Trainer

http://www.pacificridge.ca

emcevoy@pacificridge.ca