Crosstab percentages in CRXI 1

[jazbar]

Hi all
I am using CR XI connecting to AS400.
I wonder if anyone can help me with a formula for the calculation of a percentage inside of a crosstab.
I have a sales report made up of a crosstab which displays the month for the columns and for the rows the brand that has been sold.
I currently display 3 figures in each cell of the crosstab as per below:
>No of Units Sold (Sum)
>Sales Value for that Brand (Sum)
>Profit made for that Brand (Sum)
What I want is a 4th figure which will display the Profit figure as a percentage. When I try to use a formula to calculate ie (sum({Profit})/sum({Units Sold}))*100 the figures aren't correct when I add it as a summarised field. I have changed the summary type to Sum, Avg, etc but it won't display the correct figures.
Can anyone help please.
Thanks in advance.

 

[lbass]

You can do this by first creating a formula to act as a place holder:

//{@0}:
whilereadingrecords;
0

Add this as your fourth summary. Then in preview, select the summary for units sold->format field->common->suppress->x+2 and enter:

whileprintingrecords;
numbervar units := currentfieldvalue;
false

Then select the profit summary and go the field suppression formula area for that field, as previously, and then enter:

whileprintingrecords;
numbervar profit := currentfieldvalue;
false

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

whileprintingrecords;
numbervar units;
numbervar profit;
totext(profit%units,2)+"%" //2 decimals

If profit is a currency, then change the variable type for profit to currencyvar.

-LB

 

[jazbar]

That's fantastic LBass! Thank you very much.

 

[jazbar]

Hi LBass
Sorry to bother you again(!) but I've tried your suggestion (which is excellent) and I've come across a slight problem in that if the number of units sold is 0 then the formula
"whileprintingrecords;
numbervar units;
numbervar profit;
totext(profit%units,2)+"%" //2 decimals" throws up a "Division by Zero" error. Unfortunately I've tried my hardest to rectify this myself without any success and I was wondering if you could help.
Thanks again in advance

 

[lbass]

Change the formula to:

whileprintingrecords;
numbervar units;
numbervar profit;

if units <> 0 then
totext(profit%units,2)+"%
else ""

-LB

 

[jazbar]

Amazing! Thanks ever so much again!

 

[jazbar]

Hi All,

A follow up to this thread that works superbly well for anyone who wishes to take lbass' excellent advice.

I was wondering if there is any way that anyone knows of where the crosstab can be sorted by the formula's results in a decending order, so that the most profitable brand occupies the first row, the second most profitable in the second, and so on.

I've read that highlighting the crosstab and selecting Group Sort works in normal circumstances, but in this instance as the formula results are calculated whileprintingrecords (pass 2) and the group sorting happens at pre-pass 2 I don't think CRXI will allow it.

Any ideas?

Thanks for your help.

 

[IdoMillet]

You can use a Command or a View to return the information as pre-summarized values. If you need to keep the detail in other parts of the report, just insert a subreport based on this summarized data and place the crosstab in that subreport.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com