A cross-tabulation (or "cross-tab") report is one that summarizes information in your database. It's laid out in a grid, with rows representing one "fact" (for example, country, date, or salesperson), columns representing another, and the intersection of rows and columns containing the summarized information. It's similar to a PivotTable in Microsoft Excel. In fact, you can output a cross-tab report to an Excel PivotTable if you want to do additional analysis of the data in Excel.

For example, you may want to see a breakdown of your sales by country and product. In this case, the rows would be the different countries your customers are in, the columns would be the product names, and the cell at the intersection of a specific row and column would be the total sales for that product in that country.

To create a cross-tab report, select at least three fields you want to appear in the report, and then select where each field goes. A row field is one that has each different value appear in a row in the report. A column field is one that has each different value appear in a column in the report. A data field is one that's summarized and placed in the cells at the intersection of rows and columns.

Here are some notes about how cross-tabs work:

• You can have more than one row field. For example, you may want sales by country, and within country, by state. Choose both the country and state fields, and select country as the first row field and state as the second. The report shows each state in each country, with subtotals for each country.

• You can specify how the data field is summarized. For numeric fields, the default is to sum the values, but you can use another type of summarization, such as average. For all other types of fields, you can only have it count the number of values.

• If you use a date field as a row or column field, you can specify how the date is displayed. For example, you may want to show the month or year rather than the individual dates.

• You can have more than one data field. For example, if you want to show not only the total sales amount but also the count of how many sales there were, select the amount field and some other field, and make them both data fields. The amount field is summed and the other field is counted.

• You can have a page field for the report. The page field is like a row field but appears in the page header rather than the rows and there's a page break when the page field value changes. Also, a report with a page field optionally has a totals page that summarizes the values for all of the values of the page field.

• The report is automatically sorted in ascending order on the row fields, but you can instead sort in ascending or descending order on one of the data fields.

• Since there may be a lot of rows and/or columns, depending on the fields you choose, Stonefield Query automatically horizontally paginates the report for you. This is similar to how spreadsheets like Microsoft Excel print multi-page reports. Stonefield Query prints a page for the first set of rows and columns, then the next page has the next set of rows but the same columns, and so on until all of the rows have been printed. Then it prints the first set of rows and the next set of columns, and continues in this manner until all columns and all rows have been printed.

  If you use the older cross-tab engine, report are output horizontally first, then vertically.

• There are totals for each row and each column, and a grand total at the end of the report. You can optionally turn off these totals.

To create a cross-tab report, follow the steps in the Creating a Report topic. The Cross-Tabulation Wizard shown below appears.

You can resize the Cross-Tabulation Wizard window by clicking the lower right corner of the window and dragging until the window is the desired size. Stonefield Query remembers the size and position of the Cross-Tabulation Wizard and restores it the next time you run it.