Chapter 11: Analyzing with Cross-Tabs

Overview

Database report writers and spreadsheet programs are typically considered to be two completely separate products. The database report writer sorts and selects data very well, while the spreadsheet is great for analyzing, totaling , and trending in a compact row-and-column format. Crystal Reports provides a tool that, to a limited extent, brings the two features together: the cross-tab object. A cross-tab is a row-and-column object that looks similar to a spreadsheet. It summarizes data by using at least three fields in the database: a row field, a column field, and a summarized field. For each intersection of the row and column fields, the summarized field is aggregated (summed, counted, or subjected to some other type of calculation).

Consider two common summary reports. The first summary report shows total sales in dollars for each state in the United States. The second report shows total units sold by product type. If a marketing analyst wanted to combine these two reports to more closely analyze both sales in dollars by state and units sold by product type, you would be limited in what you could offer with the standard grouped summary report.

You can create a report, similar to that shown in Figure 11-1 (initially grouped by state, and within state grouped by product type), that provides the information the analyst desires. But if the analyst wanted to compare total mountain bikes sold in the country with total kid s bikes sold in the country, this report would make the process very difficult. Product type is the inner group , so there are no overall totals by it. Also, comparing Alabama totals with Illinois totals would be difficult, because they would be several pages apart on the report.

Figure 11-1: Standard summary report with two groups

This scenario is a perfect example of where a cross-tab object would be useful. A cross-tab is a compact, row-and-column report that can compare subtotals and summaries by two or more different database fields. Whenever someone requests data to be shown by one thing and by another, it s a cross-tab candidate. Just listen for the by this and by that request. Figure 11-2 will probably be much more useful to the analyst.

Figure 11-2: Cross-tab showing units/dollars by region and by product type