<BOTTOM

Command

The BOTTOM command returns rows with the lowest values of a specified data column.

Syntax

BOTTOM ([<rowgroupDimension>,]<rows>, <column>)

(<rowgroupDimension> Optional row grouping dimension that determines the rows to sort as a set.

<rows> Positive integer number of rows to be returned; must be greater than 0.

<column> @DATACOL (<colnumber>) | @DATACOLUMN (<colnumber>)
where <colnumber> is the target column number; must be between 1 and the maximum number of columns in the report.

Default Value

The row grouping dimension has a default of the inner row.

Description

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, the row group dimension <rowgroupDimension> should be the same. This prevents confusion about the sorting and ordering of rows within a row group. 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 irrespective of the order in which the commands appear in the report script.

You can use configurable variables to specify the size of the internal server buffers used for storing and sorting the extracted data. The ReptKByteBuf, ReptKByteSortBuf, and NumericPrecision variables affect the way the RESTRICT, TOP, and BOTTOM commands work. For more information on configurable variables, see the Database Administrator's Guide.

Example

<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

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.05

See Also

RESTRICT
TOP
ORDERBY

(<rowgroupDimension>	Optional row grouping dimension that determines the rows to sort as a set.
<rows>	Positive integer number of rows to be returned; must be greater than 0.
<column>	@DATACOL (<colnumber>) \| @DATACOLUMN (<colnumber>) where <colnumber> is the target column number; must be between 1 and the maximum number of columns in the report.