Calculate % of 1 column to another in Crosstab 1

[TennisNut]

Hi all,

CRXI

A record with negative amount = fee
A record with a positive amount = refund

Have Crosstab in Group Header (group is a dummy group: WhileReadingRecords; 1) like:

mo/yr Fees Refunds
01/10 1,595.00 375.50
02/10 975.80 140.65
etc

Mo/Yr is grouped by month in the Row Group Options
Fees & Refunds are from the same amount field as a Specified Order (amount < 0 = Fees / Otheres = Refunds) in the Column Group Options.

I want to create a new column for the % of Refunds to Fees (Refunds/Fees):
mo/yr Fees Refunds %
01/10 1,595.00 375.50 23.5
02/10 975.80 140.65 14.4
etc

Using Crosstab because I am creating other crosstabs, so can't use grouping and footers on report for just this crosstab and I want the detail to show.

Thanks,
jsr

 

[lbass]

I would create these as two separate summaries using conditional formulas and remove the column field, e.g.:

//{@fee}:
if {table.amt} < 0 then
-{table.amt}

Then format the summaries to display horizontally with labels in the customize style tab. Then select the fees summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar fees := currentfieldvalue;
false

Select the refunds summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar ref := currentfieldvalue;
false

Then add a third summary which sums a formula {@0}:

whilereadingrecords;
0

Then select this summary->right click->common tab->display string->x+2 and enter:

whileprintingrecords;
numbervar fees;
numbervar ref;
if fees = 0 then
"--" else
totext(ref%fees,1)+"%"

-LB