Tutorial 13-1. Creating a Cross-Tab Report

Let’s create a simple cross-tab report to see the basic steps. We’ll create an annual sales report for customers.

1. The first task is to plan the cross-tab object by determining which fields belong in the rows and columns. The report summarizes data by customer and sales year. Since there are a lot of products and fewer years, we’ll select the customer name as the row and the sales year as the column. Now let’s build the report.
2. Create a new report using the Cross-Tab Report Wizard.
3. For the data source, choose the Xtreme.mdb database and select the tables Customer and Orders. Click the Next button.
4. Check that two tables are linked by the Customer ID field. Click the Next button to confirm the table links.
5. Find the field Customer.Customer Name in the Available Fields list and select it. Then drag it to the Rows list.
6. Find the field Orders.Order Date in the Available Fields list and select it. Then drag it to the Columns list.
7. We don’t want the cross-tab to display the individual date for each order. That would be too much information and wouldn’t give us the benefit of being able to summarize the sales data. So let’s change the grouping function to group by year.
7. Click on the Orders.Order Date field in the Columns list and click the drop-down box to view the available summary functions. Select the function For Each Year to summarize annually.
8. For the summary field, click on the Orders.Order Amount field in the Available Fields list and drag it to the Summary Fields list. You can see that the default summary function is Sum, so we’ll leave it like that.
9. Click the Finish button to save your changes.
10. Save the report as Customer Annual Sales.rpt.
11. Preview the report and it should look similar to the following figure.