Totalling a derived column

[crystalreps]

Hello all
I have been learning Crystal for the last 2 weeks, but I am stuck on this:
I have a 3 column report, where column 3 is derived from column 2 via an if then formula, where each of the figures in column 2 is divided by a different value depending on what is in column 1. I have this bit working fine, but I cannot find any way to get a total for column 3. The insert subtotal or total sections does not recognise the derived column. What am I doing wrong, or is there another solution?

 

[IdoMillet]

One of the columns used to derive the 3rd column is probably an aggregate function or a PrintTime formula. Such columns cannot participate in further aggregations since they are computed in the same "report pass" where aggregation is taking place.

You can probably solve the problem using Variables but if you explain the situation in more detail it would be easier to suggest a solution.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Ngolem]

ok...so Column 3 .... whatever the value is must be totaled

As I understand your report you have a formula for Column 3 that is something like

WhilePrintingRecords;

If {table.column1}= something then
{table.column2}/constant1
else
{table.column2}/constant2;

As Ido said you can use a variable here to do a manual count... something like this

First Initialize a variable to zero in the Group header for the values or if this report has no repeat grouping place it in the Report header

@Initialize (suppressed in report or group header)

WhilePrintingRecords;

//this is needed if placed in a group header to prevent
//an accidental reset
if not inRepeatedGroupHeader then
NumberVar Col3Value := 0;


Now modify your Column 3 formula to look something like the following

WhilePrintingRecords;
NumberVar Col3Value ;
numberVar Temp;

If {table.column1}= Something then
Temp := {table.column2}/constant1
else
Temp := {table.column2}/constant2;

Col3Value := Col3Value + Temp;

Temp;

Now create the display formula for the total

@DisplayCol3Value

WhilePrintingRecords;
NumberVar Col3Value ;

Col3Value ;


That's all there is to it.



Jim Broadbent

 

[IdoMillet]

If Jim's scenario (rather than having an aggregate formula somewhere) is indeed the case, all you need to do is remove the
"WhilePrintingRecords;"
and the original formula would become available for aggregation.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Ngolem]

I prefer the "whilePrintingRecords" in any formula which doesn't specifically deal with a summary action or grouping. It garuantees the timing of the operation.

The modification to the "original formula" as I laid it out is necessary since this new formula performs 2 purposes.

1. It calculates and displays the value for column 3
2. It totals these values for later display.

The original formula does not do that...it only displayed the value. Probably the reason for CrystalReps inability to use subtotals and totals was for some other (undisclosed) formula reason.

As you are aware, from reading my posts, I am not a great fan of using Running Totals...I prefer the older (and by me anyway better understood) method of manually doing totals. They are not hard to do this way and quite easy to diagnose problems....it is just me....I offer advice ...take it or leave it.

Your way probably works...but it is not my way.

Jim Broadbent

 

[IdoMillet]

Jim,

It is true that in many cases "WhilePrintingRecords;" is a "safe bet", but in many cases it is not needed and can unnecessarily limit sorting and aggregation functionality.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Ngolem]

my style...I have never had such a problem as you describe....but then I do most of my totaling manually

Jim Broadbent