x

To sort the values of a measure from high to low:

• Click the top half of the diamond button.

  or

• Right-click the measure and select Sort By Highest from the menu.

The report runs automatically. The highest value is now first in the column. The top of the diamond button becomes solid blue to indicate the current sort direction.

To sort the values of a measure from low to high:

• Click the bottom half of the diamond button.

  or

• Right-click the measure and select Sort By Lowest from the menu.

The lowest value is first in the column. The bottom of the diamond button becomes solid blue.

Tip: After a measure has been sorted, clicking the upper or lower half of the diamond button inverts the sort order of that measure. Place your mouse over either half of the diamond to see a message that indicates the next sort order that will occur if you click that half of the diamond.

Top of page

Example: Sorting a Measure From High to Low in the Report

The following is an example of sorting a measure from high to low in the report.

1. Run the Standard Report OLAPREP2.

   The OLAP report shows sales information sorted by quarter, store, and product type.

   You are interested in seeing where the greatest quantity of goods have been sold.

2. Click the top half of the diamond button next to the Quantity measure, as shown in the following image, to sort the values from high to low.

   

   As shown in the following image, the report now shows data values for the Quantity measure in descending order. The top half of the diamond next to Quantity is blue and solid to indicate the current sort order of the measure. This is now the controlling sort in the report. All other values are reordered correspondingly.

   

Tip: To invert the sort order, click either the solid or hollow part of the diamond button.

x

1. Open the OLAP Control Panel.
2. Click a measure in the Measures pane to open the sort options pane (do not click the Measures check pane which controls the display of a measure, not its sorting).

   Verify that the Sort panel is checked (this setting is required to apply sorting specifications to the selected measure).

3. Select the High to Low or Low to High options button to specify the sort order you wish to apply. The default sort order is high to low.
4. Click OK.

   The sort pane is replaced by the Measures pane, where the measure becomes blue to indicate that sorting specifications have been defined.

5. Click Run to display the report with sorting applied to the selected measure.

   The diamond button next to the sorted measure changes to reflect the sort order. If high to low, the top half of the diamond is solid blue. If low to high, the bottom half is solid blue.

Note:

• Report execution is automatic when you sort a measure in an OLAP report. However, if the Control Panel is open, all current changes in the Control Panel are applied.
• If an OLAP request contains a horizontal (Across) sort field, the measures appear several times in the report, once for each Across value. If you apply sorting to a measure, the sort is performed on the first column occurrence of the measure, and reflected in all subsequent instances. The appropriate half of the diamond button becomes solid only for the first instance. Any additional sorting you wish to perform must be done from the first occurrence of the measure.

x

You can select to view only a subset of the total number of records in your report.

1. Open the OLAP Control Panel.
2. Click a measure name in the Measures pane to open the sort options pane (do not click the Measures check pane which controls the display of a measure, not its sorting).

   Verify that the Sort check panel is selected (this setting is required to apply sorting specifications to a measure).

3. Select the Rank check pane, then specify the number of sort field values to be included in the report.
   • Use the spin buttons located to the right of the word Highest or Lowest to increase or decrease the number of sort fields.

     or

   • Position the cursor in the input pane and type a number.

   The default number of sort fields values is 5.

4. Click OK.

   The sort pane is replaced by the Measures pane, where the measure becomes blue to indicate that sorting specifications have been defined.

5. Click Run to display the report with the designated number of sorted values.

Top of page

Example: Displaying a Subset of Sorted Data for a Measure

The following is an example of displaying a subset of sorted data for a measure.

1. Run the Standard Report OLAPREP2.

   The report shows sales information sorted by quarter, store, and product type.

2. Click the square icon next to QUARTER to open the Control Panel (notice that the original report is open on the left).
3. Click Quantity in the Measures pane.

   The sort pane opens, as shown in the following image, in front of the report.

   

4. If not already selected, click the Sort check pane.

   High to low sorting is selected by default.

5. Click the Rank check pane.

   Because the report is being sorted from high to low, you can indicate the number of values you wish to see, beginning with the highest.

6. Specify Highest 4.
7. Click OK.

   The main Control Panel window appears. In the Measures pane the Quantity measure is blue to indicate that sorting specifications have been defined.

8. Click the Run button at the bottom of the Control Panel.

   As shown in the following image, the report now shows Quantity sorted from high to low with the highest four values appearing.

   

x

You can remove sorting specifications for a measure whether the measure appears or is hidden.

1. Open the OLAP Control Panel.
2. In the Measures pane, click the measure for which you want to remove sorting specifications.
3. Clear the Sort check pane.
4. Click OK.

x

1. Open the Control Panel.
2. Select a field from the Drill Down or Drill Across pane.
3. Click the Sort button.

   The sort pane opens.

4. Under Sort Order, choose the Low to High or High to Low options button (Low to High is the default for a dimension).
5. Click OK.

   The main Control Panel window reopens.

6. Click Run to execute the report.

Top of page

Example: Inverting the Sort Order of a Dimension

The following is an example of inverting the sort order of a dimension.

1. Run the Standard Report OLAPREP4.

   In the report, the values of both sort fields (Continent and Region) are sorted from low to high (A to Z), as shown in the following image.

   

2. To sort the report in reverse alphabetical order, click the OLAP button on the band below the Selections pane to open the Control Panel.
3. Select Continent in the Drill Down pane and click the Sort button.

   The sort pane opens.

4. Under Sort Order, choose the High to Low options button, as shown in the following image, on the OLAP Control Panel.

   

5. Click OK.

   The main Control Panel window reopens.

6. Repeat the process for Region: select Region in the Drill Across pane and click the Sort button. When the sort pane opens, select the High to Low options button and click OK.

   The main Control Panel window opens.

7. Click the Run button.

   Both dimensions are now sorted in inverse alphabetical order (Z to A), as shown in the following image.

   

x

To restrict the display of sort field values to a certain number of highest or lowest values:

1. Open the OLAP Control Panel.
2. Select a field from the Drill Down pane.
3. Click the Sort button.

   The sorting pane opens.

4. Under Sort Order, choose the Low to High or High to Low options button, as shown in the following image, on the OLAP Control Panel.

   

5. Under Limit Output, click the Limit check pane and choose or type a value in the input area.
6. Click OK.

   The main Control Panel window reopens.

7. Click Run to execute your report.

x

1. Open the OLAP Control Panel.
2. Select a field from the Drill Down pane.
3. Click the Sort button.

   The sort pane opens.

4. Under Sort Order, choose the Low to High or High to Low options button.
5. Click the Rank check pane.
6. If you wish to place a restriction on the number of sort field values to rank, click the Limit check pane, and choose or type a value in the input area.
   • If the High to Low option button is selected, you can rank a specified number of Highest values.
   • If the Low to High option button is selected, you can rank a specified number of Lowest values.
7. Click OK.

   The main Control Panel window reopens.

8. Click Run to execute your report.

Top of page

Example: Ranking and Restricting the Number of Sort Values

The following is an example of ranking and restricting the number of sort values.

1. Run the Standard Report OLAPREP2.

   Information for all stores is shown for each quarter. You want to see quarterly information for only the first two stores in alphabetical order (low to high).

2. Click the square icon next to QUARTER to open the Control Panel (notice that the original report is open at the left).
3. Choose Store Name in the Drill Down pane and click the Sort button.

   The sort pane opens.

   The following image shows these three selections on the OLAP Control Panel.

   

   1. Accept the default sort order: Low to High.
   2. Click the Limit check pane and choose 2 from the input area.
   3. Click the Rank check pane.
4. Click OK to return to the main Control Panel window.
5. Click the Run button at the bottom of the Control Panel.

   Notice that only two values now appear for each Quarter and ranked low to high within each group, as shown in the following image.

   

x

You can change the order in which data is sorted and presented in the report. For example, you can change from sorting by State and then by Product to sorting by Product and then by State. If you want to reposition:

• Vertical (By) sort fields, drag and drop a field into a new column position.
• Horizontal (Across) sort fields, drag and drop the lower field above the higher one or the higher field above the lower one.

In each case, the cursor changes to a plus sign (+) to indicate acceptable places into which you can drop the field. Unacceptable positions are shown by a circle with a slash across the center.

Top of page

Example: Repositioning Sort Fields in an OLAP Report

The following is an example of repositioning sort fields in an OLAP report.

1. Run the Standard Report OLAPREP2.
2. Click the top half of the diamond button next to Quantity to sort values from high to low.

   The dimension values adjust accordingly. The report now shows the Quantity values from high to low but according to the QUARTER sort order, as shown in the following image.

   

   You would like to change the sort order in the report, making Store Name the first sort field, followed by PRODTYPE and QUARTER.

3. Drag QUARTER after PRODTYPE.

   The cursor changes to a plus sign (+) to indicate acceptable places into which you can drop the field.

   The report changes immediately, as shown in the following image, with the Store Name being the first sort order.

   

x

1. Open the OLAP Control Panel.
2. Select a field in the Drill Down or Drill Across pane.
3. Click the Shift Up or Shift Down arrow until the field is in the desired position.

   Repeat for other fields as needed.

4. Click Run to execute your report.

Top of page

Example: Repositioning Sort Fields From the Control Panel

The following is an example of repositioning sort fields from the Control Panel.

1. Run the Standard Report OLAPREP2.
2. Click the top half of the diamond button next to Quantity to sort values from high to low.

   The dimension values adjust accordingly. The report now shows the Quantity values from high to low but according to the QUARTER sort order, as shown in the following image.

   

   You would like to change the sort order in the report, making Store Name the first sort field, followed by PRODTYPE and QUARTER.

3. Click the square icon button next to QUARTER to open the Control Panel.
4. Select Quarter from the Drill Down pane.
5. Click the Shift Down arrow twice.

   QUARTER is now the third item in the Drill Down list, as shown in the following image.

   

6. Click the Run button at the bottom of the Control Panel.

   QUARTER appears in the third column of the report, as shown in the following image.

   

x

In OLAP, you can hide a sort field by clicking the Hide check box in a report.

Note: In past releases, the text "(hidden)" was displayed to the right of the field name in the Drill Down pane in the OLAP Control Panel. Beginning with Version 7 Release 7.02, hidden sort fields are indicated by reversing the color of the icon that appears at the left of the field name.

1. Enter the following code in an ad hoc page.

   -OLAP ON
   TABLE FILE CAROLAP
   SUM CAROLAP.BODY.DEALER_COST
   CAROLAP.BODY.RETAIL_COST
   BY CAROLAP.ORIGIN.COUNTRY
   BY CAR
   END

2. Open the OLAP Control Panel.
3. Double-click on the Country field in the Drill Down panel of the OLAP Control Panel. In the resulting window panel, select the Hide check box.
4. Click OK.

   Notice that the color of the sort icon has been reversed. The Drill Down panel now appears as shown in the following image.

   

x

You can quickly change a field from one that sorts data vertically, creating rows, to one that sorts data horizontally, creating columns, or vice versa.

To change a:

• Vertical (By) sort field to a horizontal (Across) sort field, drag and drop a field above the row of column titles.
• Horizontal (Across) sort field to a vertical (By) sort field, drag and drop the field into the desired location in the row of column titles.

In each case, the cursor changes to a plus sign (+) to indicate acceptable places where you can drop the field. Unacceptable places have a circle with a slash across the center.

Top of page

Example: Pivoting Rows and Columns in a Report

The following is an example of pivoting rows and columns in a report.

1. Run the Standard Report OLAPREP2.
2. Click Q1.

   The report is now sorted vertically, by month, store, and product type, as shown in the following image.

   

   You want to create a matrix in which data is sorted horizontally by month, and vertically by store and product type.

3. Drag Month above the report to sort data horizontally (Across).

   The cursor changes to a plus sign (+) to indicate acceptable places where you can drop the field.

   In the new report, Quantity and Line Cost of Goods Sold are repeated horizontally for each month, as shown in the following image.

   

x

You can change a field from one that sorts data vertically, creating rows, to one that sorts data horizontally, creating columns, or vice versa.

1. Open the OLAP Control Panel.
2. Select the title of the row or column you want to pivot in the Drill Down or Drill Across pane.
3. Click the Pivot button. The title appears in the new location.
4. Click Run to execute your report.

Top of page

Example: Pivoting Rows Into Columns From the Control Panel

The following is an example of pivoting rows into columns from the Control Panel.

1. Run the Standard Report OLAPREP2.
2. Click Q1.

   The report is now sorted vertically, by month, store, and product type, as shown in the following image.

   

   You want to create a matrix in which data is sorted horizontally by month, and vertically by store and product type.

3. Click the square icon next to MONTH to open the Control Panel.
4. Select Month in the Drill Down pane and click the Pivot button.

   Month moves into the Drill Across pane, as shown in the following image.

   

5. Click the Run button on the Control Panel.

   In the new report, Quantity and Line Cost of Goods Sold are repeated horizontally for each month, as shown in the following image.

   

x

To use a field to sort your data, but not show the sort field as a column in the report:

1. Open the OLAP Control Panel.
2. Select a field in the Drill Down or Drill Across pane.
3. Click the Sort button.

   The sort pane opens.

4. Under Sort Order, click the Hide check pane.
5. Click OK.

   The main Control Panel window reopens.

6. Click Run to execute the report.

Tip: To expose the hidden sort field, repeat the process and deselect the Hide check pane.

Top of page

Example: Sorting by a Hidden Field

The following is an example of sorting by a hidden field.

1. Run the Standard Report OLAPREP2.

   The first sort field in the report is QUARTER. You want to retain the sorting but not display this field.

2. Click the square icon next to QUARTER to open the Control Panel.
3. Select QUARTER in the Drill Down pane, then click the Sort button.

   The sort pane opens.

4. Select the Hide check pane, as shown in the following image.

   

5. Click OK.

   The main Control Panel window reopens.

6. Click the Run button in the Control Panel.

   Report sorting is unchanged, but the QUARTER column no longer appears, as shown in the following image.

   

x

1. Open the OLAP Control Panel.
2. Select a numeric or date field from the Drill Down pane.
3. Click the Sort button. Click the Tiles tab.

   

4. Click the Tile the Report check pane.
5. In the In Groups Of input area, select the number of tiles to be used in grouping the data. For example, 100 tiles produces percentiles or 10 tiles produces deciles.
6. In the Name of Tile Group input pane, type a name for the Tile column.
7. In the Restrict Report to only the Top input area, select the number of tile groups to display in the report.
8. Optionally, select a Sort Order option button:
   • Choose High to Low to sort data in descending order so that the highest data values are placed in tile 1.
   • Choose Low to High to sort data in ascending order so that the lowest data values are placed in tile 1. This is the default.
9. If you wish to specify the highest tile value to appear in the report, select a value from the Limit input area. For example, if you enter a Limit of 3, the report will not display any data row that is assigned a tile number greater than 3.
10. Click OK to accept the selections and return to the main Control Panel window.
11. Click Run to reexecute and view the report.