Percent
DataWindow expression function
Description
Gets the percentage that the current value represents of the total
of the values in the column.
Syntax
|
1 |
Percent ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } ) |
|
Argument |
Description |
|---|---|
|
column |
The column for which you want the value of each row |
|
FOR range (optional) |
The data to be included in the percentage. For most
For Crosstabs, specify CROSSTAB for
For Graph and OLE objects, specify one of
|
|
DISTINCT (optional) |
Causes Percent to consider only the distinct values |
|
expresn (optional) |
One or more expressions that you want to evaluate to |
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 DataWindow
object 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:
|
1 |
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:
|
1 |
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:
|
1 |
Percent(qty_ordered for graph) |
Assuming a DataWindow object 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:
|
1 |
Percent(order_amt for all DISTINCT order_nbr) |
See also