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 then 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, 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 5, 7 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 many limitations. These limitations are discussed throughout this chapter, but let’s look at two obvious ones first.
The first limitation is that the original grouping report has a lot of data on it that isn’t shown on the cross-tab. For example, the cross-tab report doesn’t show the fields for Employee Id or the Shipping Date. In fact, it doesn’t have any detail records shown. Although these fields are very important, the cross-tab report can’t show individual detail records. This is because a cross-tab report can only show summary calculations.
The second limitation is that you can only print numbers in the summary fields. No text values are allowed. This is because each cell must calculate a summary function and summary functions can only return numbers. If you attempt to put a text field in the cell, then the report will default to printing a count of the text fields.
Given the benefits and drawbacks of cross-tab reports, you have to consider your alternatives before using the cross-tab report. The 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 quick analyze summary data, but you have to give up looking at the detail records that make up the data.
