Crystalkeen logo; we offer Crystal reports viewers and schedulers
 
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money

  Resources  

Articles:
Administration
Advanced
Basic
Database
Financial

Tools:

Analyzers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs



 
 

Add'l:
About us

Contact Us
 

CrystalReports
on Steroids

Crystal Reports Basics: Cross-Tab Analysis

This is based on the book, Crystal Reports: A Beginner’s Guide.

Cross-tabs are special objects you can place in your Crystal Reports. If you know what a datasheet (spreadsheet or Access datasheet view) looks like, you know what a cross-tab looks like. It’s a cross tabulation created by rows and columns.

Often, a cross-tab is the meat of the report. It doesn’t have to be, but you can use it that way. People are using to seeing rows and columns, so this is often an appropriate way to display information.

Keep in mind, a cross-tab is not a spreadsheet—it’s database-driven. Thus, when you view it in design view, you’ll see the layout—rows and columns—but not the data. In the preview mode, you’ll see the rows and columns full of the data you requested. And the more records your Crystal Report returns, the larger the cross-tab will be.

Some facts:

  • You cannot create a cross-tab without a summarized field.
  • Cross-tabs can crunch tens of thousands of rows of data to produce a concise summary.
  • Cross-tabs are read-only.
  • You can use groupings (ascending, descending, and specified order) and formulas with cross-tabs.
  • You save a cross-tab as a report and export it to Excel for further manipulation.
  • You can format cross-tabs a variety of ways, including doing so via pre-formatted styles.
  • All columns in a cross-tab must be the same width, unfortunately.
  • You can pivot cross-tabs (swap the position of the rows and columns)

Cross-tabs add functionality to Crystal Reports—the items above are merely the tip of the iceberg. For even more functionality, you can use third-party programs, such as the ones available here.

Also consider cross-tab wrapping.

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.

Authorship

Except where an author's name is given at the start of the article, all of these articles were written by Mo Naughton or Bruce Ferguson and edited by Mark Lamendola. Mo is a Crystal Reports consultant, trainer, and developer for Chelsea Technologies, Inc. Bruce Ferguson is a Crystal Reports consultant, trainer, and developer for CrystalKiwi, Inc. Mark Lamendola is a writer and editor with over 15 years experience in professional and trade publications.


 

  • Questions? Please write to mark@crystalkeen.com. We do want your business.
  • Do you have your own tips for Crystal Reports administrators and designers? Write to mark@crystalkeen.com and we'll post your tips with a link to your Website (or with some other attribution if you choose).
  • Crystal Reports is a subsidiary of Business Objects, which is owned by SAP.