|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
TALLY(dimension [[STATUS] related-dimensions])
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.
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".
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.
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
Example 23-8 Breaking Out TALLY Results
Here you use TALLY to determine how many products are produced by each division. The
division.product relation records the division to which each product belongs. The following is a report of
PRODUCT DIVISION.PRODUCT --------------------------------- Tents Camping Canoes Camping Racquets Sporting Sportswear Clothing Footwear Clothing
The following statement includes TALLY to present the number of products produced by each division.
REPORT HEADING 'Products' TALLY(product, division)
The statement produces this report.
DIVISION Products ------------------------- Camping 2 Sporting 1 Clothing 2