Use a variables to interrogate data during the processing of the crosstab.

Ken Hamady found an interesting way to calculate additional totals while processing a crosstab. Ken describes in his newsletter how to use the conditional formatting functions of cells to reset and load variables inside a crosstab. Ken describes in detail how to use the cell formatting with variables. Check out his newsletter at  http://www.kenhamady.com

Luqman from Pakistan takes technique a step further to calculate ratios of summaries inside a crosstab. His crosstab has three summary fields, a Quantity total, a Sales total, and the third displays the average selling price.

Luqman uses the Display String conditional formatting to store the Sales total in a formula with 
GLOBAL NUMBERVAR MYVALUE;
MYVALUE:=TONUMBER(CURRENTFIELDVALUE);
TOTEXT(MYVALUE,2)

All this formula does is store the CurrentFieldValue in a global variable MyValue, and then display the summary. The ToText function is needed because the Display String property has to be a string value.

Then in the third summary is the Quantity summary again, but he uses the previously saved variable to calculate the ratio of the Sales/Quantity with 
GLOBAL NUMBERVAR MYVALUE;
IF MYVALUE>0 AND CURRENTFIELDVALUE>0 THEN
totext(MYVALUE/currentfieldvalue)

These two techniques display the real power of the crosstab objects. 

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.