11
Using totals

This chapter explains how to use Discoverer totals to answer typical business questions. For example, what is the total sales figure for January? This section contains the following topics:

See also:

• "What are totals?"

• "About totals on crosstab worksheets"

• "About totals in worksheets"

• "About SUM and Cell SUM"

• "When to use SUM instead of Cell SUM"

• "When to use Cell SUM instead of SUM"

• "About migrating workbook totals to Oracle9iAS Discoverer"

• "How to display or hide totals"

• "How to create totals"

• "How to edit totals"

• "How to delete totals"

• "Examples of totals"

What are totals?

Totals are worksheet items that enable you to quickly and easily summarize rows and columns. For example, to calculate the sum of a column of profit figures, or calculate the average of a row of sales figures. You can then use the totals to analyze the worksheet data.

Figure 11-1 A Discoverer worksheet with totals

Text description of the illustration tot_exp.gif

Key to figure:

1. Sub totals defined on Profit SUM for each region

2. A grand total defined on Profit SUM for all regions

You use Discoverer totals to calculate:

• the result of applying a calculation to totals (the SUM - for more information, see "When to use SUM instead of Cell SUM")

• the result of adding values (the Cell SUM - for more information, see "When to use Cell SUM instead of SUM")

• the number of values (the Count)

• the lowest of the values (the Minimum)

• the highest of the values (the Maximum)

• the square root of the variance (the Standard Deviation)

• the amount of variance in a set of values (the Variance)

About totals on crosstab worksheets

When creating totals, note that table worksheets and crosstab worksheets have the following differences:

• On table worksheets you apply grand totals to columns. Here, you position totals at the bottom of a column.

• On crosstab worksheets you can apply grand totals to either columns or rows. Here, you position totals either at the bottom of a column or to the right hand side of a row.

About totals in worksheets

When a worksheet contains totals, you can:

• display the totals (or turn the totals on)

• hide the totals (or turn the totals off)

About SUM and Cell SUM

When you create totals in Discoverer, you can select one of two functions to calculate the sum of a column or row that contains a calculation:

• SUM (Discoverer default) - use this to apply the calculation to the total

• Cell SUM - use this to apply the calculation to individual values, then add the calculated values. In other words, you simply add up values in the column or row

When to use SUM instead of Cell SUM

You typically use SUM rather than Cell SUM when you add items containing:

• analytic functions (e.g. Rank and NTILE)

• aggregated (sum total) items (e.g. AVG and VARIANCE)

Example - using SUM to calculate the average sales per employee

In this example, you use SUM to calculate an overall average sales figure per employee by region.

Figure 11-2 Using SUM to calculate the average sales per employee

Text description of the illustration total_ta.gif

Key to figure:

1. The calculation item Avg sales per emp, contains the calculation Sales SUM/No. of employees. For example, the value for the East region is 20,000 (i.e. 200,000/10).

2. In the Sales SUM and No. of employees columns, the Totals values contain the sums of the two columns.

3. In the column Avg sales per emp, the Totals value is calculated as 11,428 (i.e. 400,000/35).

In the figure above, the worksheet contains four items, including the calculation item Avg sales per emp. When you calculate the total for the Avg sales per emp item, you want to apply the calculation to the totals for the Sales SUM and No. of employees items. In other words, the intended total value for the Avg sales per emp item is 11,428 (i.e. 400,000/35).

Note: If you used Cell SUM in this example, you would sum the Avg sales per emp item column. This would result in the unintended total value 36,666 (i.e. 10,000 + 20,000 + 6,666).

When to use Cell SUM instead of SUM

You typically use Cell SUM rather than SUM when you simply want to add a row or column of values.

Example - using Cell SUM to calculate an increase in sales

In this example, you use Cell SUM to calculate an overall total sales target for individual sales targets (i.e. an increase of ten units).

Figure 11-3 Using Cell SUM to calculate an increase in sales

Text description of the illustration total_ty.gif

Key to figure:

1. The calculation item Sales Target, contains the calculation Sales + 10. For example, the value for the North region is 210 (i.e. 200 + 10).

2. In the Sales column, the Totals value is the sum of the Sales column.

3. In the Sales Target column, the Totals value is the sum of the Sales Target column 730 (210 + 310 + 210).

In the figure above, the worksheet contains three items, including the calculation item Sales Target. When you calculate a total for the Sales Target item, you want to sum the values in the column. In other words, the intended total value for the Sales Target item is 730 (210+310+210).

Note: If you used SUM in this example, you would apply the calculation to the total for the Sales column. This would result in the unintended total value 710 (700+10).

About migrating workbook totals to Oracle9iAS Discoverer

If you migrate workbooks containing totals from Discoverer 4i to Oracle9iAS Discoverer, you might want to:

• check that the total values are consistent with how total values were calculated in Discoverer 4i

• where necessary, change totals in workbooks from SUM to Cell SUM or from Cell SUM to SUM

How to display or hide totals

If a worksheet contains totals, you can display or hide the totals, as follows:

• You display totals on a worksheet when you want to use them to analyze worksheet data.

• You hide totals on a worksheet when you do not need to use them to analyze worksheet data.

To display or hide totals:

1. Display the worksheet that you want to analyze.

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

   
   
   Text description of the illustration total_1.gif

   The "Edit Worksheet dialog: Totals tab" lists totals available to the worksheet. The check box beside each item indicates whether it is displayed on the worksheet. Only selected totals are currently displayed on the worksheet.

3. Use the View Totals for drop down list to change which totals you display in the list below.

   For example, choose Active Only to display totals currently displayed on the worksheet.

4. To display or hide totals:
   • Select the check box next to each total that you want to display.

   • Clear the check box next to each total that you want to hide.

   Hint: To help you decide which totals to display, use the Description field. The Description field shows any additional information that exists about the currently highlighted total.

5. Click OK to close the Totals tab and display the worksheet.

Discoverer refreshes the worksheet according to the options selected.

Notes

• To remove a total from the worksheet permanently, you delete the total (for more information, see "How to delete totals").

How to create totals

You create totals to analyze a worksheet in a new way. For example, to calculate a sum for a list of sales figures, or to find the average of a list of profit figures.

To create a total on a table worksheet or crosstab worksheet:

1. Choose Tools | Totals to display the "Edit Worksheet dialog: Totals tab".

2. Click the New button to display the New Totals dialog (see "New Total dialog (on a table worksheet)" or "New Total dialog (on a crosstab worksheet)").

   
   
   Text description of the illustration total_3.gif
   

3. Under Which data point would you like to create a total on?, select the item that you want to summarize from the drop down list.

   Note: You can also create totals for all numeric items on the worksheet by selecting All Data Points from the drop-down list.

4. Under What kind of total do you want?, select a total type from the drop down list.

   For example, choose Sum to add the values, or choose Average to calculate a mean.

5. Under Where would you like your total to be shown?, choose where you want to display the total.

   For example, select the Grand total at bottom radio button to calculate a grand total for a column and place it after the last row of the table.

   Note: Positioning options are different depending on the type of worksheet, as follows:

   • on table worksheets, you can position the total at the bottom of the worksheet

   • on crosstab worksheets, you can position the total at the bottom of the worksheet or to the right of a worksheet

6. If you select the Subtotal at each change in radio button, select the item on which to group the data from the drop down list.

   For example, if you sort the data by region you might want to see profits by region. If so, select region as the data item and Discoverer will display the total profit for each region on a separate line.

7. Under Which page items do you want to include?, choose whether to summarize data for the currently displayed page item or all page items on the worksheet.

   Note: The Which page items do you want to include? options are greyed out when no page items are available on the worksheet.

8. Under What label do you want to be shown?, do one of the following:
   • type in a label for the total

   • use the drop down list to insert variable values into the label.

   Note: Select the Generate label automatically? check box if you want Discoverer to generate a label for you.

9. Click OK to save the details and close the dialog.

   The new total appears in the Totals dialog and is turned on ready to be applied to the data.

10. Click OK to close the Totals dialog and return to the worksheet.

    Discoverer calculates the total and displays it on the worksheet.

Notes:

• You can change the format of totals on a worksheet using Sheet | Format to display the "Edit Worksheet dialog: Format tab". Then, select the total from the item list and choose Format Heading or Format Data.

How to edit totals

You edit totals when you want to change the way that they behave. For example, to change where a total is displayed on the worksheet.

To edit a total:

1. Choose Tools | Totals to display the "Edit Worksheet dialog: Totals tab".

2. Select the total that you want to edit from the totals list.

3. Click Edit to display the Edit Totals dialog (see "Edit Total dialog (on a table worksheet)" or "Edit Total dialog (on a crosstab worksheet)".

4. Edit the total details as required.

5. Click OK to save the details and close the Edit Total dialog.

6. Click OK to close the "Edit Worksheet dialog: Totals tab" and return to the worksheet.

The total is updated as specified.

Notes:

• You can change the format of totals on a worksheet using Sheet | Format to display the "Edit Worksheet dialog: Format tab". Then, select the total from the item list and choose Format Heading or Format Data.

How to delete totals

You delete totals when you no longer want to use them, and want to remove them permanently from a worksheet. For example, you might have created a temporary total to produce an ad hoc report and now want to remove this total from the worksheet.

Note: If you want to remove the total from the worksheet without deleting it permanently, you can hide the total (see "How to display or hide totals").

To delete a total:

1. Choose Tools | Totals to display the "Edit Worksheet dialog: Totals tab".

2. Select the total that you want to delete from the Totals list.

3. Click Delete.

   The total that you selected is removed from the Totals list.

4. Click OK to close the Totals dialog and return to the worksheet.

Discoverer removes the total that you deleted from the worksheet.

Examples of totals

Example 1: In this example, the worksheet contains profit values for regions. You want to display a sub-total for each region, and a grand total for all regions.

Figure 11-4 Displaying a total on a table worksheet

Text description of the illustration pivot_1d.gif

Key to figure:

1. a sub-total for each region (Total for Central: £94,651)

2. a grand total for all regions (Total for All Values: £320,301)

Example 2: In this example, a crosstab worksheet contains profit values for regions in different years. You want to display a profit total of all three years for each region.

Figure 11-5 Displaying a total on a crosstab worksheet

Text description of the illustration tot_exp1.gif

Key to figure:

1. A total item named 'Sum' on rows, which calculates a total for each Region. For example, the total for the Central region is $234,498.

Example 3: In this example, the worksheet contains profit and sales values for each quarter in the Central region. You want to display a total profit figure and a total sales figure.

Figure 11-6 Displaying two totals on a crosstab worksheet

Text description of the illustration tot_exp2.gif

Key to figure:

1. Two totals are selected for display, as follows:

   - the Grand Total Rows Sum for Profit SUM total adds the Profit SUM column

   - the Grand Total Rows Sum for Sales SUM total adds the Sales SUM column

2. The Grand Total Rows Sum for Profit SUM total on the crosstab worksheet.

3. The Grand Total Rows Sum for Sales SUM total on the crosstab worksheet.

Notice that the two totals are displayed on the same row. When a crosstab has multiple totals displayed, Discoverer automatically puts them on the same row.