1. Scripting Reference for Oracle Essbase
  2. Report Writer
  3. Report Writer Command List
  4. BOTTOM

BOTTOM

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

Syntax

<BOTTOM ([rowgroupDimension,] rows, column)

Parameters

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

@DATACOLUMN (colNumber) | @DATACOLUMN (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:

  1. Any sorting command that sorts on member names (for example <SORTDESC or <SORTASC)

  2. RESTRICT

  3. TOP and BOTTOM

  4. 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 configuration

Example

Example 1:

The following report script is designed for the Demo Basic cube, available in the gallery.

<Page (Market, Accounts, Scenario)
 Chicago Sales Actual
<Bottom (5, @DataColumn(4))
<Column(Year)
<Ichildren Year
<Row(Product)
<Idescendants Product
!
<Bottom (3, @DataColumn(1))
{Indentgen 3}
Boston Sales Actual
<Ichildren Year
<Idescendants Product
!

The report script produces the following report:

                             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

Example 2:

The following report script example is designed for the Sample Basic cube, available in the gallery. It uses the ORDERBY, TOP, BOTTOM, and RESTRICT functions:

<TOP ("Year", 10, @DataColumn(2))
{Width 15}
{Decimal 2}
{OutAltNames}
<BOTTOM ("Year", 5, @DataColumn(2))
<OutMBrAlt
<Column(Scenario)
 {SupBrackets}
 Actual Budget  "Variance %"
<RESTRICT (@DataColumn(2) > 3000 and @DataColumn(1)
 < 3500)
<Row(Year, Product)
<Idescendants Product
<Children Year
<OrderBy ( "Year",@DataColumn(1), @DataColumn(2) Desc)
!

The report script produces the following report:


                                                Measures Market 

                                          Actual         Budget     Variance % 
                                  ============== ============== ============== 

Qtr1             300-10 Dark Cream      2,544.00       3,010.00         -15.48 
                 300-30 Diet Cream      2,695.00       3,070.00         -12.21 
                                        2,695.00       3,070.00         -12.21 
Qtr4             300-30 Diet Cream      2,820.00       3,080.00          -8.44 
                                        2,820.00       3,080.00          -8.44 
                 200-20 Diet Root       2,834.00       3,790.00         -25.22 
                                        2,834.00       3,790.00         -25.22 
Qtr1             200-20 Diet Root       2,963.00       3,600.00         -17.69 
                                        2,963.00       3,600.00         -17.69 
Qtr2             200-20 Diet Root       3,079.00       3,640.00         -15.41 
                                        3,079.00       3,640.00         -15.41 
Qtr3             200-20 Diet Root       3,149.00       3,700.00         -14.89 
                                        3,149.00       3,700.00         -14.89 
Qtr2             300-10 Dark Cream      3,231.00       3,570.00          -9.50 
Qtr3             300-10 Dark Cream      3,355.00       3,730.00         -10.05
RESTRICT
TOP
ORDERBY