totalling derived columns - Crystal XI

[pjonp]

Hi,

I am about to lose all faith in this product.....

The story so far:

I am dispaying the contents of a simple table such as this:

PERSON DEPT COST COUNT

I need to create summary reports of cost and counts for people (this is OK) and departments (problem). I have created a summary group on dept, and use running totals (per dept) to calculate the total cost and total count. This works OK. I also use running totals in this report to show the grand total of cost and grand total count. All fine.
Then I want to have a another (derived) column i.e. cost / count. So per dept I can either
1) use a formula that takes the running totals of cost and count and do a simple division to get cost/count, OR
2) use a formula to Sum the values of cost and count (Sum(fld, condfld)) to summarize these values per dept and then divide again
Both work fine. THEN: I wish to total the above values and divide by the number of distinct groups. So i think, create a running total using the formula I just created above. No way - this is not allowed - something about creating a running total on a print time value!!!!

Does this mean that Crystal does not total derived column values ?

Please help,

 

[lbass]

From your description, it sounds as though you could use formulas like:

sum({table.field})/distinctcount({table.groupfield})

In other words, eliminate the group condition at the grand total level. You could also set up running totals at the grand total level (no reset) and divide by the distinctcount.

If you don't need running totals, it is better to use inserted summaries, as they process faster.

-LB

 

[pjonp]

Hi

Thanks very much - this is basically what I am doing. I do however need to use running totals at the grand total level - but when I open the dialogue to create the running total (on a summary column), the formula does NOT appear on the left hand side of the Create Running Total dialogue, so i cannot pick it to finish creating the total. So it seems i cannot create a running (grand) total on a formula such as

Sum ({TABLE.COST} , {TABLE.DEPT}) / Sum ({TABLE.COUNT}, {TABLE.DEPT})

Ideas?

thanks in advance.

 

[lbass]

I don't see why you can't do what I previously suggested, and you didn't explain why you need running totals at the grand total level. However, if you need the running totals, then use a variable with these formulas:

//{@accum} to be placed in the group header or footer for department;
whileprintingrecords;
numbervar sumx := sumx + Sum ({TABLE.COST} , {TABLE.DEPT}) / Sum ({TABLE.COUNT}, {TABLE.DEPT});

//{@display} for the report footer:
whileprintingrecords;
numbervar sumx;

If cost is a currency, then change "numbervar" to "currencyvar" in both formulas.

-LB

 

[pjonp]

Hi thanks very much - this looks like the solution i want.

Many thanks lbass.