Orders the rows in a report according to data values in the specified columns.
<ORDERBY ( [<rowgroupDimension>,] <column> [direction>]{,<column> [<direction>]})
<Optional rowgroup Dimension> | Row grouping dimension that determines the rows to sort as a set. |
<column> | @DATACOL (<colnumber>) | @DATACOL (<colnumber>)
where <colnumber> is the target column number; must be between 1 and the maximum number of columns in the report. |
<direction> | You can specify multiple columns with different sorting directions
where:
ASC is the ascending sort DESC is the descending sort |
The innermost row grouping is the default row group dimension. Default direction is ascending.
You can use ORDERBY, TOP, BOTTOM, 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, for example, SAVEROW, before a PAGE, COLUMN command or associated member (for example, <ICHILDREN or <IDESCENDANTS).
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 restriction removes any confusion about the sorting and ordering of rows within a row group. Otherwise, an error is issued.
If TOP or BOTTOM commands exist in the same report with ORDERBY, the ordering column of ORDERBY need not be the same as that of TOP or BOTTOM.
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:
This order of execution applies irrespective 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.
//Page dimension <PAGE("Measures") //Column dimensions <COLUMN("Scenario", "Year") //Row dimensions <ROW("Market", "Product") // Page Members "Sales" // Column Members "Scenario" "Jan" "Feb" "Mar" // Row Members "New York" "Product" "100" "100-10" "100-20" "100-30" "200" "200-10" "200-20" "200-30" "200-40" "300" "300-10" "300-20" "300-30" "400" "400-10" "400-20" "400-30" "Diet" "100-20" "200-20" "300-30" // Data sorting <ORDERBY ("Product", @DATACOL(1) ASC, @DATACOL(2) DESC, @DATACOL(3) ASC) ! // End of report
Which produces the following report based on the Sample Basic sample database:
Sales Scenario Jan Feb Mar ======== ======== ======== New York Diet #Missing #Missing #Missing 300 1 675 695 400 1 664 645 200-40 1 580 523 300-10 1 495 513 300-20 1 180 182 200-20 1 3 #Missing 200-30 1 3 #Missing 100-10 1 3 2 100-20 1 3 2 100-30 1 3 2 100 1 3 2 Product 1 3 2 200-10 1 3 63 200 1 3 586 300-30 1 #Missing #Missing 400-30 134 189 198 400-20 219 243 213 400-10 234 232 234 |
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |