ORDERBY

Orders the rows in a report according to data values in the specified columns.

Syntax

<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

Default Value

The innermost row grouping is the default row group dimension. Default direction is ascending.

Description

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:

  1. Any sorting command that sorts on member names (for example <SORTDESC or <SORTASC)
  2. RESTRICT
  3. TOP and BOTTOM
  4. 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.

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           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
				 
				 

See Also

RESTRICT
TOP
BOTTOM

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com