Multiple Rows, Columns, and Summarized Fields

Multiple Rows, Columns , and Summarized Fields

The Rows, Columns, and Summarized Field boxes in the Cross-Tab Expert dialog box obviously are more than one field tall. Yes, that means that you can add more than one database field or formula to any of these cross-tab sections. It s important to understand, though, how this will affect cross-tab behavior and appearance.

Probably the simplest place to start is with multiple summarized fields. If you add more than one field to the Summarized Field box, the cross-tab will simply calculate the additional summary or subtotal in each cell . You could, for example, use Product Name as the row, Region as the column, and both Quantity and Price as summarized fields. The cross-tab would simply include two numbers in each cell ”the total quantity and total price for that particular product and region.

You can even add the same field to the Summarized Field box more than once and choose a different summary function for each occurrence, using the Change Summary button. You could, for example, add both the Quantity and Price fields to the Summarized Field box again, and then choose a different summary operation for these summaries, such as Average. Alternatively, you might add them as Sum functions again, but this time choose the Percentage Of summary function for the second occurrence of the summed fields.

In the Edit Summary dialog box, when you select the Show As a Percentage Of option, you are given a choice between the Row total and the Column total as the target comparison number. In this example, the sums will be shown as a percentage of the column totals, so the resulting cross-tab shows four numbers in every cell: total quantity, total price, percentage of total quantity for the quarter, and percentage of total price for the quarter. It s interesting to note that the Row totals also display their percentage of the column totals, so the report is very effective as an analysis of each product by quarter:

Adding multiple fields to the Rows or Columns boxes causes a little different behavior that is important to understand. Whereas multiple summarized fields simply calculate and print in the same cell, multiple row or column fields don t just print over and over, side by side. When you add multiple fields to these boxes, you create a grouping hierarchy between the fields. Consider a cross-tab in which you add the Product Type field as the first row field, and the Product Name field as the second row field. Crystal Reports will create a group hierarchy by Product Type, and within that, by Product Name. The resulting cross-tab would look like this:

Notice that rows are created for both the inner and outer groups ”each product name has its own row within its product type, and each product type has its own subtotal row. And, at the end of the cross-tab is a grand total row for everything.

You can set up this multiple-field group hierarchy for either rows or columns. Also, you can include as many fields as you want in the Rows box and the Columns box (although it may not make much sense if you go beyond two or three levels).

Here s a portion of the resulting cross-tab:

Note  

What section of the report you place the cross-tab in is particularly important when you are using multiple row or column fields. If you create a cross-tab that s based on Country, and then Region, you ll see different behavior depending on where you put the cross-tab. If you put it in the report header or footer, you ll have rows or columns for each country, and all the regions within those countries . However, if you have already grouped your report by country, and you place the cross-tab in a country group header or group footer, you ll then have one cross-tab for every country. However, that cross-tab will have only one country row or column in it, followed by any regions within that country. If you find cross-tabs at group levels with only one high-level row or column, there s not a great deal of benefit to using multiple row or column fields in that cross-tab.

If you plan to use multiple row or column fields, choosing fields that have a logical, hierarchical relationship with each other is crucial. You may think of this relationship as being one-to-many. The Product Type/Product Name relationship is a good example ”every one product type has many product names . Country/Region is another good example ”every one country has many regions.

Placing two fields in the Rows or Columns boxes that don t have this relationship will cause an odd-looking cross-tab. For example, if you add Customer Name and Address fields to the same row or column box, you ll simply see the customer name row or column, immediately followed by a single address row or column. The summaries in each will be exactly the same, because there s no logical one-to-many relationship between the fields. (The exception would be if a single customer had more than one office location ”then this would be a valid multiple-field cross-tab example.)

Note  

You may yearn for a cross-tab that allows multiple row or column fields that don t act as groups. You might, for example, want to see Actual $, Budget $, Variance $, and Variance % all as separate column fields that just calculate and print side by side. Sorry, but any time you add multiple fields to the Rows box or Columns box, Crystal Reports displays the fields in a grouping hierarchy from top to bottom.

Reordering Fields in the Rows, Columns, or Summarized Field Boxes

The multiple fields you add to the Rows box or Columns box not only have to have a logical relationship, they also need to appear in the box in the right order. Using the previous Country/Region example, Country must be the first field in the box, followed by Region. If they re added the other way around, then each region will appear first, followed by a single row or column containing numbers from the country the region is in.

If you happen to add fields to the Rows, Columns, or Summarized Field boxes in the wrong order, you can reorder them by one of two methods : either click one of the fields and then click the up or down arrow for that box to move it up or down, or click and drag a field in any of the boxes and drop it in a different location in the box.