CrosstabAvg

Description

Calculates the average of the values returned by an expression in the values list of the crosstab. When the crosstab definition has more than one column, CrosstabAvg can also calculate averages of the expression’s values for groups of column values.

NoteFor crosstabs only You can use this function only in a crosstab report.

Syntax

CrosstabAvg ( n {, column, groupvalue } )

Argument

Description

n

The number of the crosstab-values expression for which you want the average of the returned values. The crosstab expression 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.

Returns

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

Usage

This function is meaningful only for the average 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 average.


How functions in a crosstab are used

When a crosstab is generated from your definition, the appropriate computed fields are automatically created using the Crosstab functions. To understand the functions, consider a crosstab with two columns (year and quarter), a row (product), and the values expression Avg(amount for crosstab).

The Crosstab Definition dialog box looks like this.

The sample Crosstab Definition dialog box shows Source Data at top left with the selections year, code, quarter, and amount. Below is the Rows box, which displays the word code. At top right are the instructions: 1 Click and Drag items, 2 Drop them over Columns, Rows, or Values, and 3 double click to edit them. Below the instructions is the Columns box, where the selections year and quarter are displayed. At bottom right is the Values box containing the expression a v g ( amount for crosstab ). At bottom is a selected check box labeled Rebuild columns at runtime.

When you define the crosstab described above, the painter automatically creates the appropriate computed fields. A computed field named avg_amount returns the average of the quarterly figures for each year. Its expression is:

CrosstabAvg(1, 2, "@year")

A second computed field named grand_avg_amount computes the average of all the amounts in the row. Its expression is:

CrosstabAvg(1)

Other computed fields in the summary band use the Avg function to display the average of the values in the amount column, the yearly averages, and the final average.

The crosstab in the Design view looks like this.

The sample Design view of the crosstab displays as four columns across each band. In the Header 1 band are a blank, Year, Quarter, and another blank. Header 2 displays a blank, @ year, @ year A v g, and a blank. Header 3 displays Code, @ quarter, a blank, and Grand A v g. The Detail band displays code, amount, and the expressions Cross tab A v g ( 1, 2, " @ year " ) and Cross tab Avg ( 1 ). The summary band displays " Grand A v g ", and the expressions a v g ( amount for all ), a v g ( a v g _ amount for all ), and a v g ( grand _ a v g _ amount for all ).

Each row in the crosstab (after adjusting the column widths) has cells for the amounts in the quarters, a repeating cell for the yearly average, and a grand average. The crosstab also displays averages of the amounts for all the financial codes in the quarters in the summary band at the bottom.

The sample crosstab displays the headers Year and Quarter. At far left is a column header Code that displays financial codes such as e 1 and e 2 for each row. Below the year header is the number 1997. Under that are columns for each quarter. To the right of these is a column for the year’s average. Each row in the crosstab has cells for the amounts in the quarters and a repeating cell for the yearly average. A grand average displays at the bottom for all the financial codes combined. To the right of the data for 1997 is the same layout for 1998.The sample crosstab displays data for the year 1999.  Below the year are columns with values for each quarter. To the far right is the 1999 average for each row and the grand average for all years in the full crosstab. A grand average for all rows displays at the bottom of each column.

What the function arguments mean

When the crosstab definition has more than one column, you can specify column qualifiers for any of the Crosstab functions, so that the crosstab displays calculations for groups of column values. As illustrated previously, when year and quarter are the columns in the crosstab, the expression for the computed field is:

CrosstabAvg(1, 2, "@year")

The value 2 refers to the quarter column (the second column in the Crosstab Definition dialog) and “@year” specifies grouping values from the year column (meaning the function will average values for the quarters within each year). The value 1 refers to the crosstab-values expression that will be averaged. In the resulting crosstab, the computed field repeats in each row after the cells for the quarters within each year.


Tips for defining crosstabs

When you define a crosstab with more than one column, the order of the columns in the Columns box of the Crosstab Definition dialog box governs the way the columns are grouped. To end up with the most effective expressions, make the column that contains the grouping values (for example, year or department) the first column in the Columns box and the column that contains the values to be grouped (for example, quarter or employee) second.

To display calculations for groups of rows, define groups as you would for other report presentation styles and define computed fields in the group header or footer using noncrosstab aggregation functions, such as Avg, Sum, or Max.

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

Examples

Example 1

The first two examples use the crosstab expressions shown below:

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

Example 2

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

CrosstabAvg(1)

Example 3

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

CrosstabAvg(2)

Example 4

Consider a crosstab that has two columns (region and city) and the values expression Avg(sales for crosstab). This expression for a computed field in the detail band computes the average sales over all the cities in a region:

CrosstabAvg(1, 2, "@region")

Example 5

This expression for another computed field in the same crosstab computes the grand average over all the cities:

CrosstabAvg(1)

See also