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
 

17 Using calculations

This chapter explains how to use Discoverer Plus Relational's calculations to answer typical business questions. For example, what are my top three best selling products? This chapter contains the following topics:

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 Figure 17-1  follows
Description of "Figure 17-1 A Discoverer worksheet containing a calculation (Profit (Sales-Costs))"

For example:

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

Notes

About using calculations

Calculations can be created by the Discoverer manager or Discoverer users. When a worksheet contains calculations, you can:

Calculations are displayed as new columns on worksheets. Calculations can be used in other calculations. Discoverer enables you to use a comprehensive range of pre-defined functions for use in worksheet calculations. Discoverer also provides easy-to-use templates for the most popular analytic functions (for more information, see "What analytic function templates are available in Discoverer?" and "Examples of using row-based and time-based intervals").

What are analytic functions?

Analytic functions are advanced mathematical and statistical calculations that you can use to analyze data and make business decisions. For example, these functions can answer questions such as:

Note: Analytic functions are a subset of the SQL functions available in the Oracle database.

In the example below, the Rank Sales item contains an analytic function that calculates the ranked list position of Cities according to sales performance. Using the Rank Sales column, you can see that New York is ranked number 1 with total sales of $85,974.23.

Figure 17-2 Worksheet containing the Rank Sales calculation

Description of Figure 17-2  follows
Description of "Figure 17-2 Worksheet containing the Rank Sales calculation"

You can create analytic functions in the following ways:

What analytic function templates are available in Discoverer?

Discoverer Plus Relational provides easy-to-use templates for the most popular analytic functions. These templates enable you to perform complex business intelligence analysis quickly and easily.

Note: For examples of analytic functions created using templates, see "Examples of using row-based and time-based intervals".

Discoverer provides templates for the following types of analysis:

Notes

How to display or hide worksheet calculations

When a worksheet contains calculations, you can display or hide the calculations. You display calculations on a worksheet when you want to use them to analyze worksheet data. You hide calculations on a worksheet when you do not need to use them to analyze worksheet data. For example, you might display a calculation when you export a worksheet.

To display or hide calculations:

  1. Display the worksheet that you want to analyze.

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

    Description of tot6.gif follows
    Description of the illustration tot6.gif

    The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active calculations 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 calculation, move the calculation from the Available list to the Selected list.

  4. To hide an existing calculation, move the calculation 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 calculations

You create calculations to analyze a worksheet in a new way. For example:

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

To create a calculation:

  1. Display the worksheet that you want to analyze.

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

    Description of tot6.gif follows
    Description of the illustration tot6.gif

    The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active calculations 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 Calculation from the drop down list to display the "New Calculation dialog".

    Description of calc1.gif follows
    Description of the illustration calc1.gif

  4. Enter a name for the calculation in the What do you want to name this calculation? field.

    Hint: Use a short descriptive name, which will be displayed on the worksheet.

  5. Enter the calculation formula in the Calculation field.

    If you are familiar with calculation syntax, you can type the formula in the Calculation field.

    Note: If you type a formula in the Calculation field, you must prefix the formula with an equals sign (i.e. =).

    If you prefer, you can build the calculation in stages using any of the following methods:

    • To add an item from the business area to the calculation, choose Selected Items or Available Items from the Show drop down list, select an item from the item list below, then click Paste to copy the item into the Calculation field.

    • To add a function to the calculation, choose Functions from the Show drop down list, select a function from the list below, then click Paste to copy the function into the Calculation field.

    • To add existing calculations to the calculation, choose Calculations from the Show drop down list, select a calculation from the list below, then click Paste to copy the calculation into the Calculation field.

    • To include a mathematical operator in the calculation, click the appropriate operator button below the Calculation field.

      Hint: Before pasting items in the Calculation field, position the cursor in the Calculation field where you want to insert the item.

    • To use an analytic function template to create the formula, click Insert Formula from Template to display a pop-up list of templates and choose a template (for more information about using analytic function templates, see "How to create a new calculation using an analytic function template").

    Note: Calculations follow the standard Oracle calculation syntax. For a full description of this syntax, see the Oracle9i SQL Language Reference Manual.

  6. Click OK to save the details and close the New Calculation dialog.

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

    Discoverer adds the calculation to the worksheet.

Notes

How to create a new calculation using an analytic function template

Discoverer provides easy-to-use templates for the most popular analytic functions (for more information, see "What analytic function templates are available in Discoverer?"). You use templates to build analytic functions that help you analyze data in powerful ways and make business decisions quickly and easily. For example, you might want to calculate the ranked list position (i.e. rank) of sales outlets based on sales.

You use a template to create an analytic function formula, which is inserted into the definition of a new or existing Discoverer calculation.

To create a new calculation using an analytic function template:

  1. Display the worksheet that you want to analyze.

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

    Description of tot6.gif follows
    Description of the illustration tot6.gif

    The Calculations tab lists existing calculations, percentages, and totals available in the worksheet. Active calculations 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 Calculation from the drop down list to display the "New Calculation dialog".

    Description of calc1.gif follows
    Description of the illustration calc1.gif

  4. Enter a name for the calculation in the What do you want to name this calculation? field.

    Hint: Use a short descriptive name, which will be displayed on the worksheet.

  5. Click Insert Formula from Template to display a pop-up list of pre-defined templates.

    Description of calc5.gif follows
    Description of the illustration calc5.gif

  6. Choose a template from the pop-up list to display a template dialog for the selected analytic function.

  7. Use the template to define the analytic function.

    For example, if you choose the Rank template, you will use the "Rank dialog" to create the formula.

    The underlying SQL statement for the analytic function formula is displayed in the Calculation field at the bottom of the template.

  8. Click OK to save the analytic function and close the analytic function template.

    The SQL statement for the analytic function that you created is transferred to the Calculation field. You might want to modify the SQL statement for the analytic function (e.g. by adding more ORDER BY clauses) or by inserting another function into the Calculation field.

    Description of calc7.gif follows
    Description of the illustration calc7.gif

    The Calculation field displays the underlying SQL statement for the analytic function that you defined.

    Note: You can subsequently modify the calculation (e.g. to add more PARTITION BY clauses) in any of the following ways:

    • by manually editing the formula in the Calculation field

    • by first deleting the formula in the Calculation field, then clicking Insert Formula from Template and recreating the formula

    • by clicking Insert Formula from Template and appending a new formula to the existing formula in the Calculation field

      Note: If you have more than one function in the Calculation field, you must associate the functions (e.g. using + or -).

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

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

    Discoverer adds the calculation to the worksheet.

Notes

How to edit calculations

You edit calculations to change the way that they behave. For example, to change a percentage increase calculation from 25% to 30%.

To edit a calculation:

  1. Display the worksheet that you want to analyze.

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

    Description of tot6.gif follows
    Description of the illustration tot6.gif

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

  3. Select a calculation in the Available list.

  4. Click Edit to display "Edit Calculation dialog".

    Description of calc8.gif follows
    Description of the illustration calc8.gif

  5. Modify the calculation (e.g. to add more PARTITION BY clauses) in any of the following ways:

    • by manually editing the formula in the Calculation field

    • by first deleting the formula in the Calculation field, then clicking Insert Formula from Template and recreating the formula

    • by clicking Insert Formula from Template and appending a new formula to the existing formula in the Calculation field

      Note: If you have more than one function in the Calculation field, you must associate the functions (e.g. using + or -).

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

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

    Discoverer updates the calculation.

Notes

How to delete calculations

You delete a calculation when you no longer need it and want to remove it permanently from a worksheet. For example, you might have created a temporary calculation to answer a question from a colleague. After printing the report, you want to remove the calculation from the worksheet.

To delete a calculation:

  1. Display the worksheet that you want to analyze.

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

    Description of tot6.gif follows
    Description of the illustration tot6.gif

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

  3. Select a calculation in the Available list.

  4. Click Delete.

  5. Click OK to return to the worksheet.

Discoverer removes the calculation that you specified.

Notes

Examples of calculations

For examples of different types of calculation, refer to: