Skip Headers
Oracle® Business Intelligence Discoverer Plus User's Guide
10g Release 2 (10.1.2.1)
B13915-04
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

14 Using totals

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

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 to calculate the average of a row of sales figures. You can then use the totals to analyze the worksheet data.

Figure 14-1 A Discoverer worksheet with totals

Description of Figure 14-1  follows
Description of "Figure 14-1 A Discoverer worksheet with totals"


Key to figure:
a. Sub-totals defined on Profit SUM for each region.
b. A grand total defined on Profit SUM for all regions.

You use Discoverer totals to calculate:

When a worksheet contains totals, you can:

About totals on worksheets

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

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:

When to use SUM instead of Cell SUM

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

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 14-2 Using SUM to calculate the average sales per employee

Description of Figure 14-2  follows
Description of "Figure 14-2 Using SUM to calculate the average sales per employee"


Key to figure:
a. 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).
b. In the Sales SUM and No. of employees columns, the Totals values contain the sums of the two columns.
c. 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 14-3 Using Cell SUM to calculate an increase in sales

Description of Figure 14-3  follows
Description of "Figure 14-3 Using Cell SUM to calculate an increase in sales"


Key to figure:
a. The calculation item Sales Target contains the calculation Sales + 10. For example, the value for the North region is 210 (i.e. 200 + 10).
b. In the Sales column, the Totals value is the sum of the Sales column.
c. 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 OracleBI Discoverer

If you migrate workbooks containing totals from earlier versions of Discoverer to OracleBI Discoverer, you might want to:

What are aggregated values in Discoverer

Aggregated values in Discoverer are:

What are linear and non-linear totals

Linear calculations are worksheet calculations that Discoverer aggregates by simply adding up a series of data points. For example, in the crosstab worksheet below Discoverer adds up the Profit Sum and Sales Sum for Chicago and Louisville to create aggregated values for the Central region (i.e. $49,246, $77,668).

Description of agg10.gif follows
Description of the illustration agg10.gif

Non-linear calculations are worksheet calculations that Discoverer aggregates by adding up data points and applying the calculation to the result. For example, in the crosstab worksheet below Discoverer calculates the aggregated value for the Sales Margin item by applying the calculation 'Profit Sum/Sales SUM' to the aggregated values for Profit Sum and Sales Sum. In other words Discoverer calculates the aggregated value for the Sales Margin item for the Central region as 0.634 (i.e. 49,246/77,668), not as 1.322 (i.e. 0.708 + 0.614).

Description of agg9.gif follows
Description of the illustration agg9.gif

How to display or hide totals

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

To display or hide totals:

  1. Display the worksheet that you want to analyze.

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

    Description of tot4.gif follows
    Description of the illustration tot4.gif

    The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active totals are marked with a tick symbol (or check mark) in the Available list and are also displayed in the Selected list.

  3. To display an existing total, move the total from the Available list to the Selected list.

  4. To hide an existing total, move the total from the Selected list to the Available list.

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

    Discoverer refreshes the worksheet.

Notes

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. Display the worksheet that you want to analyze.

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

    Description of tot4.gif follows
    Description of the illustration tot4.gif

    The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active totals are marked with a tick symbol (or check mark) in the Available list and are also displayed in the Selected list.

  3. Click New and select New Total from the drop down list to display the "New Total dialog".

    Description of total_3.gif follows
    Description of the illustration total_3.gif

  4. 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.

  5. 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.

  6. 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 on the right of the worksheet

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

  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.

  10. Click OK to close the Calculations tab and return to the worksheet.

    Discoverer calculates the total and displays it on the worksheet.

Notes

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. Display the worksheet that you want to analyze.

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

    Description of tot4.gif follows
    Description of the illustration tot4.gif

    The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active totals are marked with a tick symbol (or check mark) in the Available list and are also displayed in the Selected list.

  3. Select a total in the Available list.

  4. Click Edit to display the "Edit Total dialog".

  5. Edit the total details as required.

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

  7. Click OK to close the Calculations tab and return to the worksheet.

Discoverer refreshes the worksheet.

Notes

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. Display the worksheet that you want to analyze.

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

    Description of tot4.gif follows
    Description of the illustration tot4.gif

    The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active totals are marked with a tick symbol (or check mark) in the Available list and are also displayed in the Selected list.

  3. Select a total in the Available list.

  4. Click Delete.

  5. Click OK to close the Calculations tab and return to the worksheet.

Discoverer refreshes the worksheet.

Notes

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 14-4 Displaying a total on a table worksheet

Description of Figure 14-4  follows
Description of "Figure 14-4 Displaying a total on a table worksheet"


Key to figure:
a. A sub-total for each region (Total for Central: £94,651).
b. 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 14-5 Displaying a total on a crosstab worksheet

Description of Figure 14-5  follows
Description of "Figure 14-5 Displaying a total on a crosstab worksheet"


Key to figure:
a. 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 14-6 Displaying two totals on a crosstab worksheet

Description of Figure 14-6  follows
Description of "Figure 14-6 Displaying two totals on a crosstab worksheet"


Key to figure:
a. 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.
b. The Grand Total Rows Sum for Profit SUM total on the crosstab worksheet.
c. 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.

Examples of worksheet aggregation in Discoverer

The following examples show how aggregation options specified on the "Worksheet Properties dialog: Aggregation tab" affect how Discoverer displays aggregated values.

Example 1: Example of a Rank calculation using an Oracle9i database

In this example (using an Oracle9i database), you want to calculate a ranked list of cities based on profits. You want the highest profits to have the highest rank. You create a Discoverer calculation called 'Rank' with the following formula:

RANK() OVER(PARTITION BY "Calendar Year" ORDER BY "Profit SUM" DESC)

You want Discoverer to calculate the 'Rank' aggregated value as follows:

  • rank regions against each other (e.g. the East region is ranked 1 with profits of $180,283 and the Central region is ranked 2 with profits of $112,538)

  • rank cities against each other (e.g. New York is ranked 1 with profits of $71,507, and Cincinnati is ranked 2 with profits of $34,406)

The worksheet below shows how Discoverer calculates the ranks if you select the Show the aggregated value computed by the database. The database uses the same aggregation method as Discoverer option on the "Worksheet Properties dialog: Aggregation tab".

Description of agg1.gif follows
Description of the illustration agg1.gif

The table below shows how Discoverer calculates the 'Rank' aggregated values for the different options on the "Worksheet Properties dialog: Aggregation tab".

Table 14-1 Explanation of fields

Check box selected What value is displayed?

Show the aggregated value calculated by the database. The database uses the same aggregation method as Discoverer

Valid ranks for each region and for each city (as in example above)

Show <Non-aggregable label>, the "values that cannot be aggregated" option, set on the Sheet Format tab

<N.A.>

Show the sum of the values displayed in the contributing cells

<N.A.> Note: Discoverer does not linearly aggregate values based on analytic functions.


Example 2: Example showing how Discoverer does not aggregate repeated values using an Oracle9i database

This example (using an Oracle9i database) shows how Discoverer does not aggregate repeated values, whichever aggregation option you choose on the "Worksheet Properties dialog: Aggregation tab".

In this example, a worksheet displays sales values (i.e. the Sales SUM item) for regions for each year. The worksheet also displays the target sales value set by the company (i.e. the Target Sales SUM item) for each region. Each region has the same target sales value. You create a Discoverer total to calculate total values for each year.

It is not meaningful to aggregate Target Sales Sum values at the Year level because there is no logical relationship between the Sales item and the Target Sales item. If you are familiar with entity-relationship diagrams, the figure below shows that this is because the Sales SUM item is dimensioned by store (i.e. in the Sales Facts table) but the Target Sales Sum item is dimensioned by date (i.e. in the Date table).

Description of agg5.gif follows
Description of the illustration agg5.gif

Therefore, you want Discoverer to display a non-aggregable label (e.g. N.A.) for the yearly total values for the Target Sales Sum item. The worksheet below shows how Discoverer displays a non-aggregable label (i.e. N.A.) for the yearly totals for the Target Sales Sum item (regardless of which aggregation option you choose on the "Worksheet Properties dialog: Aggregation tab".

Description of agg4.gif follows
Description of the illustration agg4.gif

The table below shows how Discoverer the Target Sales Sum aggregates are calculated for the different radio button options on the "Worksheet Properties dialog: Aggregation tab".

Table 14-2 Explanation of fields

Check box selected What value is displayed?

Show the aggregated value calculated by the database. The database uses the same aggregation method as Discoverer

N.A.

Show <Non-aggregable label>, the "values that cannot be aggregated" option, set on the Sheet Format tab

N.A.

Show the sum of the values displayed in the contributing cells

N.A.