Doing a Return Rate Formula Based on Cross Tabs 1

[longlam]

I'm having a problem creating a formula that will give me the return rates for all my items. All the transaction I'm looking for is based on the same table. I have filtered it so that I only get invoices and credit memos. I have pushed this data into a crosstab report so and used transaction type as the column so that it splits up the invoices and the credit memos. Now I want to create a formula based on the two columns so that I can get the return rate (credit memos/invoices *100). I have yet been able to figure out how to do this. Also, where would I place the forumla on the cross tab? Or can I even do that? Lastly, if I grouped by item but would like more information about the item such as it's size and color which is stored in different fields, how can I append it to the cross tab table so it appears on the same row as the item?

Thanks for the advise in advance!

 

[lbass]

You should always post your CR version. It is particularly relevant to crosstab functionality.

-LB

 

[longlam]

Sorry about that, new around here. We are running the basic version of Crystal XI.

 

[lbass]

Basic? Meaning standard?

Anyway, try the following. Remove the column on transaction type, and create these formulas:

//{@Invamt}:
if {table.transactiontype} = "Invoice" then {table.amt}

//{@Cramt}:
if {table.transactiontype} = "Credit" then {table.amt}

//{@0}:
whilereadingrecords;
0

Add the above formulas as summaries in the crosstab, in the order noted. Go to the customize style tab and choose "horizontal" for the summaries and also check "show summary labels".

Add the item as the row field. Then select the {@invamt} summary in an inner cell->format field->common->suppress->x+2 and enter:

whileprintingrecords;
numbervar invamt := currentfieldvalue;
false

Then select the {@Cramt}format field->common->suppress->x+2 and enter:
whileprintingrecords;
numbervar cramt := currentfieldvalue;
false

Then select {@0}->format field->common->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar invamt;
numbervar cramt;
if invamt > 0 then
totext(cramt % invamt, 2) + "%"

-LB

 

[longlam]

For some reason the percentage is only coming up for the total row and not for the individual rows. Any ideas? All the other values are showing up. Thanks for the help so far!

 

[longlam]

Forget it, I got it. Thanks! How about adding additional item information and not having the cross tab group it together?

 

[lbass]

If there is a one to one relationship between these fields and the item number, you can add them as additional row fields.

-LB

 

[longlam]

The only problem is all the additional total rows that are created. Is there a way to hide that row?

 

[lbass]

Yes, go to the customize style tab in the crosstab expert and check suppress subtotals for each row.

-LB