Returns rows with the lowest values of a specified data column.
Syntax
<BOTTOM ([rowgroupDimension,] rows, column)
| Parameter | Description |
|---|---|
rowgroupDimension | Optional row grouping dimension that determines the rows to sort as a set. Default value: inner row. |
rows | Number of rows to be returned; must be greater than 0. |
column | @DATACOL (colnumber) | @DATACOL (colnumber) where colnumber is the target column number; must be between 1 and the maximum number of columns in the report. |
Notes
This command sorts the result set by the value of the specified data column in descending order.
Rows containing #MISSING values in the sort column are discarded from the result set before BOTTOM is applied.
You can use TOP and BOTTOM, ORDERBY and RESTRICT in the same report script, but you can use each command only once per report. If you repeat the same command in a second report in the same report script, the second command overwrites the first. Place global script formatting commands before a PAGE, COLUMN command or associated member (for example, <ICHILDREN or <IDESCENDANTS). Avoid using row formatting commands with BOTTOM.
If any of the ORDERBY, TOP, BOTTOM, or RESTRICT commands exist together in a report script, rowgroupDimension should be the same. Otherwise, an error is issued.
The ORDERBY, TOP, and BOTTOM commands sort a report output by its data values. The RESTRICT command restricts the number of valid rows for the report output. Their order of execution is:
Any sorting command that sorts on member names (for example <SORTDESC or <SORTASC)
RESTRICT
TOP and BOTTOM
ORDERBY
This order of execution applies regardless of the order in which the commands appear in the report script.
You can use configurable settings to specify the size of the internal buffers used for storing and sorting the extracted data. The following settings affect the way the RESTRICT, TOP, and BOTTOM commands work:
Retrieval Buffer Size (a database setting)
Retrieval Sort Buffer Size (a database setting)
NUMERICPRECISION (an essbase.cfg setting)
For more information on the database settings, see the Oracle Essbase Database Administrator's Guide.
Example
Example 1:
<Page (Market, Accounts, Scenario)
Chicago Sales Actual
<Bottom (5, @DataCol(4))
<Column(Year)
<Ichildren Year
<Row(Product)
<Idescendants Product
!
<Bottom (3, @DataCol(1))
{Indentgen 3}
Boston Sales Actual
<Ichildren Year
<Idescendants Product
!Which produces the following report based on the Demo Basic sample database:
Chicago Sales Actual
Qtr1 Qtr2 Qtr3 Qtr4 Year
======== ======== ======== ======== ========
Television 4,410 4,001 4,934 6,261 19,606
VCR 3,879 3,579 4,276 4,877 16,611
Compact_Disc 3,150 3,021 3,032 3,974 13,177
Camera 2,506 2,522 2,602 3,227 10,857
Stereo 2,591 2,476 2,567 3,035 10,669
--------------------------------------------------------------------------------
Boston Sales Actual
Qtr1 Qtr2 Qtr3 Qtr4 Year
======== ======== ======== ======== ========
Compact_Disc 3,290 3,034 3,132 3,571 13,027
Stereo 2,450 2,341 2,377 2,917 10,085
Camera 2,230 2,255 2,266 3,162 9,913
Boston Sales Actual
Qtr1 Qtr2 Qtr3 Qtr4 Year
======== ======== ======== ======== ========
Compact_Disc 3,290 3,034 3,132 3,571 13,027
Stereo 2,450 2,341 2,377 2,917 10,085
Camera 2,230 2,255 2,266 3,162 9,913
Example 2:
The following example uses the ORDERBY, TOP, BOTTOM, and RESTRICT functions:
<TOP ("Year", 10, @Datacol(2))
{Width 15}
{Decimal 2}
{OutAltNames}
<BOTTOM ("Year", 5, @DataCol(2))
<OutMBrAlt
<Column(Scenario)
{SupBrackets}
Actual Budget "Variance %"
<RESTRICT (@DataCol(2) > 3000 and @DataCol(1)
< 3500)
<Row(Year, Product)
<Idescendants Product
<Children Year
<OrderBy ( "Year",@DataCol(1), @DataCol(2) Desc)
!Which produces the following report based on the Sample Basic sample database:
Measures Market
Actual Budget Variance %
============== ============== ==============
Qtr2 100-20 Diet Cola 1,534.00 21,010.00 -38.15
100-20 Diet Cola 1,534.00 21,010.00 -38.15
300-30 Diet Cream 2,723.00 3,100.00 -12.16
300-30 Diet Cream 2,723.00 3,100.00 -12.16
Qtr4 300-30 Diet Cream 2,820.00 3,080.00 -8.44
300-30 Diet Cream 2,820.00 3,080.00 -8.44
200-20 Diet Root 2,834.00 3,790.00 -25.22
200-20 Diet Root 2,834.00 3,790.00 -25.22
Qtr1 200-20 Diet Root 2,963.00 3,600.00 -17.69
Qtr2 200-20 Diet Root 3,079.00 3,640.00 -15.41
200-20 Diet Root 3,079.00 3,640.00 -15.41
Qtr3 200-20 Diet Root 3,149.00 3,700.00 -14.89
200-20 Diet Root 3,149.00 3,700.00 -14.89
400-10 Grape 3,201.00 3,090.00 3.59
300-10 Dark Cream 3,355.00 3,730.00 -10.05See Also