12
Calculations

This chapter consists of the following sections:

• 12.1 Introduction

• 12.2 Creating Calculations

• 12.3 Editing Calculation Properties

• 12.4 Editing Calculations

• 12.5 Deleting Calculations

• 12.6 Registering Custom PL/SQL Functions

12.1 Introduction

12.1.1 What is a Calculated Item?

Typical business calculations include values such as profit margins, average revenues per month, expected sales, and percent of profit by product type. You can represent these business calculations in Discoverer by creating Calculation Items. Once created, Calculation Items behave much like any other Item in a Folder and can be used in Conditions, Summaries, Lists of Values, Joins, and other Calculation Items.

You create Calculation Items using expressions that can contain:

• Existing Items

• Operators

• Literals

• Functions

There are three types of calculations:

• Derived Items

• Aggregate Calculations

• Aggregate Derived Items

Derived items and aggregate calculations appear with different symbols to differentiate them. Aggregate derived items appear with the same symbol as derived items. For more information about the Icons that appear in the work area, see Section 3.3, "The Work Area."

12.1.1.1 Derived Items

A derived item is a non-aggregate expression that appears and acts exactly like any other item in the folder. Derived items can be axis items or data points and can be used anywhere one would use an ordinary item. They are static, in that their value depends only on the value of the other items in the same row, and when computed will be the same regardless of what other items are selected in the user's query.

Examples of Derived Items:

Sal*12+NVL(Comm,0)

Initcap(Ename)

1

Sysdate-7

12.1.1.2 Aggregate Calculations

If the formula of a new item contains an aggregate or group function such as SUM, AVG, MAX, MIN, or COUNT, and the items it aggregates are in the current folder, the item is created as an aggregate calculation.

Examples of Aggregate Calculations:

SUM(Sal)*12

SUM(Comm)/SUM(Sal)

AVG(Monthly Sales)

Aggregate calculations are dynamic, in that their value depends on the other items selected in the Discoverer Plus worksheet in which they are used, because this affects the axis items that are grouped together and hence the number of rows that are aggregated. This is particularly important in the case of calculations that are ratios of two aggregates.

For example to calculate Margin, one would use the calculation SUM(Profit)/SUM(Sales) rather than Profit/Sales. Used in a query, the latter would result in SUM(Profit/Sales), which produces a different result from SUM(Profit)/SUM(Sales). Data points should always be summed before a ratio is computed.

12.1.1.3 Restrictions on Aggregate Calculations

Aggregate Calculations:

• must be data points

• must have Default Aggregation set to Detail.

• must reference items in the current folder.
  (Aggregates of items in source folders, in the case of a complex folder, will be created as aggregate derived items.)

• cannot be used in a join.

• cannot be used in a mandatory condition.

• cannot be used in a hierarchy.

• cannot have an item class.

• cannot be dragged into a complex folder.

• cannot have further aggregation functions applied to them in Discoverer Plus.

Aggregate calculations do not affect the row set of the folder. Their dynamic nature means that they only affect generated SQL when selected in Discoverer Plus.

12.1.1.4 Aggregate Derived Items

An aggregate derived item is a calculation created in a complex folder that aggregates items in one or more of the source folders. Aggregate derived items behave in all respects like ordinary derived items, and are used where there is a requirement to nest aggregate functions.

Aggregate derived items always affect the row set of the folder, because they cause the whole folder to be aggregated by all the other axis items in the folder, even if the axis items are not used in a sheet.

Example of an Aggregate Derived Item:

1. A Complex Folder named Monthly Sales Analysis is built by dragging the following items from the Video Analysis folder:
   • Department

   • Region

   • City

   • Store Name

   • Year

   • Quarter

   • Month

   The complex folder Monthly Sales Analysis contains a row for every store, for every month.

2. An aggregate calculation item Monthly Sales Per Store is created with the formula:

   SUM(Video Analysis.Sales)

   This item shows the total sales for a given store in a given month.

3. An aggregate calculation is defined as:

   Average Monthly Sales per Store = AVG(Monthly Sales Per Store)

   This item shows the average monthly sales and can be analyzed over region, quarter, year, etc., to compare trends. This sort of nested aggregate is only possible by creating the aggregate item in a new folder, because this folder now represents monthly sales, not the individual sales that were in the original complex folder.

   Aggregate derived items cause the folder SQL to contain a GROUP BY and all end-user queries to return one row for each combination of all the other non-aggregated items, regardless of whether the calculation is used in the query. This reduces the number of rows returned by the folder, because they are aggregated at the level of the other items.

12.1.2 Advantages of Calculations

Calculation Items can be a critical part of an end user's report. As Discoverer Administrator, you should set up commonly used calculations as predefined Items in a Folder. This makes them available for inclusion in Discoverer Plus worksheets.

Creating Calculation Items provides the following benefits:

• Users do not need to create the calculation themselves, they can simply select the item.

• New items can be added to a folder that do not exist as columns in the underlying database tables.

• Complicated formulae can be set up to compute results without the user having to understand the details of the computation involved.

12.1.3 Calculations and Analytic Functions

Analytic Functions behave like Aggregate Calculations except for the following:

• Analytic Functions do not require aggregation. All other restrictions that apply to Aggregate Functions apply to Analytic Functions, (see "Restrictions on Aggregate Calculations").

• Analytic Function aggregation must be derived from the database server, not the memory cache - this has implications on performance.

• Analytic Functions are only available in Oracle 8.1.6 or later.

• Analytic functions are not part of Object SQL.

For further information about Analytic Functions refer to the Discoverer 4i Plus User's Guide.

12.1.4 More information

You can obtain more information about Calculations in Oracle Discoverer from the following sources:

• For worked examples of Calculations, refer to the Oracle Discoverer Plus User Guide.

• For information on general Function syntax, refer to your Oracle 8i SQL Reference documentation.

• For information on Analytic Function syntax, refer to "Oracle 8i SQL Reference Release 2 (8.1.6)" - Part Number A76989-01.

12.2 Creating Calculations

This section describes how to create a new Calculation.

1. Select the Folder that you want to contain your new Calculation (on the Data page of the work area).

2. Open the New Item dialog box.

   There are three ways of doing this:

   • Popup Menu
     Right-click the folder on the Data page and choose New Item... on the popup menu.

   • Tabular Icon
     Click the New Item tabular icon ()

   • Menu
     Choose Insert | Item....

   The New Item dialog box (see Figure 12-2) enables you to create a new Calculation Item and add it to the selected Folder.

   NOTE: If you didn't select a Folder in step 1, Discoverer Administration Edition displays the New Item dialog box (see Figure 12-1). Select the Folder that you want to contain your new Calculation using this dialog box (you can select any Folder from within any open Business Area).

Figure 12-1 Selecting a Folder to Contain your new Calculation

Figure 12-2 New Item Dialog

3. Specify the Name for your new Calculation.

4. If you already know the calculation syntax, you can type your calculation directly into the Calculation area.

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

   However, the New Item dialog box has the following features to help you construct the Calculation without needing prior knowledge of the syntax:

   • Show Items

     If you select Items, the Show area displays a list of the Items in the selected Folder (if the selected Folder is a Complex Folder, the source Folders and their Items are also displayed here).

     You can insert an Item in the list into your Calculation by double-clicking on it (you can also select the Item and click Paste).

   • Show Functions

     If you select Functions, the Show area displays a list of the Functions (grouped by type) available for use in your Calculation.

     If you have registered a custom PL/SQL function, it will appear in the Database group. For more information, see Section 12.6, "Registering Custom PL/SQL Functions."

     You can insert a Function in the list into your Calculation by double-clicking on it (you can also select the Function and click Paste).

   • Operators

     You can insert any of the operators (listed along the bottom of the Calculation area) into your Calculation by clicking on them.

   • Window Size

     You can resize the New Item dialog box to give you more room to type your Calculation.

   For more information see the Oracle Discoverer Plus User Guide.

5. When you have finished specifying the Calculation formula, click OK.
   • If there are no errors in the formula, the new Item is created.

   • If there are errors in the formula, Discoverer Administration Edition displays the first error and returns you to the New Item dialog box so that you can correct it.

   You can now use this new Item to create joins, conditions, and even new calculations.

   12.3 Editing Calculation Properties

   Item properties are accessible through Item Properties dialog boxes. This section shows you how to enhance the user's view of the data by editing item properties. Figure 12-3 shows an example Item Properties dialog box.

   Figure 12-3 Item Properties Dialog Box with the General Tab Selected

   
   

   12.3.1 Editing the Properties of a Single Item

   This section describes how to edit an item's properties.

   1. Open the item's Properties dialog box.

      There are four ways to do this:

      • Double-click
        Double-click the item on the Data page.

      • Popup Menu
        Right-click the item on the Data page and choose Properties on the popup menu.

      • Toolbar Icon
        Click the item on the Data page and click the Properties toolbar icon ()

      • Menu
        Click the item on the Data page and choose Edit | Properties.

   2. Make your changes as required.

      For more information on the fields on this dialog box, click Help.

   3. Click OK.

   12.3.2 Editing the Properties of Multiple Items

   The following steps show you how to set common properties for more than one item at a time:

   1. Select all of the items whose properties you want to edit.
      (Ctrl-clicking enables you to select more than one folder.)

   2. Display the Item Properties dialog box.

      There are three ways to do this:

      • Popup Menu
        Right-click one of the selected folders on the Data page and choose Properties on the popup menu.

      • Toolbar Icon
        Click the Properties toolbar icon ()

      • Menu
        Choose Edit | Properties.

      All properties that are common to each of the selected folders are displayed. If the data for a field is not common to each of the selected folders, the field is blank.

   3. Make your changes as required.
      Any changes you make here will be applied to all of the selected folders.

      For more information on the fields on this dialog box, click Help.

   4. Click OK.

   12.4 Editing Calculations

   This section describes how to edit an existing Calculation.

   1. Display the Edit Calculation dialog box (see Figure 12-2).

      There are three ways to do this:

      • Popup Menu
        Right-click the Calculation Item on the Data page and choose Edit Item... on the popup menu.

      • Menu
        Click the Calculation Item on the Data page and choose Edit | Edit...

      • Item Properties Dialog Box
        Click in the Formula field on the Item Properties dialog box.

      The Edit Calculation dialog box works in the same way as the New Item dialog box (see Section 12.2, "Creating Calculations" for more information).

   2. Edit the Calculation as required.

   3. Click OK.

   12.5 Deleting Calculations

   This section describes how to delete Calculation Items.

   1. Select the Calculation Item(s) that you want to delete.

      To select more than one Item at once, hold down Ctrl while you click on the Items.

   2. Delete the Item(s):

      There are three ways to do this:

      • Popup Menu
        Right-click one of the selected Item(s) and choose Delete Item... on the popup menu.

      • Menu
        Choose Edit | Delete.

      • Keyboard
        Press Delete.

      This opens the Confirm Delete dialog box.

   3. Click Impact.
      This displays the Impact dialog box that shows any other objects that may be affected by the deletion (Figure 12-4). The Impact dialog box helps you to make the right choice.

   Figure 12-4 The Impact Dialog Box
   

   4. When you have finished reviewing the impact this action will have, click OK.

   5. If you still want to delete the selected Items(s), click Yes.

   12.6 Registering Custom PL/SQL Functions

   In addition to the PL/SQL functions provided by Oracle, you can create custom PL/SQL functions to meet your users' requirements. These can be used to provide predefined functions for complicated calculations. User-defined PL/SQL functions become part of the total set of PL/SQL functions available to all database processes.

   NOTE: You do not create user-defined PL/SQL functions directly in Discoverer Administration Edition. To create PL/SQL functions, use SQL*Plus, or a procedural editor. Refer to the Oracle SQL Language Reference Manual.

   To be able to access custom PL/SQL functions using Discoverer, you must have first registered them in the EUL. Once registered, user-defined PL/SQL functions appear in the list of database functions in the Edit Calculation dialog box and can be used in the same way as the Oracle-supplied functions.

   In Discoverer Plus, folders that contain derived items using PL/SQL functions will not be visible to users who do not have EXECUTE privileges on those functions. To make those functions accessible, ensure that the user has the associated EXECUTE privilege in the database.

   There are two methods for registering functions:

   • Manual Registration
     Manual registration requires that you register each function individually by supplying all of the pertinent information about the function.

   • Import
     Registering PL/SQL functions by importing is preferable, especially if many functions need to be registered. Importing functions imports all of the relevant information about each function, including name, database link, return type, and the list of arguments. Importing ensures correct information about the function, because the information does not have to be manually entered on a function-by-function basis.

   Which you choose depends on the version of the Oracle database you're using:

   • Oracle Release 7.3 or later
     Automatic import is available, but you can also register manually.

   • Oracle Release 7.2 or earlier
     Manual registration is required.

   12.6.1 Registering Custom PL/SQL Manually

   This section describes how to manually register a PL/SQL function for use in Discoverer.

   1. Choose Tools | Register PL/SQL Functions.

      This opens the PL/SQL Functions dialog box on the Functions page (see Figure 12-5).

   Figure 12-5 Functions Tab in the PL/SQL Functions Dialog Box
   

   2. Click New.

   3. Specify the function attributes.

   4. Click Validate.

      This checks the validity and accuracy of the information you have entered.

   5. If the function is invalid, correct the attributes and click Validate again.

   6. Does this function accept arguments?
      • Yes
        Continue to step 7.

      • No
        Click OK. The custom PL/SQL function is now registered for use in Discoverer.

   7. Click the Arguments tab (see Figure 12-6).

   Figure 12-6 Arguments Tab in the PL/SQL Functions Dialog Box
   

   8. Click New.

   9. Specify the argument attributes.

   10. When you have finished defining the arguments, click OK.

       The custom PL/SQL function is now registered for use in Discoverer.

   12.6.2 Registering PL/SQL Functions Automatically

   This section describes how to register PL/SQL functions automatically:

   1. Choose Tools | Register PL/SQL Functions.

      This opens the PL/SQL Functions dialog box on the Functions page (Figure 12-5).

   2. Click Import.

      This opens the Import PL/SQL Functions dialog box (see Figure 12-7), which enables you to select the PL/SQL functions that you want to register.

   Figure 12-7 Import PL/SQL Functions Dialog Box
   

   3. Select the functions that you want to import

   4. Click OK.

   All of the pertinent information about the selected functions is imported, and you do not have to manually enter information or validate it.