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:

  1. Drag the quarter column from the Source Data box to the Columns box.

  2. Drag the product column from the Source Data box to the Rows box.

  3. Drag the units column from the Source Data box to the Values box and accept the expression sum(units for crosstab).

  4. Select the Rebuild columns at runtime check box

.

Shown is the Define Cross tab Rows, Columns, and Values dialog box.

In the Design view, the crosstab looks like this:

In the Design View, the Header 1 shows the Sum of Units and Quarter. Next is Header 2, shown as Product, @ quarter, and Grand Total. Next is Detail, which includes product, units, and cross tab sum ( 1 ). Next is Summary, shown as "Grand Total" and the expression sum ( units sum ( grand _ sum _ units for.

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 cross tab are grand totals for all products for each column that represents a quarter. Data is shown for three sample products, Cosmic, Galactic, and Stellar.