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

TOP

The TOP Report Writer command in Essbase returns rows with the highest values of a specified data column.

Syntax

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

Parameters

<rowgroupDimension>

Optional. Row grouping dimension that determines the rows to sort as a set. The default is the inner row.

<rows>

Positive integer that specifies the 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

The TOP 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 TOP 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 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 TOP.

If any of the ORDERBY, TOP, BOTTOM, or RESTRICT commands coexist 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 execution order 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 applies regardless of the order in which the commands appear in the report script. For an example that uses TOP, BOTTOM, ORDERBY, and RESTRICT together, see the entry for the BOTTOM command.

You can configure 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

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

<Sym
<Column (Scenario, Year)
Actual Budget
Jan Dec
<Top ("Measures", 5, @DATACOLUMN(4))
<Row (Measures, Market, Product)
{SupMissingRows}

<Idescendants Profit
<Ichildren Market
<Idescendants Product
 !

The report script above produces the following report:

                                    Actual            Budget
                                 Jan      Dec       Jan      Dec
                                ====== ========  ======== ========

Sales    Market   Product      31,538   33,342   31,538   30,820 
  Margin Market   Product      17,378   18,435   17,378   17,360 
COGS     Market   Product      14,160   14,907   14,160   13,460 
Sales    Central  Product      10,346   10,662   10,346   10,310 
         West     Product      10,436   11,116   10,436   10,200
RESTRICT
ORDERBY
BOTTOM