Description
Gets the percentage that the current value represents of the total of the values in the column.
Syntax
Percent ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )
|
Argument |
Description |
|---|---|
|
column |
The column for which you want the value of each row expressed as a percentage of the total of the values of the column. 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 to be included in the percentage. For most presentation styles, values for range are:
For Crosstabs, specify CROSSTAB for range:
For Graph and OLE objects, specify one of the following:
|
|
DISTINCT (optional) |
Causes Percent to consider only the distinct values in column when determining the percentage. For a value of column, the first row found with the value is used and other rows that have the same value are ignored. |
|
expresn (optional) |
One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression. |
Return value
A numeric datatype (decimal, double, integer, long, or real). Returns the percentage the current row of column represents of the total value of the column.
Usage
Usually you use Percent in a column to display the percentage for each row. You can also use Percent in a header or trailer for a group. In the header, Percent displays the percentage for the first value in the group, and in the trailer, for the last value in the group.
If you specify range, Percent returns the percentage that the current row of column represents relative to the total value of range. For example, if column 5 is salary, Percent(#5 for group 1) is equivalent to salary/(Sum(Salary for group 1)).
If you specify DISTINCT, Percent returns the percent that a distinct value in column represents of the total value of column. If you specify expresn, Percent returns the percent that the value in column represents of the total for column in a row in which the value of expresn is distinct.
Formatting the percent value
The percentage is displayed as a decimal value unless you specify a format for the result. A display format can be part of the computed field's definition.
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 or OLE 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.
Null values are ignored and are not considered in the calculation.
Not in validation rules, filter expressions, or crosstabs
You cannot use Percent or other aggregate functions in validation rules or filter expressions. Percent does not work for crosstabs; specifying "for crosstab" as a range is not available for Percent.
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 value of each row in the column named salary as a percentage of the total of salary:
Percent(salary)
This expression returns the value of each row in the column named cost as a percentage of the total of cost in group 2:
Percent(cost for group 2)
This expression entered in the Value box on the Data tab page in the Graph Object property sheet returns the value of each row in the qty_ordered as a percentage of the total for the column in the graph:
Percent(qty_ordered for graph)
Assuming a report displays the order number, amount, and line items for each order, this computed field returns the order amount as a percentage of the total order amount for the distinct order numbers:
Percent(order_amt for all DISTINCT order_nbr)
See also
