TALLY

The TALLY function counts the number of values of a dimension that correspond to each value of one or more related dimensions.

Return Value

INTEGER

Syntax

TALLY(dimension [[STATUS] related-dimensions])

Arguments

dimension

A dimension whose values are to be counted. When you specify related-dimensions, TALLY counts the number of values of dimension that correspond to each value of a single related dimension, or to each combination of values of two or more related dimensions. When you do not specify related-dimensions, TALLY counts the number of values in the dimension. Only values in the current status of dimension are counted.

STATUS

May be specified when using one or more related dimensions for the results of the function. (See the description of the related-dimensions argument.) When you specify the STATUS keyword without specifying related-dimensions, Oracle OLAP produces an error.

When you use related dimensions, the STATUS keyword may be required in order for Oracle OLAP to successfully process the function, or the STATUS keyword may provide a performance enhancement. See "TALLY with STATUS".

related-dimensions

One or more related dimensions for the results. These must be related to dimension. Alternatively, you can specify the name of the relation instead of the dimension name. This enables you to choose which relation is used when there is more than one. When no related-dimensions are specified, TALLY returns the total number of values in the current status of dimension.

Notes

TALLY returns NA for any related-dimension position that has no dimension values corresponding to it.

When expression is dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, you can specify any other dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR as a related-dimension. Oracle OLAP uses the implicit relation between the dimensions. To control the mapping of one DAY, WEEK, MONTH, QUARTER, or YEAR dimension to another (for example, from weeks to months), you can define an explicit relation between the two dimensions and specify the name of the relation as the related-dimension argument to the TALLY function.

For each time period in the related dimension, Oracle OLAP tallies all the source time periods that end in the target time period. This method is used regardless of which dimension has the more aggregate time periods.

When you use TALLY with related dimensions, Oracle OLAP creates a temporary variable to use while processing the function. When you specify the STATUS keyword, then Oracle OLAP uses the current status instead of the default status of the related dimensions for calculating the size of this temporary variable.

When the size of the temporary variable for the results of the function would exceed 2 gigabytes, you must specify the STATUS keyword in order for Oracle OLAP to successfully execute the function. When dimension is limited to a few values that are physically fragmented, you can specify the STATUS keyword to improve the performance of the function.

When you use TALLY with the STATUS keyword in an expression that requires going outside of the status for results (for example, with the LEAD or LAG functions or with a qualified data reference), the results outside of the status will be returned as NA.

Examples

PRODUCT          DIVISION.PRODUCT
---------------------------------
Tents          Camping
Canoes         Camping
Racquets       Sporting
Sportswear     Clothing
Footwear       Clothing

REPORT HEADING 'Products' TALLY(product, division)

DIVISION        Products
-------------------------
Camping                 2
Sporting                1
Clothing                2