1. Scripting Reference for Oracle Essbase
  2. Report Writer
  3. Report Writer Command List
  4. ORDERBY

ORDERBY

The Essbase Report Writer ORDERBY command orders the rows in a report according to data values in the specified columns.

Syntax

<ORDERBY ( [<rowgroupDimension>,] <column> [<direction>]{,<column> [<direction>]})

Parameters

<Optional rowgroup Dimension>

Row grouping dimension that determines the rows to sort as a set.

<column>

@DATACOLUMN (<colnumber>) | @DATACOLUMN (<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:

  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.

Default Value

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

Example

The following report script is designed for the Sample Basic cube, available in the gallery.

//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", @DATACOLUMN(1) ASC, @DATACOLUMN(2) DESC, @DATACOLUMN(3) ASC) 
!
// End of report

The report script above produces the following report:

                                        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
RESTRICT
TOP
BOTTOM