Creating Group Sorts and Applying Reporting Functions

...

Ownership

The Director of the Business Support Center is responsible for ensuring that this document is necessary, reflects actual practice, and supports University policy.

Section Objectives

At the end of this section, you should be able to:

Sort information
Add group sorts to a query
Apply reporting functions to items

Sorted Output

Sorting Output

To sort a column from the toolbar, follow these steps:

1. Select a column to sort on.

2. Click the Sort icon on the toolbar.

Note: You can also sort a column by selecting Tools—>Sort from the menu bar.

Group Sort Output You can apply group sort to a sort column. Group sort splits the output information into groups. The output is easier to read if you group the information according to the values in one or more items. When an item is used to break output, duplicates are suppressed so that the item appears only once in the output. By default, group sort output is sorted by the values in the group sort item.

Creating Group Sort Output

Several group functions and two sort directions are available. Which group function you use depends on how you want the data to be displayed in each group. You can use each type of group function on number, character, and date columns. Page items must be moved to the column heading position in order to be available in the Sort Table list. To add a new item (column) to the sort table, click the Add button. The sort table options are as follows:

Column: Select the name of the item to which the group sort is to be applied.
Direction: Specify whether to sort the data from high to low or low to high by clicking an arrow in the Direction field.
Group: Specify the type of break: None, Group Sort, Page Break, or Hidden.
Line: Specify the width of the break line by entering a positive integer in this cell. A higher number results in a thicker line.
Space: This option indicates the number of rows below the break line before the value in the group sort item changes.

Sorting More Than One Column: Drag and drop a sort column or click the Move Up or Move Down button to reposition a sort column.

Creating Totals

Totals are the result of applying a function, such as the average or sum, to all the values in a particular item. In tabular output, totals appear below the values in the output item. In crosstab output, totals can be displayed below columns and to the right of rows.

When the value in a group sort item changes, any reporting functions applied to other items are calculated and produce a subtotal. A grand total for each reporting function is displayed at the end of the output.

The Totals Dialog Box

Sums of rows and columns of numbers, averages of values, standard deviations, subtotals, grand totals, and so on are all standard elements of tables and of crosstabs of numeric data. To create a total or subtotal, open the Total dialog box or use the toolbar.

To open the Totals dialog box, do the following:

1. Select Tools—>Totals.

2. Click New in the Totals dialog box. The Total dialog box is displayed.

The following pages describe the steps you take in the Total dialog box.

Creating Totals in the Total Dialog Box

To calculate a sum such as a grand total and place it at the bottom of the page, follow these steps:

1. Select Sum from the Calculate drop-down list.

Note: Sum is the default function.

2. Select the appropriate item for the calculation from the drop-down list.

3. Select the “Grand total at bottom” option.

4. Select the label that appears in the worksheet. You can create the label with text and parameters from the Label drop-down list. The parameters are the values or items used in the calculation, and they appear as &Value, &Item, and &Data in the Label drop-down list. In the worksheet, they represent the column value, item, and data point, respectively. (Data point refers to the selected columns for which the aggregate is calculated.)

5. Click the Format button and select a format from the dialog box that appears.

Reporting Functions

*Function*	*Data Type*	*Column Application*
Average	N	Arithmetic average of all values
Count	N, C, D	Number of values
Count Distinct	N, C, D	Number of unique values
Maximum	N, C, D	Maximum value
Minimum	N, C, D	Minimum value
Standard Deviation	N	Arithmetic standard deviation
Sum	N	Total of all values
Variance	N	Arithmetic variance
Percentage of Grand	N	Arithmetic percentage of a value

Several predefined totals are available in the Total dialog box. The functions in the table are predefined totals; some of them are also available as toolbar buttons on the Analysis toolbar. For text and date items, only the Count, Count Distinct, Minimum, and Maximum functions are available.

Editing Totals

Click the total (or subtotal row) with the right mouse button; a drop-down list appears displaying two options:

Copy: You can copy selected numbers to another document.
Edit Total: You can edit total or subtotal items.

Creating Subtotals

Creating Subtotals in the Total Dialog Box

To create a subtotal such as a sum, follow these steps:

1. Select Sum from the Calculate drop-down list.

2. Select the appropriate items for the calculation from the drop-down list.

3. Select the “Subtotal at each change in” option.

4. Select the group item from the “Subtotal at each change in” drop-down list.

5. You can specify not to display the subtotal for a single row (optional). Select the label that appears in the worksheet.

6. Select [Format] and select a format from the dialog box that appears.

Summary

In this lesson, you should have learned how to:

Sort information
Add group sorts to a query
Apply reporting functions to items
Effective: 02/03/06 njk

Revision: 3

Copyright 2004 University of Virginia and Oracle Corporation. All rights reserved.

Oracle® Tutor Copyright © 1997, 2004, Oracle. All rights reserved.