When you generate a crosstab, the columns and rows are automatically totaled for you. You can include other statistical summaries in crosstabs as well. To do that, you place computed fields in the workspace.
To define a column summary
-
Enlarge the summary band to make room for the summaries.
-
Select Insert>Control > Computed Field from the menu bar.
-
Click the cell in the summary band where you want the summary to display.
The Modify Expression dialog box displays.
-
Define the computed field.
For example, if you want the average value for a column, specify avg(units for all), where units is the column providing the values in the crosstab.
For example, this is a crosstab that has been enhanced to show averages and maximum values for each column. This is the Design view:
This is the crosstab at runtime:
To define a row summary
-
Select Insert>Control > Computed Field from the menu bar.
-
Click the empty cell to the right of the last column in the detail band.
The Modify Expression dialog box displays.
-
Define the computed field. You should use one of the crosstab functions, described next.
There are nine special functions you can use only in crosstabs: CrosstabAvg, CrosstabAvgDec, CrosstabCount, CrosstabMax, CrosstabMaxDec, CrosstabMin, CrosstabMinDec, CrosstabSum, and CrosstabSumDec.
These functions are listed in the Functions box when you define a computed field in a crosstab:
Each of these functions returns the corresponding statistic about a row in the crosstab (average, count, maximum value, minimum value, or sum). You place computed fields using these functions in the detail band in the Design view. Use the functions with the Dec suffix when you want to return a decimal datatype.
By default, PowerBuilder places CrosstabSum and CrosstabSumDec in the detail band, which returns the total for the corresponding row.
How to specify the functions
Each of these functions takes one numeric argument, which refers to the expression defined for Values in the Crosstab Definition dialog box. The first expression for Values is numbered 1, the second is numbered 2, and so on.
Generally, crosstabs have only one expression for Values, so the argument for the crosstab functions is 1. So, for example, if you defined sum(units for crosstab) as your Values expression, PowerBuilder places CrosstabSum in the detail band.
If you want to cross-tabulate both total unit sales and a projection of future sales, assuming a 20 percent increase in sales (that is, sales that are 1.2 times the actual sales), you define two expressions for Values:
sum(units for crosstab) sum(units * 1.2 for crosstab)
Here CrosstabSum returns the total of sum(units for crosstab) for the corresponding row. CrosstabSum returns the total for sum(units * 1.2 for crosstab).
For more information
For complete information about defining computed fields, see Enhancing DataWindow Objects.
For more about the crosstab functions, see the section called “Alphabetical list of DataWindow expression functions” in DataWindow Reference.
