Crosstabs in Alpha Five
by Robert Tishkevich
Posted at www.workings.com January 21, 2001

There are certain features in Alpha Five that are very useful, but for one reason or another, we usually fail to take advantage of the built in tools. One of those is the ability to create CROSSTABS.  What is a crosstab and when should you utilize this feature?

When you think of a crosstab, you are probably thinking of a spreadsheet because the two are very similar. The creation of a crosstab starts with data from a single table and the subsequent creation of a brand new results table where the fieldnames correspond to field values in the Master Table. In essence it gives the user an option for creating a form of group summaries. 

For example, let's say we want to tabulate sales for a company by Customer and Month. The data in the results table consists of a field or expression that is summarized for each column and row combination. It is very similar to Summary Tables, you can select the sum, count, or average values.

Just make sure you know the difference between columns (vertical) and rows (horizontal) and that will help you immensely.  In our example the calculated expression (CMONTH(Date_Sale) field is used for the Column Grouping while the Customer field is used for the ROW grouping. 

That means every row will be a new customer and as we proceed from left to right in our row, the user will see the total sales for that customer by month. Remember, you must assign a name to the results table via the Results Table Name box.

In our example, the SALES field was chosen for each customer by placing it in the Field/Expression’s dropdown list box. Then you must select the type of calculation you want to perform, in this case we want the TOTAL sales for each customer and the Total Sales per month

OK, so now we’ve talked about it, let's see what it looks like?  Here is our Crosstab Result table:

TOTAL SALES for BLUE LIGHT CUSTOMERS by MONTH

So what did we learn from our Crosstab?  We learned that Toby isn't exactly a big spender, but all customers are critical to the success of our company. Maybe we can provide extra services that Toby needs or wants? More importantly, we learned that January/February are very slow months while sales picked up dramatically in March, April, and May? Did sales pick up as a result of milder weather? The Crosstab tool provides the type of information that can be very beneficial to a company.