Viewing the crosstab

After you have specified the data for the crosstab’s columns, rows, and values, PowerBuilder displays the crosstab definition in the Design view.

For example, to create the dynamic crosstab shown as the “Dynamic crosstab example”, you would:

Drag the quarter column from the Source Data box to the Columns box.
Drag the product column from the Source Data box to the Rows box.
Drag the units column from the Source Data box to the Values box and accept the expression sum(units for crosstab).
Select the Rebuild Columns at Runtime check box

The sample is titled Define Crosstab Rows, Columns, Values. On the left are icons representing a general variety of crosstabs. On the right are four boxes listing Source Data, Columns, Rows, and Values. Above them is the prompt "Define your rows, columns, and values for your data window:" The Source data box includes rep, quarter, product, and units. Under Columns is the quarter column. Under Rows is the product row. Under Values is the expression sum( units for cross tab ). At the bottom the check box labeled Rebuild columns at runtime is shown as selected.

In the Design view, the crosstab looks like this:

The sample in the Design view shows Header 1 at the top as Sum of Units and Quarter. Next is Header 2, shown as product, @ quarter, and Grand Total. Next is Detail, which includes product, units, and crosstabsum ( 1 ). Next is Summary, shown as "Grand Total" and the expression sum ( unitssum ( grand _ sum _ units for. Last is footer, with no footer text displayed.

Notice that in the Design view, PowerBuilder shows the quarter entries using the symbolic notation @quarter (with dynamic crosstabs, the actual data values are not known at definition time). @quarter is resolved into the actual data values (in this case, Q1, Q2, Q3, and Q4) when the crosstab runs.

The crosstab is generated with summary statistics: the rows and columns are totaled for you.

At this point, the crosstab looks like this in the Preview view with data retrieved:

The sample has the headings Sum of Units and Quarter. Below them is a column heading for Product, a column heading for each quarter, and a grand total for each product row. At the bottom of the crosstab are grand totals for all products for each column that represents a quarter. Data is shown for three sample products, Cosmic, Galactic, and Stellar.

Because quarter was selected as the Columns definition, there is one column in the crosstab for each unique quarter (Q1, Q2, Q3, and Q4).
Because product was selected as the Rows definition, there is one row in the crosstab for each unique product (Cosmic, Galactic, and Stellar).
Because sum(units for crosstab) was selected as the Values definition, each cell contains the total unit sales for the corresponding quarter (the Columns definition) and product (the Rows definition).
PowerBuilder displays the grand totals for each column and row in the crosstab.