13
Sorting data

Sorting data

This chapter explains how to use Discoverer's sorting capabilities such as alphabetical, numeric, and group sorting. This section contains the following topics:

• "What is sorting?"

• "About sorting on table worksheets"

• "About sorting on crosstab worksheets"

• "What is group sorting?"

• "How to sort data on a table worksheet"

• "How to sort data on a crosstab worksheet"

• "How to change how worksheet data is sorted"

• "How to remove sorting from a worksheet"

• "Examples of sorting"

What is sorting?

Sorting is the process of arranging data into meaningful order so that you can analyze it more effectively. For example, you might want to order sales data by calendar month so that you can produce a graph of sales performance. You can use Discoverer to sort data as follows:

• sort text data into alphabetical order

• sort numeric data into numerical order

• group sort data to many levels, for example, you can sort on City within Month within Year

Sorting worksheet data also makes it easier to analyze. For example, you might want to sort sales data from most profitable sales to least profitable sales to show the relative position of your company's best selling products.

Discoverer offers great flexibility when sorting data within data. You can do this to many different levels. For example, you can sort by City within Region.

Note: Discoverer sorts data according to the alphabetical or numeric sequence most appropriate for the local language. For more information about choosing a language when you start Discoverer, contact the Discoverer manager.

About sorting on table worksheets

On table worksheets, you can sort columns individually or in groups. For example, the figure below shows a worksheet sorted on one item (Region) in the order low to high (i.e. A to Z).

Figure 13-1 A table worksheet sorted on Region

Text description of the illustration sort_3.gif

The figure below shows a table worksheet sorted on two items, City within Region in the order low to high (i.e. A to Z).

Figure 13-2 A table worksheet sorted on City within Region

Text description of the illustration sort_8.gif

For more examples on sorting in Discoverer, see "Examples of sorting".

About sorting on crosstab worksheets

On crosstab worksheets, you can sort by either of the following:

• by columns (on the vertical axis)

• by rows (on the horizontal axis)

By default, Discoverer sorts data on a crosstab automatically as follows:

• text data is sorted alphabetically from A-Z (language dependent)

• numeric data is sorted from lowest to highest

Because the location of data on a crosstab worksheet determines the relationship of one data item to another, sorting on a crosstab worksheet is different to sorting on a table worksheet. When you sort on a crosstab worksheet, you typically want to maintain data relationships while rearranging the data.

Whichever way you sort (i.e. by column or row) Discoverer automatically maintains data relationships.

Note: On crosstab worksheets, you can remove additional sorts that you have added to the worksheet but you cannot remove the original default sort.

The figure below shows a crosstab worksheet sorted on Profit SUM in the order high to low (i.e. A to Z).

Figure 13-3 A crosstab worksheet sorted on Profit SUM (high to low)

Text description of the illustration st1.gif

Note: In the example above, the worksheet is sorted on Region by default. This default sort cannot be removed.

For more examples on sorting in Discoverer, see "Examples of sorting".

What is group sorting?

Group sorting is a facility available on table worksheets that removes repeated values to make reports easier to analyze. Group sorting has the following effects:

• The group name is displayed only once at the start of a group.

• Repeated group name values are removed from the worksheet.

• Group sorts take precedence over non-group sorts. If you have more than one item in the sort list, the item with the Group Sort option selected automatically becomes the first item in the sort list.

In figure below, the worksheet on the left has group sort on Region turned off (i.e. the Sort Type setting is Normal). The worksheet on the right has the group sort on Region turned on (i.e. the Sort Type setting is Group Sort). Notice that the repeated regions are removed when group sort is turned on.

Figure 13-4 Using the Group Sort option

Text description of the illustration sortex2.gif

Key to figure:

1. The Sort dialog and worksheet before group sort is turned on.

2. The Group field set to none.

3. The Sort dialog and worksheet after group sort is turned on.

4. The Group field set to Group Sort.

How to sort data on a table worksheet

You sort worksheet data to arrange it for more effective analysis. For example, you might want to sort a list of sales figures numerically so that you analyze the relative standing of sales people.

To sort data on a table worksheet:

1. Display the worksheet that you want to sort.

2. Choose Tools | Sort to display the "Edit Worksheet dialog: Sort tab".

   
   
   Text description of the illustration st.gif

   The Sort Table dialog displays current sort options in a sort list.

3. Click Add to add a new row to the sort list and specify sorting options as required.

   Hint: You can also:

   • remove a sort item by selecting an item in the sort list and clicking Delete

   • rearrange the precedence of sort items by selecting an item in the sort list and clicking either Move Up or Move Down

4. Click OK to save the details and close the Sort dialog.

   Discoverer refreshes the worksheet according to the sort options that you select.

How to sort data on a crosstab worksheet

You sort a crosstab when you want to change the default sort order that is automatically applied to crosstab worksheets (for more information, see "About sorting on crosstab worksheets").

To sort data on a crosstab worksheet:

1. Display the worksheet that you want to sort.

2. Choose Tools | Sort to display the "Sort Crosstab dialog".

   
   
   Text description of the illustration sct.gif

3. Select the item that you want to sort from the Item to Sort list.

   The Sort Details for: <worksheet item> table below shows which data point items are used to sort the selected item.

4. Click Add to add a new row to the Sort Details for table and specify sorting options as required.

   Hint: You can also:

   • remove a sort item by selecting an item in the Sort Details for table and clicking Delete

   • rearrange the precedence of sort items by selecting an item in the Sort Details for table and clicking either Move Up or Move Down

5. Click OK to save the details and close the Sort dialog.

   Discoverer refreshes the worksheet according to the sort options that you select.

How to change how worksheet data is sorted

You can change the sort order of worksheet data at any time. For example, to reverse the sort order, hide the sorted column, or change a sort into a group sort.

To edit a sort:

1. Display the worksheet that you want to sort.

2. Choose Tools | Sort to display the Sort dialog (see "Edit Worksheet dialog: Sort tab" or "Sort Crosstab dialog".

   The Sort dialog displays current sort options in a sort list.

3. Edit the sort list as required.

4. Click OK to save the details and close the Sort dialog.

   Discoverer refreshes the worksheet according to the sort options that you select.

How to remove sorting from a worksheet

When you no longer want to sort a worksheet, you can remove the sort(s) from the worksheet. For example, you might have created a temporary sort to produce an ad hoc report that you now want to remove.

Table worksheets and crosstab worksheets behave differently, as follows:

• On table worksheets, you can remove all sorts to display data in the order in which it is stored in the database.

• On crosstab worksheets, you can remove additional sorts that you have added to the worksheet but you cannot remove the original default sort.

Note: Crosstab worksheets are group sorted automatically (for more information, see "About sorting on crosstab worksheets").

To delete a sort:

1. Display the sorted worksheet that you want to edit.

2. Choose Tools | Sort to display the Sort dialog (see "Edit Worksheet dialog: Sort tab" or "Sort Crosstab dialog".

   The Sort dialog displays current sort options in a sort list.

3. Select the item that you want to remove.

4. Click Delete to remove it from the sort list.

   Note: On crosstab worksheets, you cannot remove the original default sort (see "About sorting on crosstab worksheets").

5. Click OK to save the details and close the Sort dialog.

   Discoverer refreshes the worksheet according to the sort options that you select.

Examples of sorting

Example 1: This example shows how to use the Sort Crosstab dialog to sort a crosstab worksheet horizontally. In this example, you want to sort data on Profit SUM down the column for the 1999 year. In the figure below, Region (on the left axis) is selected in the Item to Sort list. Profit SUM is selected in the Data Point list and 1999 is selected in the Column list.

Figure 13-5 A crosstab worksheet sorted vertically

Text description of the illustration sort_7.gif

Figure 13-6 A crosstab worksheet sorted horizontally

Example 2: This example shows how to use the Sort Crosstab dialog to sort a crosstab worksheet horizontally. In this example, you want to sort data on Profit SUM along the row for the Central region. In the figure below, Calendar Year (on the top axis) is selected in the Item to Sort list. Profit SUM is selected in the Data Point list and Central is selected in the Row list.

Text description of the illustration sortex3.gif

Example 3: This example shows how to use the Sort dialog to sort on one item within another item. In the figure below, you want to sort on City within Region. To specify Region as the primary sort, you place Region in the Sort by row of the sort table. To specify City as the secondary sort, you place City in the Then by row of the sort table. You can add further levels of sorting as required.

Figure 13-7 A table worksheet with two levels of sorting

Text description of the illustration sortex1.gif