AGGCOUNT

The AGGCOUNT function retrieves the values of the Aggcount variable associated with the specified variable. An Aggcount variable is an INTEGER variable that Oracle OLAP automatically creates when it creates a variable defined using a DEFINE VARIABLE statement that includes a USING AGGOUNT phrase.

See also:

"Aggcount Variables"

Return Value

INTEGER

The values of the Aggcount variable that are the non-NA counts of the number of leaf nodes that contribute to the calculation of aggregate values when RELATION (for aggregation) statements that have an AVERAGE, HAVERAGE, WAVERAGE, or HWAVERAGE execute.

Syntax

AGGCOUNT(variable-name)

Parameters

variable-name: The name of the variable with which the Aggcount variable is associated.

Examples

Example 7-3 Reporting on an Aggcount Variable

Assume that within your analytic workspace you have objects with the following definitions.

DEFINE geog DIMENSION TEXT
DEFINE time DIMENSION TEXT
DEFINE product DIMENSION TEXT
DEFINE cc_geog_product COMPOSITE <geog product> COMPRESSED
DEFINE sales VARIABLE DECIMAL <time cc_geog_product <geog product>> WITH AGGCOUNT
DEFINE geog_parentrel RELATION geog <geog>
DEFINE product_parentrel RELATION product <product>
DEFINE time_parentrel RELATION time <time>
DEFINE aggsales AGGMAP
   AGGMAP
   RELATION time_parentrel OPERATOR AVERAGE ARGS COUNT YES
   RELATION geog_parentrel
   RELATION product_parentrel
   END

Notice that the definition for the sales variable includes a request for an Aggcount variable and that, within the aggsales aggmap, the RELATION statement for the time_parentrel relation incudes an AVERAGE operator.

Assume also that when only the base values of the sales variable are populated, sales has the following values for Radios and TVs.

REPORT sales

PRODUCT: Radio
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              122.93     176.69         NA
California         168.32     150.92         NA
Quebec                 NA         NA         NA
Ontario            187.46     164.46         NA
USA                    NA         NA         NA
Canada                 NA         NA         NA
World                  NA         NA         NA
 
PRODUCT: TV
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              184.75     135.40         NA
California         139.89     145.71         NA
Quebec                 NA         NA         NA
Ontario            123.63     113.32         NA
USA                    NA         NA         NA
Canada                 NA         NA         NA
World                  NA         NA         NA
 
PRODUCT: AV
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                  NA         NA         NA
California             NA         NA         NA
Quebec                 NA         NA         NA
Ontario                NA         NA         NA
USA                    NA         NA         NA
Canada                 NA         NA         NA
World                  NA         NA         NA

Since no aggregation has occurred, for AV, Oracle OLAP has not yet populated the Aggcount variable and the Aggcount variable for sales contains only NA values.

Now assume that you aggregate the sales variable by issuing the following statement.

AGGREGATE sales USING aggsales

A report of sales shows the following values.

REPORT sales

PRODUCT: Radio
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              122.93     176.69     149.81
California         168.32     150.92     159.62
Quebec                 NA         NA         NA
Ontario            187.46     164.46     175.96
USA                291.24     327.61     309.42
Canada             187.46     164.46     175.96
World              478.70     492.07     485.38
 
PRODUCT: TV
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              184.75     135.40     160.07
California         139.89     145.71     142.80
Quebec                 NA         NA         NA
Ontario            123.63     113.32     118.47
USA                324.64     281.11     302.87
Canada             123.63     113.32     118.47
World              448.27     394.42     421.35
 
PRODUCT: AV
               -------------SALES--------------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              307.67     312.08     309.88
California         308.21     296.63     302.42
Quebec                 NA         NA         NA
Ontario            311.09     277.78     294.43
USA                615.88     608.71     612.30
Canada             311.09     277.78     294.43
World              926.97     886.49     906.73

A report of the Aggcount variable shows that it is populated with the INTEGER values that are needed to aggregate the average sales.

REPORT AGGCOUNT (sales)

PRODUCT: Radio
               --------AGGCOUNT (SALES)--------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                  NA         NA          2
California             NA         NA          2
Quebec                 NA         NA         NA
Ontario                NA         NA          2
USA                     2          2          4
Canada                 NA         NA          2
World                   3          3          6
 
PRODUCT: TV
               --------AGGCOUNT (SALES)--------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                  NA         NA          2
California             NA         NA          2
Quebec                 NA         NA         NA
Ontario                NA         NA          2
USA                     2          2          4
Canada                 NA         NA          2
World                   3          3          6
 
PRODUCT: AV
               --------AGGCOUNT (SALES)--------
               --------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                   2          2          4
California              2          2          4
Quebec                 NA         NA         NA
Ontario                 2          2          4
USA                     4          4          8
Canada                  2          2          4
World                   6          6         12