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?"

• "About totals on 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 Oracle BI Discoverer"

• "What are aggregated values in Discoverer"

• "What are linear and non-linear totals"

• "How to display or hide totals"

• "How to create totals"

• "How to edit totals"

• "How to delete totals"

• "Examples of totals"

• "Examples of worksheet aggregation in Discoverer"

14.1 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

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:

• 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)

When a worksheet contains totals, you can:

• display the totals (or turn the totals on)

• hide the totals (or turn the totals off)

14.2 About totals on 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 on the right hand side of a row.

14.3 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

14.4 When to use SUM instead of Cell SUM

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

• analytic functions (for example, Rank and NTILE)

• aggregated (sum total) items (for example, AVG and VARIANCE)

14.4.1 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

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 (that is, 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 (that is, 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 (that is, 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 (that is, 10,000 + 20,000 + 6,666).

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

14.5.1 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 (that is, an increase of ten units).

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 (that is, 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).

14.6 About migrating workbook totals to Oracle BI Discoverer

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

• check that the total values are consistent with how total values were calculated in the earlier version of Discoverer

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

14.7 What are aggregated values in Discoverer

Aggregated values in Discoverer are:

• values that Discoverer calculates when you add a worksheet total to a worksheet

  For example, the table worksheet below contains a worksheet total (that is, displayed as Sum: $877,594) that aggregates the Sales Sum values for regions to create a yearly total.

  
  

  For more information about worksheet totals, see "What are totals?".

• outline values that Discoverer calculates for you on a crosstab worksheet (if the worksheet style is set to outline)

  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 (that is, $49,246, $77,668).

  
  

  Note: Discoverer calculates aggregate values on a crosstab worksheet if the worksheet uses the Outline style (that is, if you select the Outline option in the Crosstab style drop down list on the "Options dialog: Sheet tab").

14.8 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 (that is, $49,246, $77,668).

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 (that is, 49,246/77,668), not as 1.322 (that is, 0.708 + 0.614).

14.9 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 you want to analyze.

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

   
   

   The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active totals are marked with a checkmark 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

• You can also display existing totals in the following way:

  • If the Available Items pane is displayed, drag and drop a total from the Calculations tab to the worksheet.

• You can also hide totals in the following way:

  • If the Selected Items pane is displayed, right-click on a total in the Selected Items list and select Remove from Worksheet.

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

14.10 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 you want to analyze.

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

   
   

   The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active totals are marked with a checkmark 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".

   
   

4. Under Which data point would you like to create a total on?, select the item 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 option 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 option, 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 displays 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

• You can also create a total in the following ways:

  • Select the worksheet item for which you want to create a total, then select the New Total option on the Standard toolbar and choose one of the total types available.

  • If the Available Items pane is displayed, select the New Total option on the Available Items toolbar (for more information, see "Available Items pane").

  • If the Available Items pane is displayed, display the Calculations tab, right-click on My Totals, and select New Total.

14.11 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 you want to analyze.

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

   
   

   The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active totals are marked with a checkmark 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

• You can also edit totals in the following way:

  • If the Available Items pane is displayed, display the Calculations tab, right-click on a total, and select Edit.

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

14.12 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: 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 you want to analyze.

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

   
   

   The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active totals are marked with a checkmark 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

• You can also delete totals in the following way:

  • If the Available Items pane is displayed, display the Calculations tab, right-click on a total, and select Delete.

14.13 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

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

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

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.

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

14.14.1 Example 1: Example of a Rank calculation using an Oracle9i or later database

In this example (using an Oracle9i or later 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 (for example, 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 (for example, 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".

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.

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

This example (using an Oracle9i or later 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 (that is, the Sales SUM item) for regions for each year. The worksheet also displays the target sales value set by the company (that is, 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 (that is, in the Sales Facts table) but the Target Sales Sum item is dimensioned by date (that is, in the Date table).

Therefore, you want Discoverer to display a non-aggregable label (for example, N.A.) for the yearly total values for the Target Sales Sum item. The worksheet below shows how Discoverer displays a non-aggregable label (that is, 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".

The table below shows how Discoverer the Target Sales Sum aggregates are calculated for the different 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.