Crystal Reports: Fix Page Width with Wrapping Crosstab

Use a wrapping crosstab to eliminate those wide reports. Cathy Mitchisch demonstrated this at the CDUGNA conference presentation in New Orleans in May, 2004.

She was took a crosstab report that spread over several pages horizontally and wrapped the crosstab so it appeared on one page. It takes a little bit of preparation, but looks great.

Think of it as a crosstab relay. The data must arrive in the report already summarized and sorted.

We used a SQL command to preprocess the data:

Select Country, Region,
Sum("Last Year's Sales") as LYSales
From Customer
Where Country='USA'
Group by Country,Region

With one record for each cell, we could now use a WhileReadingRecords formula to accumulate a record counter in the report.

We called this formula {@EachRegion}

WhileReadingRecords;
Global Numbervar RegionNo := RegionNo + 1

We then created a formula to use as a group for each wrap of the crosstab {@GroupRegions}.

EvaluateAfter ({@Each Region});
Global Numbervar RegionNo;
Int((RegionNo - 1)/6)

We created a group on this formula. The 6 refers to the six regions that appear in each group. You need to subtract 1 of the RegNo variable because the first region is region 1, and if you don’t do that you get five regions in the first group and six in the rest.

We then created a crosstab and placed it in the group header. The Crosstab has a Row field of Country, a column field of Region and a Summarized field of LYSales (Sum).

If you want this to work for multiple countries, you will need the crosstab in a subreport and you then process the subreport for one country at a time.

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.