What are calculations?

Calculations are worksheet items based on formulas or expressions (e.g. mathematical formulas, or text handling functions). You use calculations to provide additional analysis to worksheets. In the figure below, the worksheet contains the calculation 'Profit (Sales-Costs)', which calculates the value of sales (i.e. the Sales SUM item) minus the value of costs (i.e. the Cost SUM item).

Figure 17-1 A Discoverer worksheet containing a calculation (Profit (Sales-Costs))

Description of the illustration calc3.gif

For example:

• to calculate a 25% increase in sales, you might create a calculation item with the following formula:

  Sales SUM * 1.25

• to convert the City item into upper-case letters, you might create a calculation item with the following formula:

  UPPER(City)

• to calculate the ranked list position (i.e. using a Rank function) of values in descending order, you might create a calculation item with the following formula:

  RANK() OVER(ORDER BY Sales SUM DESC)

  Advanced functions such as Rank are known as analytic functions. For more information about analytic functions, see "What are analytic functions?".

  Note: Discoverer provides easy-to-use templates for the most popular analytic functions (for more information, see "What analytic function templates are available in Discoverer?").

When you have defined calculations, you can use them in worksheets just like other worksheet items. For example, you can:

• pivot calculations to the page axis

• include calculations in condition statements to filter worksheet data

• display or hide calculations on worksheets

• reuse calculations within other calculations

Notes

• OracleBI Discoverer supports all functions that are supported by the version of the Oracle database being used. For example, analytic functions are supported by Oracle 8.1.7 (or later) Enterprise Edition databases. In other words, you have access to hundreds of pre-defined functions that you can use to support all of your business intelligence requirements.

• You might want to create a calculation to concatenate two (or more) items. To concatenate items, insert ||CHR(10)|| between items. For example, to create a new worksheet column containing the Calendar Year item and the Department item, create a calculation as follows:

  Calendar Year||CHR(10)||Department

  Worksheets containing this item will display Calendar Year and Department in a single column. For example:

  2002 Sales Department