	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize

	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: Analyzers Bestsellers CR Schedulers CR UFLs CR Viewers DataBase Tools Graphics International Mail UFLs ReCrystallizePro
	Add'l: About us Contact Us cViewSUITE Ppt Support
	CrystalReports on Steroids

Crystal Reports Basics: Cross-Tab Analysis

This is based on the book, Crystal Reports: A Beginner’s Guide. For more detail and explanation, plus practice exercises, order the book here.

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.