|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Selecting Data, 5 of 13
You can set the dimension values that are currently in status to the top or bottom performers based on a criterion represented as an expression. The simplified syntax for using the
LIMIT command in this way is shown below:
You can also set the dimension values that are currently in status to the top or bottom performers, by percentage, based on a criterion represented as an expression. The simplified syntax for using the
LIMIT command in this way is shown below.
This construction sorts values based on their contribution, by percentage, to an expression and then places the identified values in status.
It can happen that the last item in status, based on a
PERCENTOF criterion, is one of a number of dimension values having the same associated criterion value. In this case,
LIMIT includes all dimension values with that criterion value in the resulting status, even when that causes the total of the criterion value to far exceed the specified percentage.
Suppose the status list is sorted in descending order according to the values of
sales, and only the top two performers are kept in status. Here the
BASEDON keywords are used to limit the status of a dimension, using the values of a variable as a criterion.
Suppose that you issue the following
The following report is produced, which shows the results of the
PRODUCT: SPORTSWEAR --SALES--- --MONTH--- DISTRICT JUL96 -------------- ---------- DALLAS 220,416.81 ATLANTA 211,666.14
Suppose you want to sort products in descending order by the contribution of each product to
TOTAL(sales) and then add values to the status list, starting from the top, until the cumulative total of
product reaches or exceeds 30 percent of all sales. To limit the dimension in this way, you can use the following command.
The following commands produce a report for January through March 2002 of products in the Boston district that reached or exceeded 30 percent of all sales.
LIMIT month TO 'JAN02' 'FEB02' 'MAR02' LIMIT district TO 'BOSTON' LIMIT product TO TOP 30 PERCENTOF TOTAL(sales, product) REPORT sales
This output of the report is shown below.