Orders the rows in a report according to data values in the specified columns.
Syntax
<ORDERBY ( [<rowgroupDimension>,] <column> [direction>]{,<column> [<direction>]})
Parameter | Description |
---|---|
<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 |
Notes
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:
Any sorting command that sorts on member names (for example <SORTDESC or <SORTASC)
RESTRICT
TOP and BOTTOM
ORDERBY
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.
The innermost row grouping is the default row group dimension. Default direction is ascending.
Example
//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 100-20 #Missing #Missing #Missing 100-30 #Missing #Missing #Missing 200-20 #Missing #Missing #Missing 200-30 #Missing #Missing #Missing 300-30 #Missing #Missing #Missing Diet #Missing #Missing #Missing 200-10 61 61 63 400-30 134 189 198 300-20 180 180 182 400-20 219 243 213 400-10 234 232 234 300-10 483 495 513 200-40 490 580 523 200 551 641 586 400 587 664 645 300 663 675 695 100-10 678 645 675 100 678 645 675 Product 2,479 2,625 2,601
See Also