Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02

PERCENTAGE

The PERCENTAGE function computes the percent of total for each value in a numeric expression.

Return Value

DECIMAL

Syntax

PERCENTAGE(expression [BASEDON dimension-list])

Arguments

expression

The numeric expression for which percent figures are to be computed.

BASEDON dimension-list

An optional list of one or more of the dimensions of expression on which to base the percentage for each value. When you do not specify the dimensions, then PERCENTAGE bases the percentage on the total of all of the values of all of the dimensions of expression.

Notes

PERCENTAGE is affected by the NASKIP option. When NASKIP is set to `YES` (the default), then PERCENTAGE ignores `NA` values. When NASKIP is set to `NO`, then PERCENTAGE returns `NA` for any cell in expression whose value is `NA`.

Examples

Example 19-28 Calculating the Percentage

The following statements s limit the `month` and `district` dimensions, and report the data values, with subtotals, for the `units` variable.

```LIMIT month TO 'Jul96' TO 'Sep96'
LIMIT district TO 'Denver'
REPORT SUBTOTALS W 8 units

```

The preceding statement produces the following output.

```DISTRICT: DENVER
----------UNITS-----------
----------MONTH-----------
PRODUCT         Jul96    Aug96    Sep96
-------------- -------- -------- --------
Tents               608      517      441
Canoes              467      363      411
Racquets          3,006    2,836    2,838
Sportswear        2,395    2,039    2,138
Footwear          1,581    1,532    1,667
-------------- -------- -------- --------
TOTAL DENVER      8,057    7,287    7,495

```

This statement reports the percentage that each `month` value represents of the total `month` values for each of the `product` values that are in status. The total of the values that PERCENTAGE returns for each `product` value is `1`.

```REPORT SUBTOTALS W 8 DOWN month PERCENTAGE(units BASEDON month)

```

The preceding statement produces the following output.

```DISTRICT: DENVER
-----------PERCENTAGE(UNITS BASEDON MONTH)------------
-----------------------PRODUCT------------------------
MONTH      Tents      Canoes    Racquets  Sportswear  Footwear
-------- ---------- ---------- ---------- ---------- ----------
Jul96          0.39       0.38       0.35       0.36       0.33
Aug96          0.33       0.29       0.33       0.31       0.32
Sep96          0.28       0.33       0.33       0.33       0.35
-------- ---------- ---------- ---------- ---------- ----------
TOTAL          1.00       1.00       1.00       1.00       1.00
DENVER

```

This statement reports the percentage that each `product` value represents of the total `product` values for each of the `month` values that are in status.

```REPORT SUBTOTALS W 8 PERCENTAGE(units BASEDON product)

```

The preceding statement produces the following output.

```DISTRICT: DENVER
-PERCENTAGE(UNITS BASEDON-
---------PRODUCT)---------
----------MONTH-----------
PRODUCT         Jul96    Aug96    Sep96
-------------- -------- -------- --------
Tents              0.08     0.07     0.06
Canoes             0.06     0.05     0.05
Racquets           0.37     0.39     0.38
Sportswear         0.30     0.28     0.29
Footwear           0.20     0.21     0.22
-------------- -------- -------- --------
TOTAL DENVER       1.00     1.00     1.00

```

This statement reports the percentage based on all of the dimensions of the `units` variable. The total of all of the values that PERCENTAGE returns is `1`.

```REPORT SUBTOTALS W 8 PERCENTAGE(units)

```

The preceding statement produces the following output.

```DISTRICT: DENVER
----PERCENTAGE(UNITS)-----
----------MONTH-----------
PRODUCT         Jul96    Aug96    Sep96
-------------- -------- -------- --------
Tents              0.03     0.02     0.02
Canoes             0.02     0.02     0.02
Racquets           0.13     0.12     0.12
Sportswear         0.10     0.09     0.09
Footwear           0.07     0.07     0.07
-------------- -------- -------- --------
TOTAL DENVER       0.35     0.32     0.33

```

The total for all of the values for both the `product` and `month` dimensions is `1.00`.