adding formulas to a cross tab

[atarrqis]

Crystal 11 and Oracle
I have a cross-tab in my report header with column = Labor, rows = Part, Order#, Qty Complete and Sum = sum of Costs.
This works fine and Costs are totaled correctly. I also sum Qty Complete per Part. Now I want to use these sums to determine avg cost = sum of Costs/Qty Complete.
This seems simple but I have no experience with cross-tabs.

 

[lbass]

Is Qty Complete another summary or a row? If it is a summary, are you using a sum on it? At what level do you want to see the avg cost? Grand total? Per some row or column or?

-LB

 

[atarrqis]

Qty Complete is a Row which I bring into Summarized Fields. I suppress this except at Part Total. I need the Average at Part which is the top of my Rows. My column is Costs but this runs for many depts. A cross-tab seemed easier then defining a formula for every dept. I need lots of other formulas similar to Average but assume if I could figure out one I could figure out the others.

 

[lbass]

Please start over and go into the crosstab expert and identify the fields used for Rows, Columns, and Summaries.

-LB

 

[atarrqis]

Sorry, I confused you.
Column = Labor
Row = Part, Order, Qty Complete (in that order)
Sum = Cost, Qty Complete

 

[chelseatech]

The first thought is that "Average" is one of the summary options available in the Crosstab Expert.

What version of Crystal Reports are you using? Crystal Reports 2008 includes a neat Embedded Summary Feature where you can take two summaries and calculate a new summary using them.

it is possible to do this with older versions. Ken Hamady published a technique for this several years ago. Check out the back issues of his newsletter.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

Add a third summary (below the other two in the crosstab expert) based on a formula:

whilereadingrecords;
0

Then selet the cost summary->right click->format field->suppress->x+2 and enter:
whileprintingrecords;
numbervar cost := currentfieldvalue;//change to currencyvar if cost is a currency
false

Then selet the qtycomplete summary->right click->format field->suppress->x+2 and enter:
whileprintingrecords;
numbervar qtycompl := currentfieldvalue;
false

Then select the {@0} summary->right click->format field->DISPLAY STRING->x+2 and enter:
whileprintingrecords;
numbervar cost;
numbervar qtycompl;
if qtycompl <> 0 then
totext(cost/qtycompl,2)//2 for two decimals

-LB

 

[atarrqis]

Thanks, I was able to do what you instructed but I now realize that the sum of Qty Complete is not correct. I don't know why. It correctly displays at the row but does not add up correctly. I realize now that it is only adding relative to where there is any cost instead of for every part/order.