Using TOP and BOTTOM

The <TOP and <BOTTOM commands specify the qualified number of rows with the highest or lowest column values, respectively, within a row group to be returned in a report. If the row group member is not specified, the innermost row group dimension is the default row group.

You can use <TOP and <BOTTOM together in the same report, but only one <TOP and one <BOTTOM is allowed per report. In this case, the two commands should have the same data column as their argument in order to prevent confusion. The result of the <TOP and <BOTTOM command is sorted by the value of the data column specified in the command in descending order.

<TOP and <BOTTOM work only on the range of rows specified in row member selection.

Note:

If <TOP or <BOTTOM occurs with <ORDERBY, the ordering column of the <ORDERBY does not have to be the same as the data column of the <TOP or the <BOTTOM.

If any combination of the <ORDERBY, <TOP, or <BOTTOM commands exist together in a report script, the row group member (<rowGroupMember>) should be the same. This restriction removes any confusion about the sorting and ordering of rows within a row group.

Caution!

Essbase discards rows that contain #MISSING values in their sorting column from the set of extracted data rows before the applying the TOP or BOTTOM sort.

For example, this command returns two rows with the highest data values in col2 (Actual, Qtr2) per row group:

1- TOP (2, @DATACOL(2))

When you run this command against the Sample.Basic database, the row grouping is Product, which implies that for Florida, the report returns 100-10 and 100-30 product rows, and for Maine, the report returns 100-10, 100-40 product rows, and so on:

                            Actual              Budget
                       Qtr1       Qtr2       Qtr1    Qtr2   
Florida     100-10      570       670        570     650 
            100-20      235       345        321     432 
            100-30      655       555        455     865 
            100-40      342       342        432     234 
Maine       100-10      600       800        800     750 
            100-20      734       334        734     534 
            100-30      324       321        235     278 
            100-40      432       342        289     310 
New York    100-10     1010      1210       1110     910 
            100-20      960       760        650     870 
            100-30      324       550        432     321 
            100-40      880       980        880    1080 
            100-50      #MI       #MI        #MI     #MI

This example returns rows with the highest data values in col2 (Actual, Qtr2) per report, because the row grouping is the “market.”

2- TOP("market", 3, @DATACOL(2))

Resulting rows:

New York    100-10     1010     1210     1110     910   
            100-40      880      980      880    1080 
Maine       100-10      600      800      800     750

This example returns two rows with the lowest data values in col2 (Actual, Qtr2) per row group.

3- BOTTOM ("market", 2, @DATACOL(2))

Resulting rows:

Maine       100-20      734       334       734      534   
            100-30      324       321       235      278

Note:

<TOP and <BOTTOM put an upper limit on the number of (qualified) rows returned after all restrictions are applied. This upper limit equals the number of rows in the <TOP plus the number of rows in the <BOTTOM commands.