Cross-tab reports are a powerful way to create 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.

Ask a programmer how he or she feels about cross-tab reports and you will probably get a variety of answers – both good and bad. I think that programmers can be put into three general categories about their experiences with cross-tab reports. Some programmers have tried using cross-tab reports and found them to be too confusing. They shrug them off as being unworthy of the effort to learn. Other programmers have successfully used cross-tab reports, but found a variety of problems in getting the data that they wanted. This group uses cross-tab reports only when absolutely necessary. And last, but not least, is the programmer who has successfully mastered the cross-tab report and found it to be a great way of producing reports that quickly summarize groups of data. They gladly use cross-tab reports whenever appropriate. The goal of this chapter is to take you from being a beginner in writing cross-tab reports to the level of an expert.

Understanding Cross-Tab Reports

Cross-tab reports are a way of reformatting a report that groups data into a 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. Let’s look at the grouping report in Figure 11-1 and we’ll see it reformatted as a cross-tab report.

Figure 11-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. Figure 11-2 shows the same report in cross-tab format.

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

The cross-tab is much easier on the eyes. The outermost group field, Product Type, is represented on each row of the grid. The innermost group field is Order Date grouped by quarter, and makes each quarter a separate column. These columns span horizontally along the page. Although there are many detail records in the original report, these are ignored when generating the rows and columns. Only the values of the grouping fields are listed. The cross-tab report took two grouping fields and made them the X-axis and Y-axis of the grid.

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 11-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 might help to think of a cross-tab report as taking a multi-group report and just copying the group footers into a grid.

The benefits of using a cross-tab report can be offset by the drawbacks. As powerful as the cross-tab report is for summarizing data, it has a limitation that it can’t show any detail records. For example, the cross-tab report doesn’t show the fields for Employee ID or the Shipping Date. Although these fields are very important, they aren’t printed because a cross-tab report can only show summary calculations.

A standard grouping report is great for showing as much information as necessary and having control over the format. But the data could span many pages and this makes it hard to do analysis with. The cross-tab report gives you the ability to quickly analyze summary data, but you have to give up looking at the detail records that make up the data.

If you have a report that needs to benefit from both types of reports, a solution is to combine the two reports. Create a grouping report that prints all the necessary detail information. Then add a cross-tab object to the report header or group header. This lets a user see a summary of the critical information on the first page of the report and dive into the details printed on the remaining pages.