Tutorial 13-2 Adding Groups to a Cross-Tab

In this tutorial, we are going to explore how groups work by modifying the report from the previous tutorial. We are going to add grouping to both the rows and columns. The customers are going to be grouped by country. The column is going to be modified so that the year is the outer-most group and the quarter is the inner-most group. A new summary function will be added so that the average sales amount is shown below the total sales amount.

1. Open the “Customer Annual Sales.rpt” report that was created in Tutorial 13-1.
2. Right-click on the top-left corner of the cross-tab object and select Cross-Tab Expert.
3. The first modification we want to make is to group the customers by their country. Drag the Customer.Country field into the Rows list. This puts the field at the bottom of the list.
4. We want the country to be the outermost group, but it was placed at the bottom of the list since we just added it. Click on the Country field and click the up arrow to move it to the top of the list.
5. For the column, we want to add the Order Date field and group it by sales quarter. Drag the Order.Order Date field into the Columns list.
6. Click on the new Order Date field and click the Group Options button. This opens the Cross-Tab Group Options dialog box.
7. On the third drop-down box, select “For Each Quarter” as the way to print the column.
8. Click the OK button to close the group options dialog box.
9. For the summary section, we want to show the average of the order amounts. Drag the field Orders.Order Amount to the Summarized Fields list.
10. Click the Change Summary button to open the Edit Summary dialog box.
11. Change the summary function to Average. Click the OK button to close the summary dialog box.
12. The Cross-Tab Expert dialog box should look like the following figure:

12. We are finished adding fields to the cross-tab object, so click the OK button to save your changes.
13. Save the report as Customer Annual Sales Grouped.rpt.
14. Preview the report and it should look similar to the following figure.

Notice how the cross-tab object formats the groups. The country is listed to the far left and the customers that are in each country are located to the right of it. Along the top of the report is the year and just below that are the sales quarters. In each cell is the total order amount and the average order amount. Unfortunately, on this page of the report each customer only has one sale. Thus, the order total is the same amount as the average. However, you can see in the top Total row that the average calculation is different because the top row calculates the average order for the entire report.

There are a few things I don’t like about this report. The first is that in the third column the phrase, “Order Amount” is repeated on each row. You can change that by right-clicking on the text and selecting “Edit Text”. I also prefer having the summary fields listed side-by-side. To change this, go into the Cross-Tab Expert and on the Customize Style tab choose the Horizontal option for the summary fields. After making these changes, my report is shown in Figure 13-5.

Figure 13-5. Modified grouping report.

In the next section, we’ll see the different cross-tab properties and options that let us customize the look of cross-tab objects.