Crystal Reports: Calculate Inside a Crosstab

Crosstabs are excellent for summarizing a large block of data into a concise grid. We have developed some useful reports with the crosstab as its primary object.

Usually you use a crosstab to sum or count a field in the grid. You have all the other Crystal Summaries available like DistinctCount, Maximum, Minimum and all those statistical functions.

There is something else you can do, as well. Ken Hamady has developed a technique to perform calculations on the crosstab fields themselves. He makes particular use of the "DisplayString" property that appeared in Crystal Reports 9. (This technique requires version 9 and later).

You need a summary for each field, and for the calculation you want to add to the report.

In the example below this article text, we have calculated the value of each sale (in blue) and the Retail value of the sale (in black). The third summary is a copy of the Retail value and this is the value we are going to format.

On the first summary, we used conditional formatting to set the color to blue and to save the field value in a global variable. The conditional color formula is"

WhilePrintingRecords; Global Numbervar SellValue:=CurrentFieldValue;

Blue

The third summary field uses conditional formatting to change the display value. Set the Display String property using a conditional formatting formula of

WhilePrintingRecords; Global Numbervar SellValue;

ToText(CurrentFieldValue - SellValue)

There could be some risk of confusion in using this technique because the formula in the cross tab does not reflect the final displayed value. We need to remember to look at the format property of DisplayString if not understanding where a value comes from.

This example might be simpler than your real world requirements.

You could solve this problem using a formula to calculate the discount on each sale, and then accumulate that formula in the third summary. If your calculations use addition or subtraction, then we would recommend you take that approach and use a formula.

But if you want to calculate a ratio, multiplication and division require these variables and the display string approach.