Cross-tabs are a powerful tool for creating summaries of data in a spreadsheet style format. They generate summary data in a grid where the rows and columns represent groups of data. This provides the user with a report format that is easy to read and uses a small footprint on the page.

Cross-tab reports are not for the faint of heart. Although they are easy to use once you get the hang of them, the act of taking data and reformatting it in a grid format can be a little intimidating at first. But after this chapter, you should have a good understanding of how to use cross-tab reports and you’ll also get some practice with creating them.

Understanding Cross-Tab Reports

Cross-tab reports format groups of data into an easy to read grid format. This grid format is very similar to the way a spreadsheet represents data. It lets the user visually analyze the data in a way that makes it easy to compare values in one group against the values in another group. Before we see what a cross-tab report looks like, let’s look at a typical report that summarizes data within groups. After looking at Figure 13-1, we’ll see it reformatted as a cross-tab report.

Figure 13-1. Grouping by Product and Quarter.

This report has two grouping fields. The outermost group is by Product Type and the innermost group is the Order Date grouped quarterly. The group header for the Order Date is the first date in the period. The detail records show you the Employee ID, Order Date, and Quantity. There are two sub-totals of the quantity. The first occurs on the change of quarter and the next is on the change of product type.

This is a pretty standard grouping report and it shares a common problem with other grouping reports: the sub-total amounts are spread out across multiple pages. This makes it hard to compare numbers because they aren’t consolidated into a single page. A user reading this report will find that they are continuously flipping pages to see how the sales of one product compare to the sales of another product.

Re-writing this report as a cross-tab report eliminates this problem. This cross-tab report takes two grouping fields and makes them the X-axis and Y-axis of the grid. Figure 13-2 shows the same report in cross-tab format.

Figure 13-2. Cross-tab report by Product and Quarter.

The cross-tab is much easier on the eyes. The outermost grouping field on the original report, Product Type, is represented on each row of the grid. The two columns represent the innermost group field, Order Date grouped by quarter. These columns span horizontally along the page. Notice that the cross-tab doesn’t have any of the detail records listed. Detail records are used to calculate the summary data, but they are never shown. Only the summaries are listed.

The data inside the grid corresponds to the subtotals on the grouping report. The first row is for the Competition product type. It shows values of 7, 5 and a total of 12. When you look at the grouping report in Figure 13-1 you see that these match the subtotals for the Competition product type. Each row in the cross-tab report shows the same subtotals that are displayed in the grouping report for the product type groups. Thus, the cross-tab report took the sub-totals of a grouping report and formatted them as a grid. All the data is summarized into a very compact space and it doesn’t span many pages like the grouping report would.

It helps to think of a cross-tab report as taking a multi-group report and just copying the group subtotals into a grid.