CumulativePercent

Description

Calculates the total value of the rows up to and including the current row in the specified column as a percentage of the total value of the column (a running percentage).

Syntax

CumulativePercent ( column { FOR range } )

Argument

Description

column

The column for which you want the cumulative value of the rows up to and including the current row as a percentage of the total value of the column for range. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.

FOR range  (optional)

The data that will be included in the cumulative percentage. For most presentation styles, values for range are:

  • ALL -- (Default) The cumulative percentage of all rows in column.

  • GROUP n -- The cumulative percentage of rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.

  • PAGE -- The cumulative percentage of the rows in column on a page.

For Crosstabs, specify CROSSTAB for range:

  • CROSSTAB -- (Crosstabs only) The cumulative percentage of all rows in column in the crosstab.

For Graph and OLE objects, specify one of the following:

  • GRAPH -- (Graphs only) The cumulative percentage of values in column in the range specified for the Rows option.

  • OBJECT -- (OLE objects only) The cumulative percentage of values in column in the range specified for the Rows option.


Return value

Long. Returns the cumulative percentage value.

Usage

If you specify range, CumulativePercent restarts the accumulation at the start of the range.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range.

Settings for Rows include the following:

  • For the Graph presentation style, Rows is always All.

  • For Graph controls, Rows can be All, Page, or Group.

  • For OLE controls, Rows can be All, Current Row, Page, or Group. The available choices depend on the layer the control occupies.

In calculating the percentage, null values are ignored.

Not in validation rules or filter expressions

You cannot use this or other aggregate functions in validation rules or filter expressions.

Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a report always retrieves all rows.

Examples

This expression returns the running percentage for the values that are not null in the column named salary:

CumulativePercent(salary)

This expression returns the running percentage for the column named salary for the values in group 1 that are not null:

CumulativePercent(salary for group 1)

This expression entered in the Value box on the Data property page for a graph returns the running percentage for the salary column for the values in the graph that are not null:

CumulativePercent(salary for graph)

This expression in a crosstab computed field returns the running percentage for the salary column for the values in the crosstab that are not null:

CumulativePercent(salary for crosstab)

See also

Percent

CumulativeSum