Use this dialog to change the way that the currently selected calculation item behaves. For example, to changes its name, description, or formula.
For more information, see:
"Oracle analytic function examples"
What do you want to name this calculation?
Use this field to enter a descriptive name for the calculation item. This name is displayed in calculation lists and on worksheets as the column header of the calculation results column. If you leave this box blank, Discoverer creates a default name for you.
Use this drop down list to display calculations, functions, items and parameters that you can use to build a calculation. To include items in the calculation, paste them into the Calculation field.
Use the Show list options as follows:
Use the Functions option to display an extensive list of predefined Oracle functions.
Use the Selected option to display items in the worksheet.
Use the Available option to display all items in the business area.
Use the Calculations option to display existing worksheet calculations.
Use the Parameters option to display existing worksheet parameters. When you add parameters to the Calculation field, the parameter name is prefixed with ':' to indicate that it is a dynamic value set by the worksheet user. For more information about adding parameters to calculations, see "About using parameters to collect dynamic user input".
Use this button to the add the item currently selected in the Show list to the Calculation. The item is copied into the Calculation field.
Use this field to enter the calculation details (for calculation examples, see "Discoverer calculation examples").
Note: When you add text to this field using the Paste button or a template, the text is appended to any existing text in the Calculation field, unless text in the Calculation field is selected.
To create a calculation, you one or more of the following methods:
Type the formula directly into the Calculation field.
Note: If you type an expression in the Calculation field, you must prefix the expression with an equals sign (that is, =). For example, '=Sales SUM-Costs SUM'.
Paste items and functions from the Show field into your calculation.
Click the operator buttons underneath the Calculation field to use them in the calculation.
Use a template to help you define an analytic function. Click Insert Formula from Template (see Insert Formula from Template field below).
Use this button to display a pop-up list of templates that you can use to build analytic functions. For example, choose Rank to display an easy-to-use Rank template that helps you create a ranked list position calculation. For more information about templates available, see "What analytic function templates are available in Discoverer?". When you use a template, the formula that you create is appended to any existing text in the Calculation field, unless text in the Calculation field is selected.
Operator buttons [+] [-] [x] [/] [%] [(] [)] [||]
Use these buttons to add operators to the calculation. Operators are copied into the Calculation field.
Note: When you use arithmetic expressions in a calculation, the multiply and divide operators are executed first, regardless of their position in the calculation. If you have multiple operators of the same precedence, they are evaluated from left to right.
For example, the calculation Price – Discount * Quantity is evaluated as Discount*Quantity subtracted from Price. If you use parentheses around the subtraction expression (Price – Discount), the subtraction is executed before the multiplication.
Use this button to validate and save the calculation, as follows:
If the calculation has valid syntax, the calculation is saved and displayed in the Calculations dialog.
If the calculation has invalid syntax, an error message is displayed. You must correct any syntax errors before you can save the calculation.
The table below shows the functions available in the box beneath the Show drop down list.
| Category | Description |
|---|---|
| All Functions | An alphabetical list of all functions. |
| Analytic | Advanced statistical analysis, such as RANK, NTILE, CORR. |
| Conversion | Converting from one data type to another, such as: RAWTOHEX, TO_CHAR, TO_DATE. |
| Database | Optional category that is displayed when user defined functions are available (created using the Register PL/SQL Functions facility on the Tools menu in Oracle Discoverer Administrator). |
| Date | Manipulating date items such as ADD_MONTHS, NEW_TIME, SYSDATE and NEXTDATE. |
| Group | Aggregate and statistical functions including SUM, COUNT, MAX, MIN, VARIANCE. |
| Numeric | Numeric items and floating point such as COS, LOG, MOD, POWER. |
| Others | Miscellaneous functions such as LEAST, USER, DECODE, ROWNUM. |
| String | Character items, text operations such as INITCAP, LPAD, NLS_UPPER. |
For detailed information about all functions available in Discoverer, see Oracle SQL Reference and Oracle Database Data Warehousing Guide.
For examples of the most commonly used functions, see "Discoverer calculation examples".
To correct a divide by zero error, modify the calculation to check for a zero-value in the denominator and return a value of zero (for the entire calculation). For example, Instead of (SS+PS)/(SS-PS), use DECODE ((SS-PS), 0, 0, ((SS+PS)/(SS-PS))).