Sample 18: Ordering Data Values

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 Designing and Maintaining Essbase Cubes.

                                           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", @DATACOLUMN(1) ASC, @DATACOLUMN(2) DESC, @DATACOLUMN(3) ASC @DATACOLUMN (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.