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.
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.
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))
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))
Maine 100-20 734 334 734 534 100-30 324 321 235 278