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:
LIMIT dimension TO [BOTTOM|TOP] n BASEDON expression
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.
limit product to 'SPORTSWEAR' limit month to 'JUL96' limit district to top 2 basedon sales
Suppose that you issue the following REPORT command.
report down district sales
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.
LIMIT dimension TO [BOTTOM|TOP] percent PERCENTOF expression
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.
limit product to top 30 percentof total(sales, product)
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.
DISTRICT: BOSTON -------------SALES-------------- -------------MONTH-------------- PRODUCT JAN95 FEB95 MAR95 -------------- ---------- ---------- ---------- FOOTWEAR 91,406.82 86,827.32 100,199.46 CANOES 66,013.92 76,083.84 91,748.16
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|