13
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 group sort data"

• "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 the Group Sort option turned off, and the worksheet on the right has the Group Sort option 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.

You can also sort data within groups. The following example shows data that is first sorted by Region, and then sorted within each Region group by Profit Sum from low to high. You can easily see the relative profit standings of each department within each region.

Text description of the illustration sort_4.gif

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 (on a table worksheet)".

   
   
   Text description of the illustration st.gif

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

3. Click Add and select the item that you want to add to the sort list.

   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 "Edit Worksheet dialog: Sort tab (on a crosstab worksheet)".

   
   
   Text description of the illustration sct.gif

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

3. Specify the item on which you want to sort the worksheet:
   • To sort by an item on the top axis, select the Above the data radio button and select the item on which to sort from the Item to sort drop down list.

   • To sort by an item on the left axis, select the Along the left side of the data radio button and select the item on which to sort from the Item to sort drop down list.

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

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

Notes:

• You can also:
  • remove a sort item by selecting an item in the sort list and clicking Delete

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

How to group sort data

You use the group sort facility on table worksheets to arrange data for more effective analysis and remove repeated values to make data more presentable.

Note: You cannot use group sorting on crosstab worksheets.

To group sort data:

1. Open the table worksheet that you want to sort.

2. Choose Tools | Sort to display the "Edit Worksheet dialog: Sort tab (on a table worksheet)".

   
   
   Text description of the illustration st.gif

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

   Hint: If the sort list is empty, click Add then select an item to add it to the sort list.

3. Choose Group Sort from the Group drop down list next to the item on which you want to sort the worksheet.

   Note: Group sorts take precedence over non-group sorts. Therefore, if there is more than one item in the sort list, the item you selected automatically becomes the first item in the sort list.

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

   Discoverer refreshes the worksheet according to the group 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 (on a table worksheet)" or "Edit Worksheet dialog: Sort tab (on a crosstab worksheet)".

   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 (on a table worksheet)" or "Edit Worksheet dialog: Sort tab (on a crosstab worksheet)".

   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. In the figure below, a table worksheet is sorted on Region, which is on the left axis.

Figure 13-5 A table worksheet sorted on Region

Text description of the illustration sort_7.gif

Example 2: 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 list as item 1. To specify City as the secondary sort, you place City in the sort list as item 2. You can add further levels of sorting as required.

Figure 13-6 Sorting data within data

Text description of the illustration sortex1.gif