New Item dialog

Use this dialog to create items based on EUL objects and functions. Items can be derived items, aggregate calculated items or aggregate derived items.

For more information, see:

"What are items?"

"What are calculated items?"

"How to create calculated items"

For example:

• to create an item named Year, based on the EUL item Calendar Year

• to create a calculated item named Sales Target, based on the formula Sales SUM * 1.25

• to create a calculated item named Profit, based on the formula Sales SUM - Cost SUM

• to create a calculated item named Rank Sales, based on the formula RANK () OVER (ORDER BY Sales SUM)

Show

Use these options to display calculations, functions, and item that you can use to build a calculation. To include items in the calculation, paste them into the Calculation field.

• Items

  Use this option to display the EUL items available.

• Functions

  Use this option display the functions available.

Paste

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.

Name

Use this field to enter a name for the item or calculated item. Discoverer Administrator creates a default name for you.

Calculation

Use this field to enter the calculation details (for calculation examples, see Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus).

To create a calculation, you can use any or all 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 (=). For example, '=Sales SUM-Costs SUM'.

• Paste items and functions from the Show field on the left into the calculation.

• Click the operator buttons underneath the Calculation field to use them in the calculation.

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 more than one operator 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.

OK

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.

Notes

• The table below shows the functions available in the field beneath the Show field:

  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).
  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, transcendentals 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 the Oracle Database SQL Reference Guide and Oracle Database Data Warehousing Guide.

• For examples of the most commonly used functions, see Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus.