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

• You can also create a new calculation in the following ways:

  • If the Available Items pane is displayed, select the New Calculation 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 Calculations, and select New Calculation.

• For more information about analytic functions, see "What are analytic functions?" and "Examples of using row-based and time-based intervals".

• If you want to use parameter values in analytic functions to collect dynamic input, you must manually prefix the item name with a ':' (i.e. colon) character in the Calculation field (for more information, see "About using parameters to collect dynamic user input").

  For example, you might create a Band by rank formula based on the Profit SUM item (e.g. NTILE(4) OVER (ORDER BY Profit SUM DESC). If you want end users to select the number of bands at runtime, you might create a worksheet parameter called Band number. To use the Band number in the Band by rank formula you must manually change the formula to:

  NTILE(:Band number) OVER (ORDER BY Profit SUM DESC)

• Analytic functions follow the standard Oracle function syntax. For a full description of this syntax, see the Oracle9i SQL Reference Manual.