Options dialog: Aggregation tab

Use this tab to control how Discoverer populates cells for aggregated values (sometimes referred to as roll-up values). For more information about aggregated values in Discoverer, see "What are aggregated values in Discoverer?".

In most cases, Discoverer calculates aggregated values by simply adding up a series of data points. In some cases however, Discoverer cannot compute aggregated values in this way and requires a more complex calculation to obtain the correct results. For example, if you are using an Oracle8i database, Discoverer cannot compute aggregated values for certain specific non-linear calculations, or worksheet items based on the following SQL functions:

• a CASE SQL statement

• a DECODE SQL statement

• a PL/SQL function

• a DISTINCT SQL statement

• an analytic function

Note: For more information about linear and non-linear calculations in Discoverer, see "What are linear and non-linear calculations?".

In these cases, you might want to use the aggregation options on this tab (for more information about aggregation options, see "Aggregation options") to control how the aggregated values are computed, as follows:

• If you are using an Oracle9i database, Discoverer can compute a total for non-linear calculations. Therefore you typically select the Show the aggregated value calculated by the database. The database uses the same aggregation method as Discoverer option. For an example, see "Example 1: Example of a Rank calculation using an Oracle9i database".

  In the unlikely event that you want Discoverer to compute an aggregated value by simply add up a series of data points, select the Show the sum of the values displayed in the contributing cells option.

• If you using an Oracle8i database, Discoverer cannot compute a total for certain specific non-linear calculations. Therefore, do one of the following:
  • select the Show <Non-aggregable label>, the "values that cannot be aggregated" option, set on the Sheet Format tab option if you want to display a non-aggregable label (e.g. N.A). For an example, see "Example 2: Example of a weighted margin calculation using an Oracle8i database".

  • select the Show the sum of the values displayed in the contributing cells option if you want Discoverer to compute an aggregated value by simply adding up a series of data points. For an example, see "Example 3: Example of a weighted margin calculation using an Oracle8i database".

Notes

• To find out which database version you are using, choose Help | About Discoverer to display the About Oracle Discoverer page, and look at the Enterprise Edition Release number.

• For an example of a Discoverer worksheet that includes repeated values, see "Example 4: Example showing how Discoverer does not aggregate repeated values using an Oracle9i database".

For more information, see:

"Field level descriptions"

"What are linear and non-linear calculations?"

"Examples of worksheet aggregation in Discoverer"

"Notes on sharing worksheets between Discoverer Desktop and Discoverer Plus/Viewer users"

Field level descriptions

Use these options to specify how you want Discoverer to populate cells for aggregated values. If you clear the Use the aggregation behavior selected by the Discoverer manager check box, the radio button options beneath become active.

Use the aggregation behavior selected by the Discoverer manager

Use this check box to control whether you specify how Discoverer calculates aggregated values, or whether you accept the default Discoverer behavior specified for you by the Discoverer manager, as follows:

• Select this check box if you want the Discoverer manager to specify how Discoverer calculates aggregated values. The radio button options show which aggregation behavior the Discoverer manager has specified for you.

  Note: This is the recommended option. To avoid getting non-meaningful results, do not clear this check box unless you are advised to do so by the Discoverer manager.

• Clear this check box if you want to specify how Discoverer calculates aggregated values by selecting one of the radio button options. If you clear this check box, make sure that you get the worksheet results that you expect if you select a different aggregation option to the default aggregation option.

Hint: For more information about the Discoverer manager, see "Who is the Discoverer manager and what do they do?".

Aggregation options

If you clear the Use the aggregation behavior selected by the Discoverer manager check box, use these options to manually control how Discoverer populates cells for aggregated values.

• (Oracle9i specific) Show the aggregated value calculated by the database. The database uses the same aggregation method as Discoverer

  Use this radio button if you are using an Oracle9i database and you want Discoverer to compute an aggregated value by adding up data points and applying the calculation to the result. For an example, see "Example 1: Example of a Rank calculation using an Oracle9i database".

  Note: This field is only displayed when you are using an Oracle9i database.

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

  Use this radio button if you have an Oracle8i database and you want Discoverer to display the non-aggregable label (e.g. N.A.) for certain specific non-linear calculations that cannot be aggregated. For an example, see "Example 2: Example of a weighted margin calculation using an Oracle8i database".

  The non-aggregable label is the display text that you specify on the "Options dialog: Sheet Format tab (on a crosstab worksheet)".

  Note: You typically will not use this option if you are using an Oracle9i database.

• Show the sum of the values displayed in the contributing cells

  Use this radio button when you want Discoverer to compute an aggregated value by simply adding up a series of data points. For an example, see "Example 3: Example of a weighted margin calculation using an Oracle8i database".

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 (i.e. displayed as Sum: $877,594) that aggregates the Sales Sum values for regions to create a yearly total.

  
  
  Text description of the illustration agg8.gif

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

  
  
  Text description of the illustration agg10.gif

  Note: Discoverer calculates aggregate values on a crosstab worksheet if the worksheet uses the Outline style (i.e. if you select the Outline radio button from the Style options on the "Options dialog: Sheet Format tab (on a crosstab worksheet)".

What are linear and non-linear calculations?

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



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



Text description of the illustration agg9.gif

Examples of worksheet aggregation in Discoverer

The following examples show how the aggregation options 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 calculated by the database. The database uses the same aggregation method as Discoverer option on the "Options dialog: Aggregation tab".

Text description of the illustration agg1.gif

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

Check box selected	What value is displayed
(Oracle9i database only) 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 of a weighted margin calculation using an Oracle8i database

In this example (using an Oracle8i database), a worksheet displays profits and sales data for cities in regions. You want to calculate a weighted margin increase of 10% for Chicago and no margin increase for other cities. You create a Discoverer calculation called 'Weighted Profits/Sales' with the following formula:

CASE WHEN Video Analysis Information.City = 'Chicago' THEN Profit SUM/Sales SUM*1.1 ELSE Profit SUM/Sales SUM END

With an Oracle8i database, Discoverer cannot compute aggregated values for certain specific non-linear calculations, or worksheet items based on CASE SQL statements. Therefore, you cannot display the 'Weighted Profits/Sales' aggregated value as 0.66 (i.e. $112, 538/$171,028). In this scenario, you can either display the non-aggregable label (e.g. N.A.) or compute an aggregated value by simply adding up the 'Weighted Profits/Sales' values to get 4.86. In this example you want to display the non-aggregable label (e.g. N.A.).

The worksheet below shows how Discoverer displays the 'Weighted Profits/Sales' aggregated value as the non-aggregable label 'N.A' if you select the Show <Non-aggregable label>, the "values that cannot be aggregated" option, set on the Sheet Format tab option on the "Options dialog: Aggregation tab".

Text description of the illustration agg2.gif

The table below shows how the 'Weighted Profits/Sales' aggregated value is calculated for the different radio button options on the "Options dialog: Aggregation tab".

Check box selected	What value is displayed
Show <Non-aggregable label>, the "values that cannot be aggregated" option, set on the Sheet Format tab	<N.A> (as in example above)
Show the sum of the values displayed in the contributing cells	4.86 (a simple addition of the Weighted Profits/Sales values)

Example 3: Example of a weighted margin calculation using an Oracle8i database

In this example (using an Oracle8i database), a worksheet displays profits and sales data for cities in regions. You want to calculate a weighted margin increase of 10% for Chicago and no margin increase for other cities. You create a Discoverer calculation called 'Weighted Profits/Sales' with the following formula:

CASE WHEN Video Analysis Information.City = 'Chicago' THEN Profit SUM/Sales SUM*1.1 ELSE Profit SUM/Sales SUM END

With an Oracle8i database, Discoverer cannot compute aggregated values for certain specific non-linear calculations, or worksheet items based on CASE SQL statements. Therefore, you cannot display the 'Weighted Profits/Sales' aggregated value as 0.66 (i.e. $112, 538/$171,028). In this scenario, you can either display the non-aggregable label (e.g. N.A.) or compute an aggregated value by simply adding up the 'Weighted Profits/Sales' values to get 4.86. In this example you want to compute an aggregated value by simply adding up the 'Weighted Profits/Sales' values to get 4.86.

The worksheet below shows how Discoverer calculates the 'Weighted Profits/Sales' aggregated value as 4.86 if you select the Show the sum of the values displayed in the contributing cells option on the "Options dialog: Aggregation tab".

Text description of the illustration agg3.gif

The table below shows how the 'Weighted Profits/Sales' aggregated value is calculated for the different radio button options on the "Options dialog: Aggregation tab".

Check box selected	What value is displayed
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	4.86 (as in example above)

Example 4: 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 "Options 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).

Text 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 "Options dialog: Aggregation tab").

Text 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 "Options dialog: Aggregation tab".

Check box selected	What value is displayed
(Oracle9i database only) 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.>

Notes on sharing worksheets between Discoverer Desktop and Discoverer Plus/Viewer users

This section explains how Discoverer computes aggregated values if you share Discoverer worksheets with other Discoverer Desktop or Discoverer Plus/Viewer users.

Note: If a potential fan trap situation exists, make sure that Discoverer users specify the same fan trap settings (for more information, see "About fan traps").

About sharing a Discoverer worksheet between Discoverer Plus users

To make sure that a Discoverer worksheet contain the same aggregated values regardless of which Discoverer Plus user opens it, do one or both of the following:

• explicitly set the aggregation option on the Aggregation tab (i.e. clear the Use the aggregation behavior selected by the Discoverer manager check box and select one of the radio button options)

• make sure that the Discoverer manager has specified the same default Aggregation option (i.e. the EnhancedAggregationStrategy preference) on all Discoverer middle-tier machines being used

About sharing a Discoverer worksheet between Discoverer Plus and Discoverer Desktop users

If you want to share a Discoverer worksheet between Discoverer Plus and Discoverer Desktop, note the following:

• if a worksheet created in Discoverer Plus against an Oracle8i database is opened in Discoverer Desktop, Discoverer Desktop displays the same aggregated values

• if a worksheet created in Discoverer Plus (and the Show the aggregated value calculated by the database. The database uses the same aggregation method as Discoverer option is selected) against an Oracle9i database is opened in Discoverer Desktop, Discoverer Desktop displays the non-aggregable label by default

• if a worksheet created in Discoverer Desktop is opened in Discoverer Plus, Discoverer Plus uses the same aggregation behavior as specified in Discoverer Desktop