Sorting Data

In this section:

You can sort the data in an OLAP report based on the values of dimensions in the hierarchy and/or the values of the quantitative measures that constitute the body of the report. Sorting options vary depending on the nature of the data being sorted. For details, see Sorting Measures and Sorting Dimensions.

You can also group numeric data into any number of tiles (percentiles, quartiles, deciles, and so on). See Grouping Numeric Data Into Tiles.

Sorting Measures

How to:

You can apply aggregation and sorting simultaneously to a numeric measure in an OLAP report, and sort the data from high to low (descending order) or from low to high (ascending order). All other columns are sorted correspondingly.

For the measure being sorted, you can restrict the report to a specified number of highest values (when sorting high to low) or lowest values (when sorting from low to high).

When you sort a measure, any subtotals, subheadings, or subfootings in the report are automatically suppressed since these elements relate to a specific sort field and are not meaningful when the report is resorted by the values in a measure column. For an illustration, see How to Applying a Percent Calculation to a Measure.

Note: Sorting by measures is not available in a report in which measures have been stacked. See Hiding and Displaying Measures.

Procedure: How to Sort Measures High to Low or Low to High in an OLAP Report

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

  • Click 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 once, clicking the upper or lower half of the diamond button inverts the sort order of that measure. Place your mouse pointer over either half of the diamond to see a message that indicates the next sort order that will occur if you click the diamond.

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

  1. Run 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 has been sold.

  2. Click the top half of the diamond button next to the Quantity measure to sort the values from high to low.

    As shown in the following image, the report now displays 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.

    Rep 2 sort quantity

Tip: To invert the sort order, click the diamond button again.

Procedure: How to Sort Measures High to Low or Low to High From the OLAP Control Panel

  1. Open the OLAP Control Panel.
  2. Click a measure name in the Measures pane in the upper portion of the OLAP Control Panel to open the sort options pane.

    Do not click the Stack Measures check box, which controls the display of a measure, not its sorting.

  3. Select the Sort check box. This setting is required to apply sorting specifications to the selected measure.
  4. Select the High to Low or Low to High option button to specify the sort order you wish to apply. The default sort order is high to low.
  5. Click Ok.

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

  6. 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 the sort order is high to low, the top half of the diamond is solid blue. If the sort order is 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 OLAP Control Panel is open, all current changes in the OLAP 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.

Procedure: How to View a Subset of Data for Sorted Measures

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 Stack Measures check box, which controls the display of a measure, not its sorting.

  3. Verify that the Sort check box is selected. (This setting is required to apply sorting specifications to a measure.)
  4. Select the Rank check box, then specify the number of sort field values to be included in the report.
    • Use the spin controls 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 field values is 5.

  5. Click Ok.

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

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

Example: Displaying a Subset of Sorted Data for a Measure

  1. Run OLAPREP2.
    OLAP Report 2 default

    The report shows sales information sorted by quarter, store, and Product Type.

  2. Click the square icon next to QUARTER to open the OLAP 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.

    WebFOCUS OLAP Control Panel
  4. If not already selected, click the Sort check box.

    High to Low sorting is selected by default.

  5. Click the Rank check box.

    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 OLAP Control Panel window appears. In the Measures pane the Quantity measure is blue, indicating that sorting specifications have been defined.

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

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

Procedure: How to Remove Sorting Criteria for a Measure

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 box.
  4. Click Ok.

Sorting Dimensions

How to:

There are several ways in which you can sort dimensions in an OLAP hierarchy:

Procedure: How to Change Sort Order for a Dimension

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

    The sort pane opens.

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

    The main OLAP Control Panel window reopens.

  6. Click Run to execute the report.

Example: Reversing the Sort Order of a Dimension

  1. Run 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.

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

    The sort pane opens.

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

    The main OLAP Control Panel window reopens.

  6. Repeat the process for Continent. Select Continent in the Drill Down pane and click the Sort button. When the sort pane opens, select the High to Low option button and click Ok.

    The main OLAP Control Panel window opens.

  7. Click Run.

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

    OLAP Report 4. Two dimensions sorted high to low.

Procedure: How to Restrict the Display of Sort Values

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

    The sorting pane opens.

  4. Under Sort Order, choose the Low to High or High to Low option button, as shown in the following image, on the OLAP Control Panel.
  5. Under Limit Output, click the Limit check box and choose or type a value in the input area.
  6. Click Ok.

    The main OLAP Control Panel window reopens.

  7. Click Run to execute your report.

Procedure: How to Rank Rows in a Vertically Sorted Report

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

    The sort pane opens.

  4. Under Sort Order, choose the Low to High or High to Low option button.
  5. Click the Rank check box.
  6. If you wish to place a restriction on the number of sort field values to rank, click the Limit check box, 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 OLAP Control Panel window reopens.

  8. Click Run to execute your report.

Example: Ranking and Restricting the Number of Sort Values

  1. Run 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 OLAP Control Panel (notice that the original report remains open at the left).
  3. Choose Store Name in the Drill Down pane and click the Sort Sort button.

    The sort pane opens.

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

    WebFOCUS OLAP Control Panel
    1. Accept the default sort order: Low to High.
    2. Select the Limit check box and choose 2 for the limit.
    3. Select the Rank check box.
  4. Click Ok to return to the main OLAP Control Panel window.
  5. Click Run at the bottom of the OLAP Control Panel.

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

    OLAP Sort limit=2

Procedure: How to Reposition Sort Fields in an OLAP Report

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 a field into a new column position.
  • Horizontal (Across) sort fields, drag 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.

Example: Repositioning Sort Fields in an OLAP Report

  1. Run 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 Product Type and QUARTER.

  3. Drag QUARTER after Product Type.

    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.

    OLAP Report 2. Sorted with Store Name first

Procedure: How to Reposition Sort Fields from the OLAP Control Panel

  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.

Example: Repositioning Sort Fields from the OLAP Control Panel

  1. Run 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 Product Type and QUARTER.

  3. Click the square icon next to QUARTER to open the OLAP 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.

    WebFOCUS OLAP Control Panel
  6. Click Run at the bottom of the OLAP Control Panel.

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

    OLAP Report 2. Sorted with Store Name first

Procedure: How to Hide a Sort Field

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

Note: 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 pane 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 pane now appears, as shown in the following image.

    shift down

Procedure: How to Pivot Rows and Columns In an OLAP Report

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 a field above the row of column titles.
  • Horizontal (Across) sort field to a vertical (By) sort field, drag 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.

Example: Pivoting Rows and Columns in a Report

  1. Run 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.

    OLAP Report 2, by month

Procedure: How to Pivot Rows and Columns from the OLAP Control Panel

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 Pivot button. The title appears in the new location.
  4. Click Run to execute your report.

Example: Pivoting Rows Into Columns from the OLAP Control Panel

  1. Run 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 OLAP Control Panel.
  4. Select MONTH in the Drill Down pane and click the Pivot Pivot button.

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

    OLAP Report 2 with MONTH pivoted
  5. Click Run on the OLAP 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.

    OLAP Report 2, by month

Procedure: How to Sort by a Field Without Displaying the Sort Column

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

    The sort pane opens.

  4. Under Sort Order, select the Hide check box.
  5. Click Ok.

    The main OLAP 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 box.

Example: Sorting by a Hidden Field

  1. Run 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 OLAP Control Panel.
  3. Select QUARTER in the Drill Down pane, then click the Sort Sort button.

    The sort pane opens.

  4. Select the Hide check box, as shown in the following image.
    OLAP
  5. Click Ok.

    The main OLAP Control Panel window reopens.

  6. Click Run in the OLAP Control Panel.

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

    OLAP Report 2 with Quarter hidden

Grouping Numeric Data Into Tiles

How to:

You can group numeric data into any number of tiles (percentiles, deciles, quartiles, and so on) in tabular reports. For example, you can group student test scores into deciles to determine which students are in the top ten percent of the class.

Grouping is based on the values in the selected vertical (BY) field and data is apportioned into the number of tile groups you specify.

The following occurs when you group data into tiles:

Procedure: How to Group Data Into Tiles in an OLAP Report

  1. Open the OLAP Control Panel.
  2. Select a numeric or date field from the Drill Down pane.
  3. Click the Sort button.
  4. Click the Tiles tab, as shown in the following image.
    WebFOCUS OLAP Control Panel
  5. Click the Tile the Report check box.
  6. 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.
  7. In the Name of Tile Group input pane, type a name for the Tile column.
  8. In the Restrict Report to only the Top input area, select the number of tile groups to display in the report.
  9. Optionally, select the Sort tab and 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.
  10. 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.
  11. Click Ok to accept the selections and return to the main OLAP Control Panel window.
  12. Click Run to execute and view the report.