CrosstabSum

Description

Calculates the sum of the values returned by an expression in the values list of the crosstab. When the crosstab definition has more than one column, CrosstabSum can also calculate the sum of the expression's values for groups of column values.

For crosstabs only

You can use this function only in a crosstab report.

Syntax

CrosstabSum ( n {, column, groupvalue } )

Argument

Description

n

The number of the crosstab-values expression for which you want the sum of the returned values. The expression's datatype must be numeric.

column (optional)

The number of the crosstab column as it is listed in the Columns box of the Crosstab Definition dialog box for which you want intermediate calculations.

groupvalue (optional)

A string whose value controls the grouping for the calculation. Groupvalue is usually a value from another column in the crosstab. To specify the current column value in a dynamic crosstab, rather than a specific value, specify @ plus the column name as a quoted string.


Return value

Double. Returns the total of the values returned by expression n for all the column values or, optionally, for a subset of column values. To return a decimal datatype, use CrosstabSumDec.

Usage

This function is meaningful only for the sum of the values of the expression in a row in the crosstab. This means you can use it only in the detail band, not in a header, trailer, or summary band.

Null values are ignored and are not included in the sum.

For more information about restricting the calculation to groups of values when the crosstab definition has more than one column, see Usage for CrosstabAvg.

Reviewing the expressions

To review the expressions defined for the crosstab values, open the Crosstab Definition dialog box (select Design>Crosstab from the menubar).

Examples

These examples all use the crosstab-values expressions shown below:

Count(emp_id for crosstab),Sum(salary for crosstab)

This expression for a computed field in the crosstab returns the sum of the employee counts (the first expression):

CrosstabSum(1)

This expression for a computed field in the crosstab returns the sum of the salary totals (the second expression):

CrosstabSum(2)

The next two examples use a crosstab with two columns (year and quarter), a row (product), and the values expression Avg(sales for crosstab).

This expression for a computed field returns the sum of the quarterly average sales for each year:

CrosstabSum(1, 2, "@year")

This expression for a computed field returns the sum of all the average sales in the row:

CrosstabSum(1)

For an example illustrating how the painter automatically defines a crosstab by creating computed fields using the Crosstab functions, see CrosstabAvg.

See also

CrosstabAvg

CrosstabCount

CrosstabMax

CrosstabMin

CrosstabSumDec