About crosstabs
Contents
Cross tabulation is a useful technique for analyzing data. By
presenting data in a spreadsheet-like grid, a crosstab lets users view
summary data instead of a long series of rows and columns. For example,
in a sales application you might want to summarize the quarterly unit
sales of each product.
In PowerBuilder, you create crosstabs by using the Crosstab
presentation style. When data is retrieved into the DataWindow object,
the crosstab processes all the data and presents the summary information
you have defined for it.
An example
Crosstabs are easiest to understand through an example. Consider
the Printer table in the PB Demo DB. It records quarterly unit sales of
printers made by sales representatives in one year. (This is the same
data used to illustrate graphs in Working with
Graphs)
|
Rep |
Quarter |
Product |
Units |
|---|---|---|---|
|
Simpson |
Q1 |
Stellar |
12 |
|
Jones |
Q1 |
Stellar |
18 |
|
Perez |
Q1 |
Stellar |
15 |
|
Simpson |
Q1 |
Cosmic |
33 |
|
Jones |
Q1 |
Cosmic |
5 |
|
Perez |
Q1 |
Cosmic |
26 |
|
Simpson |
Q1 |
Galactic |
6 |
|
Jones |
Q1 |
Galactic |
2 |
|
Perez |
Q1 |
Galactic |
1 |
|
. |
. |
. |
. |
|
. |
. |
. |
. |
|
. |
. |
. |
. |
|
Simpson |
Q4 |
Stellar |
30 |
|
Jones |
Q4 |
Stellar |
24 |
|
Perez |
Q4 |
Stellar |
36 |
|
Simpson |
Q4 |
Cosmic |
60 |
|
Jones |
Q4 |
Cosmic |
52 |
|
Perez |
Q4 |
Cosmic |
48 |
|
Simpson |
Q4 |
Galactic |
3 |
|
Jones |
Q4 |
Galactic |
3 |
|
Perez |
Q4 |
Galactic |
6 |
This information can be summarized in a crosstab. Here is a
crosstab that shows unit sales by printer for each quarter:
The first-quarter sales of Cosmic printers displays in the first
data cell. (As you can see from the data in the Printer table shown
before the crosstab, in Q1 Simpson sold 33 units, Jones sold 5 units,
and Perez sold 26 units—totaling 64 units.) PowerBuilder calculates each
of the other data cells the same way.
To create this crosstab, you only have to tell PowerBuilder which
database columns contain the raw data for the crosstab, and PowerBuilder
does all the data summarization automatically.
What crosstabs do
Crosstabs perform two-dimensional analysis:
-
The first dimension is displayed as columns across the
crosstab.In the preceding crosstab, the first dimension is the quarter,
whose values are in the Quarter column in the database table. -
The second dimension is displayed as rows down the
crosstab.In the preceding crosstab, the second dimension is the type of
printer, whose values are in the Product column in the database
table.Each cell in a crosstab is the intersection of a column (the
first dimension) and a row (the second dimension). The numbers that
appear in the cells are calculations based on both dimensions. In
the preceding crosstab, it is the sum of unit sales for the quarter
in the corresponding column and printer in the corresponding
row.Crosstabs also include summary statistics. The preceding
crosstab totals the sales for each quarter in the last row and the
total sales for each printer in the last column.
How crosstabs are implemented in
PowerBuilder
Crosstabs in PowerBuilder are implemented as grid DataWindow
objects. Because crosstabs are grid DataWindow objects, users can resize
and reorder columns at runtime (if you let them).
Import methods return empty result
A crosstab report takes the original result set that was
retrieved from the database, sorts it, summarizes it, and generates a
new summary result set to fit the design of the crosstab. The
ImportFile, ImportClipboard, and ImportString methods can handle only
the original result set, and they return an empty result when used
with a crosstab report.