Planning the Rows and Columns of a Cross-Tab Object

Creating a cross-tab object can be a little intimidating the first time you create one. It can be confusing trying to figure out what to put in the rows and columns. A little planning prior to creating the cross-tab object can really help ease the process. When creating a cross-tab object, it helps to consider these tips.

The rows of a cross-tab object generally list the detail data. For example, it could show customers, employee names, or products. Since this data spans down the length of the page and continues onto the next page, you don’t have to worry about how many rows are displayed.

The columns of a cross-tab object are usually used to collect the data into groups. For example, the columns frequently display data grouped by date (month, quarter, year). Other common groups are department or region.

Later in this chapter, we’ll see that after creating a cross-tab object, Crystal Reports has a property that lets you easily switch the data in the rows and columns back and forth. Since it is so easy to change their orientation, you might think that it is a trivial choice. From a purely technical standpoint, this is true. But from a practical standpoint, there are other factors to consider.

The primary goal of a cross-tab object is to summarize a large amount of data and present it to the user in an easy to use format. The best location for the fields is dependent upon the type of data you are summarizing and your user’s preferences. For example, many cross-tab objects summarize dates by year and list them across the top of the page (in the column section). But, if your user prefers seeing the dates listed down the length of the page, then in this circumstance that is obviously the best way to do it because it meets the user’s needs.

When I create cross-tab objects, the biggest factor for me is the inherent page size limitation. Not only can cross-tab objects span down the length of multiple pages, but they can also span horizontally across the page and overlap onto multiple pages. When possible, I try to avoid having the cross-tab span across multiple pages because most users are not comfortable with having to lay pages side-by-side to view the full cross-tab object. And, if a cross-tab object is part of a standard columnar report, then the formatting can get even more confusing.

When I see that the cross-tab columns span horizontally across multiple pages, the first thing I do is switch the paper orientation from Portrait to Landscape. This allows more data to appear in the columns and, in many cases, everything fits on a single page.

Before creating a cross-tab object, I determine the amount of data required for each field. If one of the fields has significantly less data to show, I use that as the column field. For example, consider an annual sales report that lists the customer name and summarizes by the year. Many companies have a lot of customers so I choose this as the row field and use the year as the column field. Thus, the customer names will span multiple pages and the cross-tab object can be displayed along the length of the report in landscape mode. But, consider a company that only wants to show the top five customers and break down sales by quarter. Using the date as the column would cause the cross-tab to stretch across multiple pages and be more difficult to read. In this case, I would choose the customer name as the column and the sales quarter as the row. That way, there are only a few customers listed along the top of the page and the dates can span down multiple pages and still be easy to read.

Creating a Cross-Tab Object

The name “Cross-Tab Report” is a little misleading. It makes it sound like the whole report only shows the cross-tab grid and that no other data is printed. This isn’t true. A cross-tab report refers to a report that has a cross-tab object in one of its sections. The rest of the report is just a typical report. This object is similar to the other report objects on a report. It has properties that let you modify its fields and how it’s formatted.

There are two ways to add a cross-tab object to a report. On an existing report, right-click on the report and select Insert > Cross-Tab. This changes the mouse to the shape of a rectangle. Drag and drop the rectangle onto the report where you want the cross-tab object to be shown. An empty cross-tab object, shown in Figure 13-3, is displayed as a placeholder.

Figure 13-3. The Cross-Tab placeholder.

The second way to create a crosstab object is to choose the Cross-Tab Report Wizard from the Start Page. After selecting the data source and tables to pull data from, you are presented with the Cross-Tab Expert where you select the fields that are shown in the rows, columns, and summary fields. The Cross-Tab Expert is discussed next.