The Crosstab Format

There are many different formats a spreadsheet may take. The Crosstab format is one of the most popular. Crosstab stands for Cross tabulation, a process by which totals and other calculations are performed based on common values found in a set of data.

In Microsoft Excel™ the term "Pivot Table" is used for a Crosstab.

For example, let's assume you have a set of data that describes the sales for a company. Each sale is represented by a row of data. Each row of data contains a customer name, company type, Geographical Area, sale date or period, and sale amount. Now, assume you want to know the total sales for each month by area.

Here is one way you could present the data:

Area: Sydney

Year: 2000

Total Sales: $2577

State: Auckland

Year: 2001

Total Sales: $3548

This format is OK, but it makes a state-to- state comparison difficult. Another format is as follows:

	Region
Areas	Auckland	Sydney	Total
2000	$7816	$5327	$13,143
2001	$10,500	$9750	$20,250

This format is easier to read and more compact there is more information in less space. It is easy to make state-to-state and year-to-year comparisons. This format is a Crosstab. The Year and Region values are called "dimensions" because they orient the data in rows and columns. The values in the cells are the calculations created when the sales data is summarized and are sometimes referred to as measures.

The simple Crosstab we've outlined here can be taken a couple of steps further to create a very informative report. What if we wanted to know the sales by Customer type within the region, as well as the total number of distinct sales per Customer type. We can present this information by adding another dimension to the columns (i.e. Customer Type) and another calculation to the values (i.e. count of sales). The resulting Crosstab would look like the one below:

		Region
		Auckland					Sydney					Total
Year	Data	Dairies	Super- markets	Mini-marts	Take- aways	Region Total	Dairies	Super- markets	Mini-marts	Take- aways	Region Total
2000	Count Sales Sum of Sales	43   $2076	27   $1764	19   $1524	103   $1672	192   $7816	23   $1001	24   $1502	12   $1423	83   $1401	142   $5327	334   $13,143
2001	Count Sales Sum of Sales	41   $3084	56   $4500	14   $1029	164   $2097	275   $10,500	30   $2124	36   $4500	10   $1029	102   $2097	178   $9750	453   $20,250

Notice the new subtotal columns after each Region. This Crosstab shows all of the information of the initial Crosstab, plus more detailed information by Customer Type. You can see that Crosstabs can express a lot of information in a very small amount of space. Clarity has a built-in facility for creating Crosstabs. The Crosstab component is designed to handle the most common Crosstab requirements with minimal effort on your part. For example, let's assume you wanted to create the Crosstab we just described. You would take the following steps:

1. Select the sales data from the database

2. Create a Crosstab component in the report layout.

3. Select the Region, Customer type, and Period as dimensions.

4. Select count and sum as values.

5. Preview the report. It would look identical to the one above.

Note: Be aware that while it is tempting to create Crosstabs to browse and analyse your transaction data, you should be careful to filter out unnecessary transactions. On a large database, calculating a Crosstab could take a long time, and it could affect performance if you are running it on the database server. Do some small test runs first to analyse the performance of the query.