Adding Formula data to Cross tab Reports 1

[benramz]

Question on Crystal Report XI

I have a cross tab report which has dates as columns, Month over month.
Summaries are displayed in columns under the date.
the rows are Job type broken down by location. the Summary data is sum of web leads, phone leads and booked orders.

**I need to add a formula for each location of the form (Sum(booked Orders))/(Sum(webleads) + sum(Phone Leads)) to display Monthly and combined multiple month totals.

This could include more than 1 month of results

April 2011
WEB Phone BookedOrders ClosingRatio
JobType Location 3 4 2 28.5%

 

[lbass]

So would the multiple month total for closing ratio use the sum of each formula element without regard to month?

-LB

 

[lbass]

Make sure that your row totals are displayed on the right. Then select the inner cell and the total cells (row and column) for the WEB summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar web := currentfieldvalue;
false

Then repeat for phone and booked orders, using formulas like these in the suppress formula area:

whileprintingrecords;
numbervar ph := currentfieldvalue;
false

whileprintingrecords;
numbervar bo := currentfieldvalue;
false

Then in the field explorer, create a formula to act as a holder for the ratio:

whilereadingrecords;
0

Add this as your last summary. Then select it in inner cells and total cells->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar web;
numbervar ph;
numbervar bo;
totext(bo%(web+ph),1)+"%"//1 for one decimal

-LB

 

[benramz]

The total for multiple months would use the total booked orders / (total web leads + total phone leads)

I would just average the closing ratios but this would not be representative for longer time frames.

 

[lbass]

The method I showed should give you the first, not the average of the closing ratios.

-LB

 

[benramz]

Linda, thanks again! Have you considered writing a book that covers what the other books leave out?

 

[lbass]

Yes, maybe--but what do you see as missing--are there broad areas?

-LB