|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-01
Limiting an Application's View of the Data, 5 of 11
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:
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 TOP and 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 REPORT command.
The following report is produced, which shows the results of the LIMIT commands.
PRODUCT: SPORTSWEAR --SALES--- --MONTH--- DISTRICT JUL96 -------------- ---------- DALLAS 220,416.81 ATLANTA 211,666.14
You can 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.
Note: Do not use a criterion expression that changes its own value.
Suppose you want to sort products in descending order by each product's contribution to TOTAL(SALES) and then add values to the status list, starting from the top, until the cumulative total of SALES by 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 1995 of products in the Boston district that reached or exceeded 30 percent of all sales.
limit month to 'JAN95' 'FEB95' 'MAR95' limit district to 'BOSTON' limit product to top 30 percentof total(sales, product) report sales
This output of the report is shown below.