Specifying the information – PB Docs 125

Dynamic crosstab example

The process is illustrated using the following dynamic crosstab.
The columns in the database are Rep, Quarter, Product, and Units.
The crosstab shows the number of printers sold by Quarter:

Specifying the columns

You use the Columns box to specify one or more of the retrieved
columns to provide the columns in the crosstab. When users run
the crosstab, there is one column in the crosstab for each unique
value of the database column(s) you specify here.

Using the printer example, to create a crosstab where the
quarters form the columns, specify Quarter as the Columns value.
Because there are four values in the table for Quarter (Q1, Q2,
Q3, and Q4), there are four columns in the crosstab.

Specifying the rows

You use the Rows box to specify one or more of the retrieved
columns to provide the rows in the crosstab. When users run
the crosstab, there is one row in the crosstab for each unique value
of the database column(s) you specify here.

Using the printer example, to create a crosstab where the
printers form the rows, specify Product as the Rows value. Because
there are three products (Cosmic, Galactic, and Stellar), at runtime
there are three rows in the crosstab.

Columns that use code tables

If you specify columns in the database that use code tables,
where data is stored with a data value but displayed with more meaningful
display values, the crosstab uses the column’s display
values, not the data values. For more information about code tables,
see Chapter 22, “Displaying and Validating
Data .”

Specifying the values

Each cell in a crosstab holds a value. You specify that value
in the Values box. Typically you specify an aggregate function,
such as Sum or Avg, to summarize the
data. At runtime, each cell has a calculated value based on the
function you provide here and the column and row values for the
particular cell.

Using the printer example, you would drag the Units column
into the Values box and accept the expression sum(units
for crosstab).

Using expressions

Instead of simply specifying database columns, you can use
any valid DataWindow expression to define the columns, rows, and values used
in the crosstab. You can use any non-object-level DataWindow expression function in
the expression.

For example, say a table contains a date column
named SaleDate, and you want a column in the
crosstab for each month. You could enter the following expression
for the Columns definition:

The Month function returns the integer
value (1–12) for the specified month. Using this expression,
you get columns labeled 1 through 12 in the crosstab. Each database
row for January sales is evaluated in the column under 1, each database
row for February sales is evaluated in the column under 2, and so
on.