The following report demonstrates the use of the ORDERBY conditional retrieval command in a report script. For a discussion of various issues related to use of the ORDERBY command, see "Restricting and Ordering Data Values" in the Oracle Essbase Database Administrator's Guide.
Sales Scenario Jan Feb Mar Apr ======== ======== ======== ======== New York 100-20 #Missing #Missing #Missing #Missing 100-30 #Missing #Missing #Missing #Missing 200-20 #Missing #Missing #Missing #Missing 200-30 #Missing #Missing #Missing #Missing 300-30 #Missing #Missing #Missing #Missing Diet #Missing #Missing #Missing #Missing 200-10 61 61 63 66 400-30 134 189 198 198 300-20 180 180 182 189 400-20 219 243 213 223 400-10 234 232 234 245 300-10 483 495 513 638 200-40 490 580 523 564 200 551 641 586 630 400 587 664 645 666 300 663 675 695 827 100-10 678 645 675 712 100 678 645 675 712 Product 2,479 2,625 2,601 2,835
Use the following script to create Sample 18:
<Page ("Measures") <Column ("Scenario", "Year") <Row ("Market", "Product") "Sales" "Scenario" "Jan" "Feb" "Mar" "Apr" "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" <ORDERBY ("Product", @DATACOL(1) ASC, @DATACOL(2) DESC, @DATACOL(3) ASC @DataCol (4) DESC) !
The ORDERBY command is based only on data in the data columns. If the SUPPRESSMISSING command is not used in the report, #MISSING is considered to be the lowest data value. ORDERBY compares data values in the following order:
Two values in the same column (for example, in COL1, the value associated with 200-10 is compared with the 400-30 data value, as shown in the example below).
Data values between two data columns (for example, the data value in COL1 is compared with the data value in COL2, as shown in the example next).
If two data values are the same, the sort proceeds to the next column to determine the order.
In the following subset of Sample 18, for Product 200-10, the data values in COL1 and COL2 are both 61; the data in COL1 should be in ascending order, the data in COL2 should be in descending order. The two values are compared, and as they are the same, COL2 and COL3 are compared. Therefore, even though COL2 is supposed to be in descending order, the comparison for the row 400-30 was determined by the values in COL3, which is in ascending order.
COL 1 COL 2 COL 3 COL 4 ===== ===== 200-10 61 61 63 66 400-30 134 189 198 198 300-20 180 180 182 189
The report script for Sample 18, ORDERBY.REP, is available in the \ARBORPATH\App\Sample\Basic directory, and is displayed in Enterprise View in Administration Services.